7

I have a .accfb file from an access 2016 database and am trying to import this database into a SQL Server 2016 developer edition instance.

While I found posts online discussing using import/export data tools in SQL Server and also migration assistant for access, neither have worked out for me.

Does anyone know how to easily move a database from access 2016 to sql server 2016? More specifically an "accdb" file type?

All suggestions are greatly appreciated!

2
  • What about the import/export wizard did not work for you? Do you have access to the source of the .accfb file or just the .accfb file itself? If the later, can you spin up Access on the dev instance in order to properly establish a connection with SQL Server? Jan 12, 2017 at 2:28
  • I just have the accdb file unfortunately @SQL_Underworld .
    – choloboy
    Jan 12, 2017 at 13:33

3 Answers 3

7

1.Open MS SQL Server Import and Export Wizard.

2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.

3.Click “Properties” enter the location of the .accdb file in the “Data Source” field. That is the physical location of the Access database file, like C:\Temp.

4.Under login information, choose a blank username and be sure to check “Blank Password”.

5.Test the connection. If it works, click OK.

6.For the “Data Destination” choose “Microsoft OLE DB Provider for SQL Server”.

7.Enter the MS SQL Server name or choose from the dropdown menu.

8.Enter your SQL credentials and select the database you wish to import the data to, the database should show up in the dropdown menu if your MS SQL info is correct.

9.Select “Copy data from one or more tables or views”.

10.Select the tables you want copied and edit the mappings if needed.

11.Continue through the wizard and hit finish to begin the import.

4
  • 3
    I do not have "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as an option in step 2. I performed the steps from the following link and still don't have that option. stackoverflow.com/questions/24517167/…. Any thoughts?
    – Cory
    Jul 26, 2018 at 15:37
  • What version of Access are you using? Also make sure you are installing the correct version for your system. X86 or 64 bit. Jul 27, 2018 at 17:34
  • I have the latest version of Access included in Office 365. Everything I've installed is x86, so we should be good there. I actually followed this process to get the correct "Data Source" to show up in visual studio and it worked. It just doesn't seem to be working for SSMS (v17.8.1).
    – Cory
    Jul 27, 2018 at 20:56
  • I can't install the MS Office 12 Access Database Engine Provider becaues my companies IT Department has given me a computer with both 64 and 32 bit Office products installed (which I thought was impossible). Both installers will not allow me to install.
    – tnk479
    Mar 16, 2019 at 19:43
4

For me it didn't work to try to import directly from the ACCDB file. I had to save in Access as an mdb file and do the import that way.

3

I just had a similar issue with SQL Server 2017.

My setup is 64 bit SQL Server 2017 and Access 2016. It seems that SSMS is 32 bit and wants to show you the 32 bit version of the wizard.

If the driver you need (Microsoft.ACE.OLEDB.16.0) is not showing up, install the AccessDatabaseEngine_X64.exe and then from the windows search bar start typing Import and you should see the 64 bit version of the SQL Server Import and Export Wizard

When you browse for the database, change the filter to All files and select your .accdb file.

3
  • 1
    After installing 64-bit AccessDatabaseEngine_X64.exe, you can additionally install the 32-bit version using Command Prompt to install AccessDatabaseEngine.exe /quiet. Once this is done, you see 3 options to import data from Access including Microsoft.ACE.OLEDB.16.0 needed to import from .accdb files.
    – cusman
    Dec 22, 2020 at 21:55
  • thank you! this saved me big time. But I dont understand why the import wizard in MSQL server managment studio is not the same as pulling it up from the command line. I'm just thankful this worked - saved me a lot of time having to resave files as MDB!
    – JamiRae
    Mar 10, 2022 at 23:58
  • 1
    oh one more thing. I also, by default, it only looked for .mdb files when I was browsing for the source Access file. I changed that to all *. and selected the *.accdb file I needed to import.
    – JamiRae
    Mar 11, 2022 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.