2

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!

  • 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? – SQL_Underworld Jan 12 '17 at 2:28
  • I just have the accdb file unfortunately @SQL_Underworld . – choloboy Jan 12 '17 at 13:33
4

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.

  • 1
    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 '18 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. – bwilliamson Jul 27 '18 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 '18 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 at 19:43
3

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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