472,789 Members | 1,190 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

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 2020
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ron Nolan | last post by:
I have a large application that contains lots and lots of financial history data. The history data is currently set up in a table called 'TblHist' that exists inside each of these three .mdb...
6
by: alanknipmeyer | last post by:
Hi, I`m in the process of migrating a Access 2002 (Run in 2000 mode) from Windows 98 to Win2K Server. It is a shared resource via a file share on the 98 Server. Client systems are Win98 with the...
9
by: Daven Thrice | last post by:
If I have a fairly big Access MDB, that is relational, and has, say, 100 objects (forms, reports, modules, etc.), what is the path to get this database "online". Is there a way to put the tables...
6
by: Woody Splawn | last post by:
I have been using SQL Server 2000 on my stand-alone machine as a back-end to a VS.net application. It is time to switch environments and take the application to the customer. I need to install...
3
by: Zammy | last post by:
I have a database on a server that many people access repeatedly during the day. I have set up the switchboard form with code to minimize the database window, but when I try to open the database...
9
by: axs221 | last post by:
I am trying to move some of our large VBA Access front-end file into ActiveX DLL files. I created two DLL files so far, one was a module that contains code to integrate into the QuickBooks...
26
by: Trish | last post by:
I have an Access 2003 database application consisting of a frontend on each user's desktop and a backend on a network drive. I have been asked to move the backend into a folder that the users...
4
by: mrouleau | last post by:
I am sorry if this is the wrong group to ask, if so please point me in the correct direction. My problem is I have an MDB file with user-level security on it (mdw). When i move it over to a...
0
by: SuzK | last post by:
I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are Week Ending (date) ItemNbr (double) Sales Dollars...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.