Copy table structure in Access 2000

  • Thread starter Thread starter Del
  • Start date Start date
D

Del

I'm trying to create a second table in my DB with the same structure as an
existing table but without the data. This code copies both the structure and
the data.

DoCmd.CopyObject , "Table2", acTable, "Table1"

How can I copy just the structure?
 
Del said:
I'm trying to create a second table in my DB with the same structure as an
existing table but without the data. This code copies both the structure
and
the data.

DoCmd.CopyObject , "Table2", acTable, "Table1"

How can I copy just the structure?
Click to expand...


If you want to do it manually, you can copy and paste, choosing "Structure
Only" when prompted. If you want to do it programmatically, you can use
TransferDatabase, specifying the current database as the target of an export
and giving True for the StructureOnly argument, like this:

Docmd.TransferDatabase acExport, "Microsoft Access", _
CurrentDb.Name, acTable, _
"Table1", "Table2", _
True
 
I'm trying to create a second table in my DB with the same structure as an
existing table but without the data. This code copies both the structure and
the data.

DoCmd.CopyObject , "Table2", acTable, "Table1"

How can I copy just the structure?
Click to expand...

You can simply select the existing table,right-click copy, then
right-click and select Paste. When the renaming dialog comes up click
on Structure only.

If you need to do this via code, have you tried deleting the data from
the new table after you copy the existing table?

DoCmd.CopyObject , "Table2", acTable, "Table1"
CurrentDb.Execute "Delete Table2.* From Table2;",dbFailOnError

Then compact the database.
 
Del said:
I'm trying to create a second table in my DB with the same structure as an
existing table but without the data. This code copies both the structure
and
the data.

DoCmd.CopyObject , "Table2", acTable, "Table1"

How can I copy just the structure?
Click to expand...

TransferDatabase will do the job. Just specify CurrentDb.Name for the
receiving database.

DoCmd.TransferDatabase acExport, "Microsoft Access", _
CurrentDb.Name, acTable, "SourceTableName", _
"DestinationTableName", StructureOnly:=True
 
Thanks, this worked for me.

There is one confusing thing. The default for the database type is supposed
to be Microsoft Access, but when I leave it blank (comma only) I get an
error. Oh well.
 
Back
Top