How do I convert Microsoft Access file into SQL Server database?

Question

I want to know how to convert an Access database file into a SQL Server (.mdf file) database?

Answer 1

The MS Access "Upsize Wizard" was discontinued with Access 2012. SQL Server Migration Assistant (SSMA) is now recommended. Reference: https://accessexperts.com/blog/2013/01/30/access-2013-is-here-but-wheres-the-sql-server-upsizing-wizard/

However, as Johnny Bones noted, I found it relatively easy to create a new empty SQL Server (2012) database and then import:

SQL Server Management Studio, R-click on the newly created Database | Tasks | Import Data -> SQL Server Import Wizard

• I used "Access Database Engine" instead of "Jet Database Engine" for no reason other than I GUESSED Jet was older. Mine is a throw-away project you may want to research the difference.
• I 'weeded out' the Access queries (views) and just imported the tables.
• So far it looks like everything was imported OK.

Answer 2

The simple answer is; you can not "convert" an Access database to a SQL database. You can, however, import the Access database (tables only) into SQL. Remember that SQL is a true database, and, as such, contains no front end or GUI creation mechanisms. You will still need Access (or C# or VB or another front-end builder) to create the interface.

You may already know this, but in SQL Server a Query is called a View, and Modules are called Stored Procedures. You will need to convert your Access queries and modules accordingly if you're planning on having that all reside server-side.

It's actually pretty easy to import Access tables into SQL Server, you would just create a database on a server, right-click on the database name and choose Tasks --> Import Data. There you will choose Microsoft Access as your Data Source. The rest should be pretty self-explanatory.

Answer 3

JonnyBones wrote a good full answer. Other have made suggestions for tools which may work. However, the SSMA (SQL Server Migration Assistant) is likely to be the best option now. (See this youtube video for help with understanding some of the issues to overcome and an explaination of how to use SSMA.

Befre you consider using other tools, which may do more, you should find out why to use them instead of SSMA. Check out the youtube video as a starting point.

Be aware that the MS Access Upsizing wizard was great but is now discontinued, and there was a bit of a gap before MS created a decent version of SSMA, which is when&why these other tools came into existence.

SSMA should do what you need.

There are some access things that do not get moved to SQLServer and some that do.

eg Access triggers do not. Access tables constraints do, Access boolean datatypes so - of sorts - but you need to do some work. The video will explain most issues well.

Harvey

Answer 4

You can try with the Upsize Wizard or copy content of the table from SHOW TABLE DATA and paste to the target table with the same structure as the source.