Copy table structure and data - using parameter

[chunchun]

I am getting a

run time error 3011: The microsoft database engine could not find the object '& TableToCopy &'. Make sure the object exists and that you spell its name and path name correctly

when I use the below command to copy a table with its structure and data in the same database. I am using variable for the tablenames instead of the actual table names because, I might have to come up with a table name based on a monthdate. That is why I have table names in parameter. Is there something wrong with the way I am passing the table names as parameters?

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "& TableToCopy &", "& TableToCreate &", True

 

[DarrellW]

You will need to dimension two tabledef variables, you can't get away with using the variable name on items such as forms, tables etc without defining the type first as follows: -

Dim tdf_orig As TableDef
Dim tdf_create as TableDef

You can then assign a table name to temporary variable as follows: -

Set tdf_orig = db.TableDefs("tblorig")
Set tdf_create as db.TableDefs("tblcreate")

Your statement would then read: -

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, tdf_orig, tdf_create, True

 

[chunchun]

I am getting a compile error for the statement.

Set tdf_create as db.TableDefs("tblcreate")

 

[chunchun]

I changed the 'as' to '='

Set tdf_create = db.TableDefs("tblcreate")

The compile error goes away, but I get a run time error 3265: Item not found in this collection.

Should I have the table created? But, I want to create a new table in the VBA code every time rather than have the tables already created.

Please help..

 

[dhookom]

What happens if you get rid of all the extra ampersands and quotes?

[green]'assuming TableToCopy and TableToCreate are legitimate values[/green]
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, TableToCopy , TableToCreate , True

Duane
Hook'D on Access
MS Access MVP

 

[chunchun]

I got rid of all the quotes and ampersands. The command I am using right now is

Dim TableToCopy As TableDef
Dim TableToCreate As TableDef
Dim myDB As DAO.Database

Set myDB = CurrentDb
Set TableToCopy = myDB.TableDefs("Report_Curr")
Set TableToCreate = myDB.TableDefs("Report_Oct")

DoCmd.TransferDatabase acExport, "Microsoft Access", myDB, acTable, TableToCopy, TableToCreate, True


I am using access 2007. I have the above code within the following Sub.
Private Sub Report_Open(Cancel As Integer)

Since I had the above code in a report, I created a test form with a button. For the button on_click event I call a function to do the above. I am assuming that the above docmd.transferdatabase will create a new table called "Report_Oct" and copy the structure of the existing "Report_Curr" table.

When I click on the form, I get a Run time error 3265: item not found in this collection

I am not sure what I am doing wrong here....
please help....

 

[PHV]

Why not simply this ?
DoCmd.TransferDatabase acExport, "Microsoft Access", myDB, acTable, "Report_Curr", "Report_Oct", True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

chunchun,
If you check Help, you will find that most of the arguments in DoCmd.Trans... are names of objects. You have spent extra code creating objects to use as arguments. You simply need the names of the tables and mdb.

DoCmd.TransferDatabase acExport, "Microsoft Access", Currentdb.Name, acTable, "Report_Curr", "Report_Oct", True

Duane
Hook'D on Access
MS Access MVP

 

[chunchun]

It works if the object names are mentioned directly in the Docmd.Transferdatabase.

But I was looking pass the object name as a parameter/variable in the command, because I would like to create the Table name every month dynamically, by concatenating a string of table name with the specific month.

If I could do that then it would have been easier for me. Otherwise I will have to use docmd 12 times in an if statement to copy the 12 month tables.

If any body has idea about passing object name as a parameter/variable in the Docmd.transferdatabase, please let me know.

Thanks

 

[dhookom]

You can use a string or a string memory variable. Your issue before was using an object variable rather than a string variable.

Dim strNewTable as String
Dim strOldTable as String
strNewTable = "tblNewName"  [green]'or pull it from anywhere[/green]
strOldTable = "tblOldName"  [green]'or pull it from anywhere[/green]

DoCmd.TransferDatabase acExport, "Microsoft Access", Currentdb.Name, acTable, strOldTable, strNewTable, True

Duane
Hook'D on Access
MS Access MVP

 

[chunchun]

Thanks to all of you.
The string definition worked.