How to export an entire Access database to SQL Server?

Question

I've just got a lovely Access database, so the first thing I want to do is to move it over to a normal database management system (sqlexpress), but the only solution I've found sounds like craziness.

Isn't there an "export database to .sql" button somewhere? I have around 50 tables and this export might run more than once so it would be great if I didn't have to export all the tables manually. Generating a .sql file (with tables creation and inserts) would also be great since it would allow me to keep that under version control.

I guess if it's not possible to do something simple like this I'd appreciate any pointers to do something similar.

Solution 1

Is there a reason you don't want to use Management Studio and specify Microsoft Access as the data source for your Import Data operation? (Database->Tasks->Import, Microsoft Access as data source, mdb file as parameter). Or is there a reason it must be done from within Microsoft Access?

Solution 2

There is a tool from the SQL Server group - SQL Server Migration Assistant for Access (SSMA Access) There have been comments stating it's a better tool than the Upsizing Wizard included in Access.

Solution 3

A quick-and-dirty way to upsize Jet/ACE tables to any ODBC-accessible database engine:

1. create an ODBC DSN for your database.

2. in Access, select a table, and choose EXPORT from the file menu. Choose ODBC as the type and then select your DSN.

This will export the table and its data with data types that your ODBC driver indicates are most compatible with Jet/ACE's data types. It won't necessarily guess right, and that's why you likely wouldn't do this with SQL Server (for which there are tools that do better translating). But with non-SQL Server databases, this can be an excellent starting place.