Automatically Generate SQL from existing MS Access table

Question

I've just designed a large database by playing around in MS Access. Now that I'm happy with the design, I need to be able to generate this same database (tables, relationships, etc.) from code.

Rather than hand-writing the SQL CREATE statements (which will be long, tedious, and error-prone process), I was wondering whether there was a shortcut. I seem to recall from my limited exposure to MySql that I was able to export an entire database as an SQL statement that can then be run in order to regenerate that database.

Do you know of a way to do this in MS Access, either through the GUI, or programmatically?

Answer 1

The quick and dirty, easy, perfectly legitimate way to do this is just copy the .mdb file. Empty out the data if you need to - usually there are static tables that are handy to leave populated, however.

Answer 2

For free for 30 days (then $30) you can give DBWScript a go, looks like its what you are asking for, although not in native Access GUI or programmatically

Answer 3

The thing that you're mentioning in MySQL is sql dumping. Very useful feature. If you want to migrate the database to mysql, here's a helpful article.

http://www.kitebird.com/articles/access-migrate.html#TOC_4

Answer 4

I don't know what tools you have on your development machine, so the may or may not be helpful.

You can easily transfer your Access database to Microsoft SQL Server using the Upsizing Wizard.

The express edition of SQL Server is available for free > here.

You will also want to get the free Management Studio Express.

Using these free graphical based tools you can easily generate the SQL statments to re-create the database. You will have the Create statments you are looking for and they will be placed in a text file.

Answer 5

This thread may be of interest: http://stackoverflow.com/questions/698839/how-to-extract-the-schema-of-an-access-mdb-database/699516#699516

Answer 6

Compare'Em http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm The free version creates VBA while the $10 pro version gives you DDL statements.