SQL to recreate database structure

[Steve Baenett]

When I'm developing, I tend to mess with my database in MS-Access. Once it's
complete, I like to create an SQL script of CREATE TABLE and CREATE INDEX
statements to recreate the database tables and fields. In the past, I've
always done this manually, but my databases are becomming more complex and
this is becoming a pain.

Does anyone know of a simple app that can interrogate an MS-Access database
and generate an SQL script that can be used to re-build the database (just
the structure). Ideally, I'd like the script to work for Access and
SQL-Server.

I've seen a couple that do this, but nothing that properly handles primary
and secondary indexes.

Any suggestions?

Thanks
Steve

 

[Steve Barnett]

Guess that's a No then?

 

[John Vinson]

Guess that's a No then?

I've done this by using the Upsizing wizard to put the tables into
SQL/Server, then using the Enterprise Manager tools to generate the
SQL scripts.

It's not all that common (though it would be a good idea in many ways)
for Access developers to use DDL for table creation. Older versions of
Access didn't fully support DDL, and there are still many things that
are simpler to do using the user interface than by executing scripts.
Just for one thing, each SQL query must contain one and only one query
- unlike in SQL/Server or Oracle, you cannot string together one long
multiple set of queries into one SQL string.

John W. Vinson[MVP]

 

[Steve Barnett]

I've more of less given up on finding an app to do this. I've found several
that claim to create the SQL I need to define the database, but they either
generate the wrong or incomplete SQL or miss out the indexes. One app
happily created "int" fields for all of the "doubles" that I had in my
database... very useful.

I've got an old MSDE admin program that I intend to try next to see if it
will connect to SQLExpress. I don't want to install the developer copy of
SQL Server for this, as my experience with it tells me that it'll probably
cripple the performance of my machine for a function that I'll only need
once in a while.

Thanks anyway
Steve

 

[John Vinson]

I've more of less given up on finding an app to do this. I've found several
that claim to create the SQL I need to define the database, but they either
generate the wrong or incomplete SQL or miss out the indexes. One app
happily created "int" fields for all of the "doubles" that I had in my
database... very useful.

I've got an old MSDE admin program that I intend to try next to see if it
will connect to SQLExpress. I don't want to install the developer copy of
SQL Server for this, as my experience with it tells me that it'll probably
cripple the performance of my machine for a function that I'll only need
once in a while.

Tom Wickerath suggested Visio, if you should happen to have that
installed. I haven't used it but I'm going to investigate!

John W. Vinson[MVP]

 

[Tom Wickerath]

Visio Professional can be used to create a database diagram from a Microsoft
Access (JET) database or a SQL Server database. However, you will need the
Enterprise edition to go the other way, from Visio to create a database.

I suggested this to John as a possibility, but I've only seen this done one
time, so I really cannot vouch for how reliable it is.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Steve Barnett]

Alas, I am Visio-less - I never really saw th e benefits of Visio, though I
suspect that has more to do with never having used it rather than it's
actual capabilities.

However, I'm also 4/5ths of the way through an app that maps MS-Access
databases - giving me a list if the tables, fields (+ attributes), indexes
and, hopefully in the next day or so, the SQL necessary to reproduce the
table.

It's something I started many years ago as an introduction to C# and VS.Net
2002. I've added the index parts and now just need to deal with the
translation of OLEDB data types in to something Access will understand and
I'll be there. I used the app to document my databases - it allows me to add
a comment to describe what the table is used for and what each individual
field is used for.

Sad what you do when you're on holiday - well, when I'm not watching Buffy
(the Vampire Slayer) anyway. Now I really look sad!

Thanks
Steve