create table scripts

[shank]

In SQL Server you can generate a script from an existing table that will
create another table just like it.
The script contains all the attributes, etc...

Does Access have a feature like that?
I'd like to generate a script for creating a table.

thanks!

 

[Allen Browne]

JET doesn't have a script like that. DDL in JET is hopeless with setting
properties, and the property settings are inconsistent between versions.

The simplest thing to code is:
DoCmd.CopyObject ,"MyNewTable", acTable, "MyTable"
dbEngine(0)(0).Execute "DELETE FROM MyNewTable;"
but that might be inefficient to exeucte if the table has many records.

If you don't mind the user supplying the new name and asking for structure
only:
DoCmd.SelectObject acTable, "MyTable", True
RunCommand acCmdCopy
RunCommand acCmdPaste

In practice, you very rarely create tables in an end-user database. Would it
be feasible to stash a blank copy of the table without data in your original
database as a template, and then use the CopyObject solution when you need
it?


If you really need to write this solution, it would be very lengthy. You
will need a combination of DAO and ADOX, because neither one is able to
create all the field types and properties on its own. Then there is the
Indexes collection of the TableDef, and you may wish to exclude the indexes
that are present only to manage the foreign key field(s) in the relations
that have enforced integrity.

 

[Tim Ferguson]

Does Access have a feature like that?
I'd like to generate a script for creating a table.

There are a lot of db utilities out there for modelling and reverse-
engineering a database, including Jet and SQL Server etc. They will
usually generate DDL for various target platforms. MS Visio is one
example (you need the enterprise edition, I think); Embarcadero have
various products. Try googling for something like "reverse engineer ms
access database".

On the other hand, if you just need to generate the same table on a
regular basis then: (a) you might have a design problem; and (b) it's not
hard to make a DDL command using CREATE TABLE. If you are creating and
pulling down a temp table on a regular basis, then you might be better
off emptying and repopulating the same one in order to reduce bloating
and corruption of the mdb file. Alternatively, make the temp table in a
brand new temporary mdb and erase the whole file afterwards.

Hope that helps


Tim F