[thelist] MS Access CREATE TABLE

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "David Mitchell" <dave at dbmdata.com>
Subject: [thelist] MS Access CREATE TABLE

: I am stuck working with an MS Access database. I am much
: more familiar working with SQL Server. This leads me to my
: question: Is there any way to generate CREATE TABLE scripts
: for all the tables in an access database sorta the same way
: one can do this with SQL Server?
:
: Can't seem to find anything in Access "Help" (the paperclip ain't
: helping me much...) or on Google.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi,

If you are using a Jet database (.mdb) rather than a Data Project (.adp),
then there's no native way to do this. However, if you are familiar with
ADO, then there are two things I could suggest:

a) use the ADO schema to get the info about each of the tables in the
database, and have a little VBScript app write out the SQL statements for
you

b) use ADOX to do the same as in (a)

If you are not familiar with ADO, then you could also interrogate the hidden
MSys* (particularly MSysObjects) tables to get the info about the database
out, and create your CREATE TABLE scripts.

In general, CREATE TABLE scripts aren't as necessary in Access/Jet, since
it's a file-based database. You can just make a copy of the database file
itself and move it anywhere you like.

Cheers
Ken

Microsoft MVP - Windows Server (IIS)