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:


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?

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 [Table] IN [DestinationDatabase] FROM [SourceDatabase];

__________________________________________________ _________________________________________

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


'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
Dim StrgSQL As String

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

'Execute the Query...
CurrentDb.Execute StrgSQL, dbFailOnError



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


'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
Dim StrgSQL As String

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

'Execute the Query...
CurrentDb.Execute StrgSQL, dbFailOnError



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


'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
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];"

'Execute the Query...
CurrentDb.Execute StrgSQL, dbFailOnError

'Refresh the TableDefs since this is the Local Database.
Application.CurrentDb.TableDefs.Refresh



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


'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
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;"

'Execute the Query...
CurrentDb.Execute StrgSQL, dbFailOnError

'Refresh the TableDefs since this is the Local Database.
Application.CurrentDb.TableDefs.Refresh



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


'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 MyDB_C.mdb
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];"

'Execute the Query...
CurrentDb.Execute StrgSQL, dbFailOnError



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


'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 MyDB_C.mdb
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;"

'Execute the Query...
CurrentDb.Execute StrgSQL, dbFailOnError



Food for thought anyways :)

.