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:
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?
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?