Transfer data from 1 Access DB to another without using Linked tab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an Access database which has a table called Bookings. What I need to
do is, on a regular basis, transfer the data from this table into another
SEPARATE Access database. I am unable to use linked tables.

The approach I have taken is to connect to the source database using ADO and
creating a recordset with the required data. I now need to "paste" that
recordset into the target DB. Apart from doing it record by record (ie:
traversing the recordset), is there any other way of getting the data from
the recordset into the target table?

Rael
 
Rael,

You can use an Append query in the target database, which retrieves
records from the table in the source database without using links, by
referencing the source database by its full path, like:

INSERT INTO MyTableA ( Field1, Field2, ..., FieldX )
SELECT FieldX, FieldY, ..., FieldZ
FROM [\\ServerName\Folder1\Folder2\SourceDatabase.mdb].[MyTableB] As tB
WHERE tB.FieldX = 5

I hope this is clear.
Nikos
 
if you were using Access Data Projects you would have much much better
luck.. you have an arsenal of enterprise level ETL tools; for example
DTS or SSIS.

MDB is obsolete; anybody that uses MDB to store 1gb of information
should be fired on the spot.

-Aaron





rael_lucid said:
Thanks!!
Seems to work perfectly :)


Nikos Yannacopoulos said:
Rael,

You can use an Append query in the target database, which retrieves
records from the table in the source database without using links, by
referencing the source database by its full path, like:

INSERT INTO MyTableA ( Field1, Field2, ..., FieldX )
SELECT FieldX, FieldY, ..., FieldZ
FROM [\\ServerName\Folder1\Folder2\SourceDatabase.mdb].[MyTableB] As tB
WHERE tB.FieldX = 5

I hope this is clear.
Nikos
Click to expand...
Click to expand...
 
Back
Top