I want to know how to convert an Access database file into a SQL Server (.mdf file) database?
-
6MDF is an SQL Server file type. You might need to import not convert maybe..Soner Gönül– Soner Gönül2014-02-21 12:32:51 +00:00Commented Feb 21, 2014 at 12:32
-
Spin up an AccessDB and a MSSQL instance and copy the data across, it'll be far easier than editing the files themselvesLiath– Liath2014-02-21 12:33:00 +00:00Commented Feb 21, 2014 at 12:33
Add a comment
|
4 Answers
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.
2 Comments
Olivier Jacot-Descombes
Here is the current link (2017): SQL Server Migration Assistant
SanthoshM
Import from SSMS is much easier than downloading SSMA,installing it and all the hassle. Thanks for the suggestion.
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.
1 Comment
Olivier Jacot-Descombes
SSMA can import queries as views as well.
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
Comments
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.
