By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

Moving data between Access mdb's

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.