Retrieving existing table definition to SQL-create statement

[Guest]

Hi all,
Is there a way take an existing mdb-table and export it to a SQL statement
(CREATE TABLE ....) so that when one runs the SQL one creates the same
(empty) table?

 

[Douglas J Steele]

Not built into Access (although there are some 3rd party tools that do
that). You could write the DDL yourself.

 

[Guest]

One of my favorite tricks:

SELECT MSysObjects.* INTO tblMakeEmptyTable
FROM MSysObjects
WHERE 1 = 2;

Since 1 never equals 2, no records are appended but the fields are created.

Now if you are talking about taking this SQL statement to another database,
that's a whole nother kettle of fish.

 

[Guest]

Thanks, Douglas,
but building my own ddl is a little bit out of my scope...
I was hoping there was something to export table structures with all their
indexes,field properties, loopup fields, relationships ....to SQL
15 years ago, Oracle could do it..so why not access...

its a pity

 

[Guest]

Hi Jerry, I love kettle of fish..

What i wanted to do with vbcode is check in a backend if a table exists and
if not create it and build its relationships with other existing tables.
I have the table with its relationships in another backend......unfortunatly
the first backend is not on my computer..so i cannot easely 'copy' it .
So i wanted to generate the necessairy SQL from my backend to recreate it
all in the other backend....
Can you still follow me...?

 

[Douglas J Steele]

If you're trying to make a copy of a database, you can always use the
TransferDatabase method. One of its options allows you to transfer only the
structure, not the data. Of course, this isn't a "portable" solution: you'd
need the original database to be present to create the copy.

 

[Guest]

I understand now. For my Oracle databases I have a tool called TOAD that can
export everything including tables, triggers, views, etc., as SQL so that you
could rebuild an empty database.

 

[Guest]

Yes, that is exactly what i meant....
Do you know of a tool somewhere that does the same thing for Access databases?