Moving data between Access mdb's

[Guest]

I have tried everything I can think of to move records from one Access
database to another. It should not be that hard. Read on....

I have identical access mdb's. One contains data; one contains empty
tables. From an earlier posting, I was able to move the data by first
exporting XML files and then importing them. I would like to find a way to
skip the Import/Export steps.
I first read the tables into a DataSet from the full database using a
DataAdapter. At this point the following code does the Export/Import.

oDS.WriteXml(sDir & "\BackUp.xml")
oDS.WriteXmlSchema(sDir & "\BackUp.xsd")
oDS_New.ReadXmlSchema(sDir & "/BackUp.xsd")
oDS_New.ReadXml(sDir & "/BackUp.xml")
oDS.Merge(oDS_New, True)

oDS is the DataSet created from the full database. By using oDS.Merge()
method, I can use the oDS to successfully populate the empty mdb by using a
connection to that database and a DataAdapter. There must be a way to skip
the Import/Export.

I have unsuccessfully tried using oDS_New = oDS.Clone and other ideas. to
replace the four Read/Write steps. None work. Any suggestions will be
appreciated.

 

[OHM \( Terry Burns \)]

Take a look at this, it may help ?

http://www.vbforums.com/showthread.php?t=251384

 

[Paul Clement]

¤ I have tried everything I can think of to move records from one Access
¤ database to another. It should not be that hard. Read on....
¤
¤ I have identical access mdb's. One contains data; one contains empty
¤ tables. From an earlier posting, I was able to move the data by first
¤ exporting XML files and then importing them. I would like to find a way to
¤ skip the Import/Export steps.
¤ I first read the tables into a DataSet from the full database using a
¤ DataAdapter. At this point the following code does the Export/Import.
¤
¤ oDS.WriteXml(sDir & "\BackUp.xml")
¤ oDS.WriteXmlSchema(sDir & "\BackUp.xsd")
¤ oDS_New.ReadXmlSchema(sDir & "/BackUp.xsd")
¤ oDS_New.ReadXml(sDir & "/BackUp.xml")
¤ oDS.Merge(oDS_New, True)
¤
¤ oDS is the DataSet created from the full database. By using oDS.Merge()
¤ method, I can use the oDS to successfully populate the empty mdb by using a
¤ connection to that database and a DataAdapter. There must be a way to skip
¤ the Import/Export.
¤
¤ I have unsuccessfully tried using oDS_New = oDS.Clone and other ideas. to
¤ replace the four Read/Write steps. None work. Any suggestions will be
¤ appreciated.

If you're just copying data you can use straight SQL to perform the transfer. Just connect to your
source database and execute a statement with the following syntax:

INSERT INTO [MS Access;DATABASE=D:\My Documents\db10.mdb;].[Table1] SELECT * FROM Table1


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ilyan Mishiyev]

Try using DTS, in my opinion one of the best solutions for moving large
amounts of data. Create a dts package using export/import wizard of
MSSQLServer Enterprise Manager , save it locally, and call it from your
application. That's it. You don't have to use ADO to do that because you
don't want to load this data into memory, you just need it moved from one
place to another.
You can dynamically provide source, destination, and other variables.