14

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.

Improve this question
0

3 Answers 3

Reset to default
29

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

No reason except the fact I didn't know there was such an option available :)
5

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.

Improve this answer

5 Comments

I'm a fan of the SSMA for Access 4.2, and recently saw an announcement of a new version.
I've used this on a few databases now and it's a breeze.
Link's dead now, redirects to SQL Server 2012's page.
The link is updated and active again.
They seem to be up to version 6.x now. It seems to work nicely. Thanks. Especially since the SSMS import option didn't seem to allow me to import a .accdb file from Access 2013 into a SQL2008 express database.
2

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.

Improve this answer

2 Comments

This likely wouldn't do the indexes or relationships though.
Isn't this the same solution he is trying to avoid? (see Only Solutions link). Not sure why he felt it would be so crazy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.