DAO - If you convert a Jet3 [DAO 3.5] database to Jet4 [DAO 3.6] using VB6's CompactDatabase function the database will be converted to Jet4 but it can't be opened in Access XP . To get around the problem I started making a conversion in VB6 by creating a new Jet4 database then copying the database structure and all the data using various DAO facilities. This tip 'builds on' the September 2000 tip: Update an Access Database with a Model's Schema using DAO. I added one more class which I call 'cCopyDataDAO.cls'. I wrote a VB6 demo program to show how to use the tip.
ADO - July 2003 - Support for ADO/ADOX added. I used ADO/ADOX version 2.7. The tip 'builds on' the June 2003 tip: Update an Access Database with a Model's Schema using ADOX. Class 'cCopyDataADO.cls' is added.
Select the database to be copied then click the 'Do Create...' button.
To - The 'to' name is automatically created by adding the '_Copy_yyyymmdd_hhnnss' to the copy from name; where yyyy is the current year, mm is the current month, dd is the current day, hh is the current hour, nn is the current minute, and ss is the current second.
From - The name of the database to be created. Click the 'browse' button to select from a list, for example:
Select a database to be copied.
This sample Access database is distributed with the tip.
Sample Copy Data Code
Private Function DoCopyData(dbIn As Database, dbOut As Database) As Boolean
Dim obj As New cCopyDataDAO
.dbIn = dbIn
.dbOut = dbOut
If .CopyData Then
DoCopyData = True
Private Function DoCopyData(connDBIn As ADODB.Connection, connDBOut As ADODB.Connection) As Boolean
Dim obj As New cCopyDataADO
DoCopyData = .CopyData(connDBIn, connDBOut)
DAO - Processing steps are:
Create a new database using code similar to: DAO.CreateDatabase "myDB.mdb", dbLangGeneral, dbVersion40
Copy the tables from the existing database to the one just created using DatabaseUpdateUsingDLL.dll.
Copy the data from the existing database to the new one using cCopyDataDAO.cls.
Copy the indexes from the existing database to the new one using DatabaseUpdateUsingDLL.dll.
Copy the relationships from the existing database to the new one using DatabaseUpdateUsingDLL.dll.
ADO - Processing steps are:
Create a new database using code similar to: catDBOut.Create mcsProvider & Me.Text1(1).Text
Copy the tables and indexes from the existing database to the one just created using DatabaseUpdateUsingADODLL.dll.
Copy the data from the existing database to the new one using cCopyDataADO.cls.
Copy the stored procedures and views from the existing database to the new one using DatabaseUpdateUsingADODLL.dll.
Copy the relationships from the existing database to the new one using DatabaseUpdateUsingADODLL.dll.
Note: Only tables, data, indexes, queries, and relationships are copied. Any other 'Access' item is not copied, for example: Macros, Reports or Forms.
You may want to review the July 1999 tip titled: What I Learned About DAO 3.60 and Jet 4.
Using the Routines
Add the following module to your application if you want to use these routines:
Module Description cCopyDataDAO.cls The DAO class module used to copy the data. cCopyDataADO.cls The ADO class module used to copy the data. Also contains a 'CompactDatabase' method.
Register the following DLL and OCX.
DLL Description DatabaseUpdateUsingDLL.dll DAO - Copy the structure from the old to the new database. Click here for details. DatabaseUpdateUsingADODLL.dll ADO - Copy the structure from the old to the new database. Click here for details. VBDotComSubClassLib.ocx Restrict the form's size. Click here for details. Not needed for your application. Used with clsMinWindow, see below.
Used in the demo, not needed in your application:
Module - Click for More Info Description Used in the Demo To modConnectToWebsite.bas Connect to a web site Show this info when Help/Info is selected from the demo. modInfoAbout.bas Info and about help. Provide help via the web and 'about' info. modLocationLocationLocation.bas Housekeeping routines. Add a 'window' menu to the form. modMouseSet.bas Nest mouse settings. Show then restore the cursor. modPath.bas Get name or path from fully qualified file name. Used to get the path and name from the copy from file name. modSleep.bas Suspend execution. Allow DAO to create all tables before copying data. modStatusV2.bas Show information in a status bar. Show status information. TBRegistry.bas Read and write to the registry. Save the database name and form location. Also needed by modLocationLocationLocation. cDialog.cls Browse for a file. From Francesco Balena. Get the database to be copied. cFileExists.cls Check for file existence. From Randy Birch. Does the database, to be copied, exist. Enable the copy button if it does. Used in DAO version only. clsMinWindow.cls Restrict a form's size. Use with VBDotComSubClassLib.ocx, see above. Restrict the form to a minimum size. modCompactDBUsingJRO.bas Compact a Jet database using JRO. Requires a reference to 'Microsoft Jet and Replication Objects x.x' library. Compact the from and to database. ADO only.
Download VB Code
Click here to download the VB 6 DAO Source Code [88k]. Click here to download the VB 6 ADO Source Code [102k].
Previous Tip Tip Index Next Tip First Tip