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!
Best Answer
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.