8

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!

choloboy
  • 235
  • 1
  • 2
  • 7

3 Answers3

8

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
bwilliamson
  • 511
  • 3
  • 13
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.

Michael Green
  • 25,255
  • 13
  • 54
  • 100
Luis
  • 41
  • 1
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.

Mike Cheel
  • 235
  • 1
  • 10