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?

I have previously written some notes in http://stackoverflow.com/questions/698839/how-to-extract-the-schema-of-an-access-mdb-database/699516#699516 — Fionnuala, Aug 02 '13 at 13:56

jpmc26 · Answer 1 · 2019-11-06T14:35:20.877

12

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

edited Nov 06 '19 at 14:35

answered Aug 01 '13 at 18:02

jpmc26

28,463
14
94
146

1

needed a 2003 access database (mdb) instead of 2016 (accdb) – Old Badman Grey Dec 07 '16 at 18:04
Looks like it does support mdb format, at least in the last version. – 41686d6564 stands w. Palestine Apr 04 '18 at 10:27

score 6 · Accepted Answer · answered Apr 17 '09 at 01:43

6

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

answered Apr 17 '09 at 01:43

curtisk

19,950
4
55
71

score 5 · Answer 3 · answered Apr 17 '09 at 01:28

5

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.

answered Apr 17 '09 at 01:28

dkretz

37,399
13
80
138

1

+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... – Smashery Apr 17 '09 at 02:13

score 4 · Answer 4 · answered Feb 22 '17 at 08:59

4

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".

answered Feb 22 '17 at 08:59

Niente0

504
3
11

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. – Myrddin Emrys Jun 25 '17 at 17:05
Bad news. I downloaded MDB Viewer Plus Version 2.63 and the option "Table > Generate SQL - CREATE" is missing. – villamejia Oct 30 '20 at 14:56
1

You can find v.2.52 here: http://s000.tinyupload.com/index.php?file_id=00286856232743084617. This version still has the export option. – Niente0 Nov 03 '20 at 09:27

score 4 · Answer 5 · edited Jun 10 '19 at 23:57

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.

score 3 · Answer 6 · edited Mar 02 '17 at 04:19

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.

score 2 · Answer 7 · answered Apr 17 '09 at 02:09

2

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

answered Apr 17 '09 at 02:09

putolaruan

2,084
2
21
32

score 0 · Answer 8 · answered Feb 24 '20 at 13:35

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.

score 0 · Answer 9 · answered Aug 25 '20 at 15:46

0

I found an easy way to go:

Export-> ODBC Database

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

answered Aug 25 '20 at 15:46

Raff

1

score -1 · Answer 10 · answered Apr 21 '09 at 02:01

-1

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.

answered Apr 21 '09 at 02:01

Tony Toews

7,850
1
22
27

Automatically Generate SQL from existing MS Access table

10 Answers10