11

Is it possible to generate a sql script that contains CREATE and INSERT statements for some tables within an Access database?

digiarnie
  • 253
  • 1
  • 2
  • 6

3 Answers3

4

Take a look at Access To MySQL conversion tool, it allows the generation of a dump file ...

gtgaxiola
  • 103
  • 4
aleroot
  • 3,160
  • 5
  • 28
  • 37
  • This was still relevant for us as we're still leaving the bad, old Access world. We didn't dump, but let it do the full migration. I had some trouble though where this tool couldn't drop & create tables in an empty SQL Server 2017 database. However, this was due to Windows Authentication and apparently some lacking rights (it may be that the tool internally uses SQL TRUNCATE for unlogged deletes). So it was solved by logging in as 'sa'. :) Upvote for an otherwise still perfect solution! – Jonas Nov 02 '18 at 13:06
  • Can this tool dump individual tables one at a time? – posfan12 Dec 16 '19 at 17:12
  • I can't get this program running. It asks for 32-bit version of MySQL ODBC driver, and 32-bit version of something called AccessDatabaseEngine, which Microsoft doesn't allow you to install on 64-bit systems. Of course, the homepage of this tool doesn't mention any of this. Just wasted an hour on this junk. – posfan12 Dec 16 '19 at 17:50
1

I've used Access to MySQL and Access to MSSQL (both free) before with success.

It can produce SQL Dumps for their respective targets. Download link

andyhky
  • 2,652
  • 1
  • 25
  • 26
0

Jet/ACE supports DDL, yes, but not as fully as some other SQL dialects. It also has its idiosyncracies.

But keep in mind that Jet/ACE can execute only one statement at a time -- no batching of multiple statements in a single "script." Each DDL statement has to be executed one at a time, in succession.

David W. Fenton
  • 232
  • 1
  • 7