468,484 Members | 1,618 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,484 developers. It's quick & easy.

Moving data between Access mdb's

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.

Nov 21 '05 #1
3 1881
Take a look at this, it may help ?

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

--
OHM ( Terry Burns )

http://TrainingOn.net

"genojoe" <ge*****@discussions.microsoft.com> wrote in message
news:04**********************************@microsof t.com...
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.

Nov 21 '05 #2
On Fri, 22 Apr 2005 22:46:01 -0700, "genojoe" <ge*****@discussions.microsoft.com> wrote:

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)
Nov 21 '05 #3
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.

"genojoe" <ge*****@discussions.microsoft.com> wrote in message
news:04**********************************@microsof t.com...
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.

Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Daven Thrice | last post: by
6 posts views Thread by Woody Splawn | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by theflame83 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.