Can I use an SQL action query to transfer data between DIFFERENT Access .mdb files 
Author Message
 Can I use an SQL action query to transfer data between DIFFERENT Access .mdb files

Please help.
I am writing a program that takes vegetation data from one table in one
Access *.mdb file and matches it with regional data in a different Access
database. As a consequence I need to insert the new veg data into the
regional database for matching.  How can I specify the path as well as
table name from which to get the data in the SQL for my INSERT INTO action
query.  If this isn't possible will I have to loop through the source
recordset and use AddNew, Update etc to add the copy the records between
databases ? Many thanks. Simon Smart.



Sun, 11 Jul 1999 03:00:00 GMT  
 Can I use an SQL action query to transfer data between DIFFERENT Access .mdb files

Hello Simon,

The easiest way to accomplish this is to temporarily attach (link) the
table in the remote access database to the database you currently have
open.  Then, you can access both tables as if they were in the same
database.  You could then delete the attachment when you were done.

Check out the TableDef Object page in help, particularly the Connect
property for help on this.  The RefreshLink method might be worth a look as
well.  I hope this helps,

Larry Tubbs, MCPS, MCSD
Silverleaf Vacation Club

http://www.flash.net/~enigma/



Quote:
> Please help.
> I am writing a program that takes vegetation data from one table in one
> Access *.mdb file and matches it with regional data in a different Access
> database. As a consequence I need to insert the new veg data into the
> regional database for matching.  How can I specify the path as well as
> table name from which to get the data in the SQL for my INSERT INTO
action
> query.  If this isn't possible will I have to loop through the source
> recordset and use AddNew, Update etc to add the copy the records between
> databases ? Many thanks. Simon Smart.



Mon, 12 Jul 1999 03:00:00 GMT  
 Can I use an SQL action query to transfer data between DIFFERENT Access .mdb files

Quote:

> Please help.
> I am writing a program that takes vegetation data from one table in one
> Access *.mdb file and matches it with regional data in a different Access
> database. As a consequence I need to insert the new veg data into the
> regional database for matching.  How can I specify the path as well as
> table name from which to get the data in the SQL for my INSERT INTO action
> query.  If this isn't possible will I have to loop through the source
> recordset and use AddNew, Update etc to add the copy the records between
> databases ? Many thanks. Simon Smart.

You'll find things much easier to cope with if you just deal with *one*
database. Pick one, and use MS-Access to Attach the relevant table(s), from
the other database, to it. Your program will then no longer need to worry
about pathnames in SQL since all the tables will appear to come from the
same database.

--

Jacobin's Chare                          http://tatpurusa.octacon.co.uk/
Newcastle upon Tyne      BB 74 A4 EF 03 F8 44 C1 F3 75 FE C6 7E F9 6E 43



Thu, 22 Jul 1999 03:00:00 GMT  
 Can I use an SQL action query to transfer data between DIFFERENT Access .mdb files

Hi guys:

I agree attaching two databases to a single mdb file could make things easier.

If you are sure you want to do it in different way, here is an option.

In your sql statements, specify the full path of the mdb file and use IN keyword.

for example:

SELECT * FROM customer WHERE CustomerID BETWEEN 1 AND 1000 IN
c:\MSOffice43\Access\Samples\NWind.mdb

I did this once and I can not actually remember if you should use quote signs to enclose
the database name. But I think you can search for IN keyword in your on-line help, or
simply try it out on your VB environment.

rgds
Jeff Hong YAN

Quote:


> > Please help.
> > I am writing a program that takes vegetation data from one table in one
> > Access *.mdb file and matches it with regional data in a different Access
> > database. As a consequence I need to insert the new veg data into the
> > regional database for matching.  How can I specify the path as well as
> > table name from which to get the data in the SQL for my INSERT INTO action
> > query.  If this isn't possible will I have to loop through the source
> > recordset and use AddNew, Update etc to add the copy the records between
> > databases ? Many thanks. Simon Smart.

> You'll find things much easier to cope with if you just deal with *one*
> database. Pick one, and use MS-Access to Attach the relevant table(s), from
> the other database, to it. Your program will then no longer need to worry
> about pathnames in SQL since all the tables will appear to come from the
> same database.

> --

> Jacobin's Chare                          http://tatpurusa.octacon.co.uk/
> Newcastle upon Tyne      BB 74 A4 EF 03 F8 44 C1 F3 75 FE C6 7E F9 6E 43


> > Please help.
> > I am writing a program that takes vegetation data from one table in one
> > Access *.mdb file and matches it with regional data in a different Access
> > database. As a consequence I need to insert the new veg data into the
> > regional database for matching.  How can I specify the path as well as
> > table name from which to get the data in the SQL for my INSERT INTO action
> > query.  If this isn't possible will I have to loop through the source
> > recordset and use AddNew, Update etc to add the copy the records between
> > databases ? Many thanks. Simon Smart.

> You'll find things much easier to cope with if you just deal with *one*
> database. Pick one, and use MS-Access to Attach the relevant table(s), from
> the other database, to it. Your program will then no longer need to worry
> about pathnames in SQL since all the tables will appear to come from the
> same database.

> --

> Jacobin's Chare                          http://tatpurusa.octacon.co.uk/
> Newcastle upon Tyne      BB 74 A4 EF 03 F8 44 C1 F3 75 FE C6 7E F9 6E 43



Thu, 22 Jul 1999 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Parameters on action query for MS Access data environment

2. Executing Access 7.0 action queries from VB4 using RDO

3. Executing Access 7.0 action queries from VB4 using RDO

4. How to access an access (.mdb) without using odbc or sql

5. SQL in VB 4.0 Query Access 2.0 MDB

6. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

7. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

8. Query Builder: Join table from different MDB?

9. Query Builder: Join table from different MDB?

10. Transfer data from SQL server to Access

11. Transfer Record within Access 2000 Tables using VB6 Sql

12. Transfer Record within Access 2000 Tables using VB6 Sql

 

 
Powered by phpBB® Forum Software