I have 2 database db1 and db2
db1 is in c:\mydir
db2 in server dir \\myserverdir
the 2 databse have a table named TABLE1 with the same fields and property.
I need to append (witha select sql) data from db1 to db2....
Wath is the best way based speeding and best performance?
I use vb6 and ado ject.
fast way to append records in access mdb in a server dir
-
- PlatinumLounger
- Posts: 4376
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: fast way to append records in access mdb in a server dir
You can create a link in either of the databases to the table in the other one. You can then treat the linked table as a local table.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4376
- Joined: 26 Apr 2010, 17:36
Re: fast way to append records in access mdb in a server dir
HansV wrote:You can create a link in either of the databases to the table in the other one. You can then treat the linked table as a local table.
just i have think this way....sure.
But the db1 is on a Pc client and not every day is on line:-(
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: fast way to append records in access mdb in a server dir
You won't be able to run the query if the PC is not online, of course. But as long as you don't try to run the query (or open the linked table), you won't get an error message.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4376
- Joined: 26 Apr 2010, 17:36
Re: fast way to append records in access mdb in a server dir
OK! but in otrher case how about:HansV wrote:You won't be able to run the query if the PC is not online, of course. But as long as you don't try to run the query (or open the linked table), you won't get an error message.
Code: Select all
SQL = "INSERT INTO TABEL2 IN \\MYSERVER\DB2.mdb SELECT * FROM TABEL1 WHERE AGG=#" & Format(LAST_DAY, "MM/DD/YYYY") & "#"
OCONN.Execute (SQL), CONTA, adCmdText Or adExecuteNoRecords
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: fast way to append records in access mdb in a server dir
That should work, but again only when the PC is connected to the network.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4376
- Joined: 26 Apr 2010, 17:36
Re: fast way to append records in access mdb in a server dir
Naturally when the PC is connected... but i have forgotten a part mosst important in my postHansV wrote:That should work, but again only when the PC is connected to the network.
I run the code from other project in vb6, and naturally the pc is Conected
In other case after the first test with the last code have error in "insert into..." have idea?
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: fast way to append records in access mdb in a server dir
You should enclose the database name in single quotes:
SQL = "INSERT INTO TABEL2 IN '\\MYSERVER\DB2.mdb' SELECT * FROM TABEL1 WHERE AGG=#" & Format(LAST_DAY, "MM/DD/YYYY") & "#"
SQL = "INSERT INTO TABEL2 IN '\\MYSERVER\DB2.mdb' SELECT * FROM TABEL1 WHERE AGG=#" & Format(LAST_DAY, "MM/DD/YYYY") & "#"
Best wishes,
Hans
Hans