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?

Subjoin

I have previously written some notes in #699339 – Ers

Answer 1

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

Lenity

Answer 2

I just found and tried out this tool: jet-tool. It seems to work well for Access 2010.

Abnormality

needed a 2003 access database (mdb) instead of 2016 (accdb) – Capparidaceous

Looks like it does support mdb format, at least in the last version. – Swum

Answer 3

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.

Miculek

+1 - Hehe - hadn't really considered that. Funny how the simplest solutions are often the ones you overlook. Unfortunately, our product may be released as a standalone executable, so probably not plausible in this case. Although I guess we could somehow embed the binary data in the exe... – Subjoin

Answer 4

I don't know what tools you have on your development machine, so this 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 statements to re-create the database. You will have the Create statements you are looking for and they will be placed in a text file.

Imperceptible

Answer 5

I use a free utility called MDB Viewer Plus (http://www.alexnolan.net/software/mdb_viewer_plus.htm). Launch it, open your db, then select your table. On top menu, select "Table > Generate SQL - CREATE".

Coulombe

This worked well for me, thank you. As a side note, for some reason you cannot download the MDB Viewer Plus directly from Alex Nolan's website. It is available on several public software archives. – Openwork

Bad news. I downloaded MDB Viewer Plus Version 2.63 and the option "Table > Generate SQL - CREATE" is missing. – Soubrette

You can find v.2.52 here: s000.tinyupload.com/index.php?file_id=00286856232743084617. This version still has the export option. – Coulombe

Answer 6

The Bullzip is very good to this. Very simple. See bullzip Access to MySQL for example

It is possible export any tables to SQL or migrate automatically.

Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.

• Wizard interface.
• Transfer data directly from one server to another.
• Create a dump file.
• Select tables to transfer.
• Select fields to transfer.
• Transfer password protected databases.
• Supports both shared security and user-level security.
• Optional transfer of indexes.
• Optional transfer of records.
• Optional transfer of default values in field definitions.
• Identifies and transfers auto number field types.
• Command line interface.
• Easy install, uninstall and upgrade.

Acey

Answer 7

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

Koetke

Answer 8

I have been using for years a tool called database.net from https://fishcodelib.com/Database.htm
I generally use it on client's site as a portable version of SSMS (drop and run), but it can handle a multitude of RDBMSes, including Access.
Connect to your mdb/accdb, right click any table, choose SCRIPT AS, Create, and you're done.
If you right click Tables header, you can select multiple tables to generate, but I think it's a feature of the paid version.
I have no acquaintance with them, just a happy client.

Quiz

Answer 9

I found an easy way to go:

Export-> ODBC Database

and then retrieve the SQL form there (e.g via pgadmin on postgres)

Stratocumulus

Answer 10

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.

Selene