Copy Database with Data Using DAO - December 2002 - or ADO - July 2003

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 [2002]. 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.

Demo Program

Copy Database Sample Using DAO
DAO Version
Copy Database Sample Using ADO
ADO Version
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

DAO Sample
Private Function DoCopyData(dbIn As Database, dbOut As Database) As Boolean
    Dim obj As New cCopyDataDAO
    With obj
        .dbIn = dbIn
        .dbOut = dbOut
        If .CopyData Then
            DoCopyData = True
        End If
    End With
End Function
ADO Sample
Private Function DoCopyData(connDBIn As ADODB.Connection, connDBOut As ADODB.Connection) As Boolean
    Dim obj As New cCopyDataADO
    With obj
        DoCopyData = .CopyData(connDBIn, connDBOut)
    End With
End Function

The Process

DAO - Processing steps are:

  1. Create a new database using code similar to: DAO.CreateDatabase "myDB.mdb", dbLangGeneral, dbVersion40

  2. Copy the tables from the existing database to the one just created using DatabaseUpdateUsingDLL.dll.

  3. Copy the data from the existing database to the new one using cCopyDataDAO.cls.

  4. Copy the indexes from the existing database to the new one using DatabaseUpdateUsingDLL.dll.

  5. Copy the relationships from the existing database to the new one using DatabaseUpdateUsingDLL.dll.

ADO - Processing steps are:

  1. Create a new database using code similar to: catDBOut.Create mcsProvider & Me.Text1(1).Text

  2. Copy the tables and indexes from the existing database to the one just created using DatabaseUpdateUsingADODLL.dll.

  3. Copy the data from the existing database to the new one using cCopyDataADO.cls.

  4. Copy the stored procedures and views from the existing database to the new one using DatabaseUpdateUsingADODLL.dll.

  5. 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.

Another Tip

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

Home

Updated 2004/10/05