copy table

[peljo]

By Edit Copy and Paste i can copy a table,select the option "Structure only and paste it".In this case the Autonumber begins from 1.is it possible to do it by code ?

 

[TheAceMan1]

How are ya peljo . . .

Have a look at the [blue]CopyObject[/blue] method . . .

See Ya! . . . . . .

 

[fneily]

As far as I can see, if you use the CopyObject method, it copies the table structure and data. You would then need to delete the data in the new table. But when you delete the data, the autonumber is not reset to 1. To have it reset to 1, you can do a compact and repair on the database.
This can be in a macro:
CopyObject Action with appropriate paramters
RunQuery which would be a delete query on the new table
Then runcode which you can find at
Compact and Repair automatically at the start of each day
faq705-4998

 

[TheAceMan1]

How are ya fneily . . .

Yes your correct! For [blue]structure[/blue] only it'll take more than one line . . . no matter what! . . .

See Ya! . . . . . .

 

[TheAceMan1]

peljo . . .

My initial post is shy of what you need. The closet I find in my library is the [blue]TransferDatabase[/blue] method. With it you can [blue]export structure only[/blue] to another DB then import the table. The only problem here is the requirement of an additional DB resource.

There are other avenues such as using a Make Table query. But much more code/SQL is required.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[PHV]

No need of another DB:
DoCmd.TransferDatabase acImport, "Microsoft Access", "\path\to\yourDB.mdb", acTable, "yourOldTable", "yourNewTable", True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Or an SQL way

SELECT yourOldTable.*
INTO yourNewTable
FROM yourOldTable
WHERE 1=2

 

[PHV]

JerryKlmns, I'm afraid your suggestion doesn't copy the index nor the constraints ...

 

[JerryKlmns]

PHV

you are correct as always, but in same cases you just need the fields (names and data types) to store the records, do stuff and when done move them back to original place.
But the Autonumber it does work, doesn 't it?