Automatically generate a "create table" statement

[Rob]

Has anyone here written up (or know of) some code to automatically generate a
"CREATE TABLE" SQL statement based upon the structure of an existing table?
I'm thinking of a VBA function that will return the appropriate statement
given the name of a table in the db.

Along the same lines, how about the appropriate "ALTER TABLE" statement to
build index(es?) that exist on a table?

MySQL has a cute little feature that does this, I would have thought that
Access does but I haven't seen it....

Thanks.

 

[Marshall Barton]

Has anyone here written up (or know of) some code to automatically generate a
"CREATE TABLE" SQL statement based upon the structure of an existing table?
I'm thinking of a VBA function that will return the appropriate statement
given the name of a table in the db.

Along the same lines, how about the appropriate "ALTER TABLE" statement to
build index(es?) that exist on a table?

Why on earth would you want to do such a thing? It sounds
like a severe violation of the Normalization Rules.

Because it should not be necessary to do that, the examples
for it are few and far between. It's pretty advanced DAO
programming, but doable.

 

[Jeff Boyce]

Rob

I'm with Marsh, ... why?

If you'll describe a bit more about what business need you figure this
approach will help you solve, folks here may be able to offer alternate
approaches.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Rob]

Why on earth would you want to do such a thing? It sounds
like a severe violation of the Normalization Rules.

Because it should not be necessary to do that, the examples
for it are few and far between. It's pretty advanced DAO
programming, but doable.

I think you would want to do this for the same reason one records Excel
macros, then alters that code to fit one's true needs: it's a lot easier to
build a table using the GUI than with a CREATE TABLE statement. And if you
have situations, as I do, where tables are frequently built by importing
Excel data ranges, it would be nice to be able to duplicate the structure of
the data as the TransferSpreadsheet routine understands it.

I just want to be able to store the table structure of built tables in code
so that I can drop and re-create tables easily.

I don't really think the process should be THAT hard: the code should build
a skeleton

CREATE TABLE myTableName ( .......... )

and fill in the details by running through each field in the tableDef and
iteratively adding a

field_i type [(size)] [NOT NULL] [WITH COMP] [,]

clause as necessary. The only thing I don't know is how to find/interpret
the type and size from the tableDef attributes.

Thanks,

 

[Marshall Barton]

Why on earth would you want to do such a thing? It sounds
like a severe violation of the Normalization Rules.

Because it should not be necessary to do that, the examples
for it are few and far between. It's pretty advanced DAO
programming, but doable.

I think you would want to do this for the same reason one records Excel
macros, then alters that code to fit one's true needs: it's a lot easier to
build a table using the GUI than with a CREATE TABLE statement. And if you
have situations, as I do, where tables are frequently built by importing
Excel data ranges, it would be nice to be able to duplicate the structure of
the data as the TransferSpreadsheet routine understands it.

I just want to be able to store the table structure of built tables in code
so that I can drop and re-create tables easily.

I don't really think the process should be THAT hard: the code should build
a skeleton

CREATE TABLE myTableName ( .......... )

and fill in the details by running through each field in the tableDef and
iteratively adding a

field_i type [(size)] [NOT NULL] [WITH COMP] [,]

clause as necessary. The only thing I don't know is how to find/interpret
the type and size from the tableDef attributes.

You can get the type codes by looking up Type Property (DAO)
in VBA Help. Then use a Select Case to translate the
numeric code to the text you need in the query.

The size for Text fields come directly from the Size
property. Size is not used for number and date type fields.

 

[Dirk Goldgar]

I think you would want to do this for the same reason one records Excel
macros, then alters that code to fit one's true needs: it's a lot easier
to
build a table using the GUI than with a CREATE TABLE statement. And if
you
have situations, as I do, where tables are frequently built by importing
Excel data ranges, it would be nice to be able to duplicate the structure
of
the data as the TransferSpreadsheet routine understands it.

I just want to be able to store the table structure of built tables in
code
so that I can drop and re-create tables easily.

Access tables and the fields in them have properties that you can't set via
SQL. If you want to copy all attributes of the table, you can do it in a
couple of ways. One simple one is to use TransferDatabase, specifying the
current database as the source for an import. For example:

DoCmd.TransferDatabase _
acImport, "Microsoft Access", CurrentDb.Name, _
acTable, "Table1", "Table1_Copy",
True

It is also possible to copy the table via DAO, but the process is quite
involved. There is code posted in the Microsoft KB somewhere for doing
this.