TransferDatabase - Creating a DB to Transfer info from 1 DB into another (1 Viewer)

CNx · Oct 24, 2008

I'm creating VBA code in a new Access project to automatically produce a test environment. The database I am attempting to create a test environment for is a split database; meaning the GUI and the data are in two seperate .mdb files.

I am using Access 2003. The new Access project is to:

1. Copy the production version the live database into another folder (DONE)
2. Rename the production tables with the prefix "Prod_" (DONE)
3. Make structural copies of all the tables in the newly copied database (QUESTION)

I am using TransferDatabase to make the structural copies. The code below is as such:

Code:

Dim acc As Access.Application
    Dim aobj As AccessObject
    Dim i As Integer
    Dim strName As String
 
    Set acc = New Access.Application
    acc.OpenCurrentDatabase (testDBLocation)
     '
 
    For i = acc.CurrentDb.TableDefs.Count - 1 To 8 Step -1
        If Left(acc.CurrentDb.TableDefs(i).Name, 5) = "Prod_" Then
            tableToCopy = acc.CurrentDb.TableDefs(i).Name
            tableToCreate = Replace(acc.CurrentDb.TableDefs(i).Name, "Prod_", "")
             DoCmd.TransferDatabase acExport, "Microsoft Access", testDBLocation, acTable, tableToCopy, tableToCreate, True
        End If
    Next i

I get the following error when I am attempting to copy the tables: Run-Time Error 3011 - The MS JET Engine could not find the object "Prod_zz"....

The problem it seems is that I'm using a mediatary DB (I'll it C) to transfer information from the live DB (label it A) into a copied DB (label it B).

How do I use the VBA function TransferDatabase to copy information from A into B using Project C?

CyberLynx · Oct 25, 2008

To be honest, and I could obviously be wrong here, I'm not sure you can do that with the TransferDatabase Function. Not the end of the world though, you can do it with a SELECT query. Here are some examples:

The Query samples shown here to Copy a Table are based off of the following principle Query:

SELECT * INTO

IN [DestinationDatabase] FROM [SourceDatabase];

___________________________________________________________________________________________

Copy a Table (STRUCTURE and DATA) from Local Database To a External Database .

Code:

[COLOR="DarkGreen"]'Copy a Table (STRUCTURE and DATA) from Local Database To a External Database.
'MyOrigTable_A Table is located in the Local Database making this Call
'NewTableIn_B Table will be Created in the External Database located at C:\MyDatabases\MyDB_B.mdb[/COLOR]
Dim StrgSQL As String

StrgSQL = "SELECT * INTO [NewTableIn_B] IN 'C:\MyDatabases\MyDB_B.mdb' FROM [MyOrigTable_A];"

[COLOR="DarkGreen"]'Execute the Query...[/COLOR]
CurrentDb.Execute StrgSQL, dbFailOnError

Copy a Table (STRUCTURE ONLY) from Local Database To a External Database.

Code:

[COLOR="DarkGreen"]'Copy a Table (Structure Only) from Local Database To a External Database.
'MyOrigTable_A Table is located in the Local Database making this Call.
'NewTableIn_B Table will be Created in the External Database located at C:\MyDatabases\MyDB_B.mdb[/COLOR]
Dim StrgSQL As String

StrgSQL = "SELECT * INTO [NewTableIn_B] IN 'C:\MyDatabases\MyDB_B.mdb' FROM [MyOrigTable_A] WHERE 1 = 2;"

[COLOR="DarkGreen"]'Execute the Query...[/COLOR]
CurrentDb.Execute StrgSQL, dbFailOnError

Copy a Table (STRUCTURE and DATA) from One external Database into the Local Database

Code:

[COLOR="DarkGreen"]'Copy a Table (Structure and Data) from One external Database into the Local Database.
'MyOrigTable_A Table will be created in the Local Database which is making this Call.
'ExternTableIn_B Table is the Table located in the External Database located at C:\MyProcessDatabases\MyDB_B.mdb[/COLOR]
Dim StrgSQL As String

StrgSQL = "SELECT * INTO [MyOrigTable_A] IN '" & Application.CurrentProject.Path & _
          "\" & Application.CurrentProject.Name & "' FROM " & _
          "[;DATABASE=C:\MyProcessDatabases\MyDB_B.mdb;PWD=''].[ExternTableIn_B];"

[COLOR="DarkGreen"]'Execute the Query...[/COLOR]
CurrentDb.Execute StrgSQL, dbFailOnError

[COLOR="DarkGreen"]'Refresh the TableDefs since this is the Local Database.[/COLOR]
Application.CurrentDb.TableDefs.Refresh

Copy a Table (STRUCTURE ONLY) from One external Database into the Local Database[/B]

Code:

[COLOR="DarkGreen"]'Copy a Table (Structure and Data) from One external Database into the Local Database.
'MyOrigTable_A Table will be created in the Local Database which is making this Call.
'ExternTableIn_B Table is the Table located in the External Database located at C:\MyProcessDatabases\MyDB_B.mdb[/COLOR]
Dim StrgSQL As String

StrgSQL = "SELECT * INTO [MyOrigTable_A] IN '" & Application.CurrentProject.Path & _
          "\" & Application.CurrentProject.Name & "' FROM " & _
          "[;DATABASE=C:\MyProcessDatabases\MyDB_B.mdb;PWD=''].[ExternTableIn_B] WHERE 1 = 2;"

[COLOR="DarkGreen"]'Execute the Query...[/COLOR]
CurrentDb.Execute StrgSQL, dbFailOnError

[COLOR="DarkGreen"]'Refresh the TableDefs since this is the Local Database.[/COLOR]
Application.CurrentDb.TableDefs.Refresh

Copy a Table (STRUCTURE and DATA) from One External Database to another External Database.

Code:

[COLOR="DarkGreen"]'Copy a Table (STRUCTURE and DATA) from One external Database to another External Database.
'ExternTable_A Table is a Table located within the External Database which is located at C:\MyExternalDatabases\MyDB_A.mdb
'ExternTable_B Table will be created within the External Database located at C:\MyExternalDatabases\MyDB_B.mdb
'This code is run from within [B]MyDB_C.mdb[/B][/COLOR]
Dim StrgSQL As String

StrgSQL = "SELECT * INTO [ExternTable_B] IN 'C:\MyExternalDatabases\MyDB_B.mdb' FROM " & _
          [;DATABASE=C:\MyExternalDatabases\MyDB_A.mdb;PWD=''].[ExternTable_A];"

[COLOR="DarkGreen"]'Execute the Query...[/COLOR]
CurrentDb.Execute StrgSQL, dbFailOnError

Copy a Table (STRUCTURE ONLY) from One External Database to another External Database.

Code:

[COLOR="DarkGreen"]'Copy a Table (STRUCTURE and DATA) from One external Database to another External Database.
'ExternTable_A Table is a Table located within the External Database which is located at C:\MyExternalDatabases\MyDB_A.mdb
'ExternTable_B Table will be created within the External Database located at C:\MyExternalDatabases\MyDB_B.mdb
'This code is run from within [B]MyDB_C.mdb[/B][/COLOR]
Dim StrgSQL As String

StrgSQL = "SELECT * INTO [ExternTable_B] IN 'C:\MyExternalDatabases\MyDB_B.mdb' FROM " & _
          [;DATABASE=C:\MyExternalDatabases\MyDB_A.mdb;PWD=''].[ExternTable_A] WHERE 1 = 2;"

[B]'Execute the Query...[/B]
CurrentDb.Execute StrgSQL, dbFailOnError

Food for thought anyways

.

TransferDatabase - Creating a DB to Transfer info from 1 DB into another (1 Viewer)

CNx

New member

CyberLynx

Stuck On My Opinions

Similar threads

Users who are viewing this thread