Forum Discussion
Import ACCDB to SQL SERVER
Hi... working with SQL SERVER MANAGEMENT STUDIO 20.1, I try to import accdb database, and it is impossible. It only allows me to import data from mdb databases. Any help please? Thanks in advanced!...
Importing data from an ACCDB database (Microsoft Access 2007 or later) into SQL Server using SQL Server Management Studio (SSMS) can sometimes be challenging due to driver and compatibility issues. Here's a step-by-step guide to help you import your ACCDB database:
Steps to Import ACCDB Database into SQL Server
Install Microsoft Access Database Engine:
Download and install the appropriate version of the Microsoft Access Database Engine.
Microsoft Access Database Engine 2016 Redistributable
Choose the correct version (32-bit or 64-bit) based on your SQL Server instance and SSMS.
Open SQL Server Management Studio (SSMS):
Open SSMS and connect to your SQL Server instance.
Open the Import Data Wizard:
Right-click the database where you want to import the data.
Select Tasks > Import Data to open the SQL Server Import and Export Wizard.
Choose a Data Source:
In the Choose a Data Source step, select Microsoft Access (if available) or Microsoft Office 12.0 Access Database Engine OLE DB Provider.
If the Microsoft Access option is not available, choose Microsoft Office 12.0 Access Database Engine OLE DB Provider.
Click Properties.
Configure Data Source:
Set the Data Source to the path of your ACCDB file.
Example: C:\path\to\your\database.accdb
Click Test Connection to ensure that the connection is successful.
Choose a Destination:
In the Choose a Destination step, select SQL Server Native Client 11.0 or the appropriate SQL Server destination.
Configure the SQL Server connection details (server name, authentication method, database).
Specify Table Copy or Query:
Choose whether to copy data from one or more tables or to write a query to specify the data to transfer.
For most scenarios, choose Copy data from one or more tables or views.
Select Source Tables and Views:
Select the tables and views from your ACCDB file that you want to import.
Map the source tables to the destination tables in SQL Server.
Run the Import:
Review the summary of the import operation.
Click Finish to start the import process.
Monitor the progress and ensure there are no errors.
Troubleshooting Tips
Driver Issues: Ensure you have the correct version of the Access Database Engine installed (32-bit vs. 64-bit).
Permissions: Verify that you have the necessary permissions to read the ACCDB file and write to the SQL Server database.
Compatibility: If the import still fails, consider converting the ACCDB file to an MDB file using Access if feasible, and then import it.
Steps to Import ACCDB Database into SQL Server
Install Microsoft Access Database Engine:
Download and install the appropriate version of the Microsoft Access Database Engine.
Microsoft Access Database Engine 2016 Redistributable
Choose the correct version (32-bit or 64-bit) based on your SQL Server instance and SSMS.
Open SQL Server Management Studio (SSMS):
Open SSMS and connect to your SQL Server instance.
Open the Import Data Wizard:
Right-click the database where you want to import the data.
Select Tasks > Import Data to open the SQL Server Import and Export Wizard.
Choose a Data Source:
In the Choose a Data Source step, select Microsoft Access (if available) or Microsoft Office 12.0 Access Database Engine OLE DB Provider.
If the Microsoft Access option is not available, choose Microsoft Office 12.0 Access Database Engine OLE DB Provider.
Click Properties.
Configure Data Source:
Set the Data Source to the path of your ACCDB file.
Example: C:\path\to\your\database.accdb
Click Test Connection to ensure that the connection is successful.
Choose a Destination:
In the Choose a Destination step, select SQL Server Native Client 11.0 or the appropriate SQL Server destination.
Configure the SQL Server connection details (server name, authentication method, database).
Specify Table Copy or Query:
Choose whether to copy data from one or more tables or to write a query to specify the data to transfer.
For most scenarios, choose Copy data from one or more tables or views.
Select Source Tables and Views:
Select the tables and views from your ACCDB file that you want to import.
Map the source tables to the destination tables in SQL Server.
Run the Import:
Review the summary of the import operation.
Click Finish to start the import process.
Monitor the progress and ensure there are no errors.
Troubleshooting Tips
Driver Issues: Ensure you have the correct version of the Access Database Engine installed (32-bit vs. 64-bit).
Permissions: Verify that you have the necessary permissions to read the ACCDB file and write to the SQL Server database.
Compatibility: If the import still fails, consider converting the ACCDB file to an MDB file using Access if feasible, and then import it.
- Hi. Thanks for response.
If I choose "Microsoft Access (Jet Database Engine)" only allow me to select MDB databases, if I select an ACCDB database i get the error "database format is not recognized".
On the other hand, option "Microsoft Office 12.0 Access Database Engine OLE DB Provider" does not appears in my list of choices.Jota_Q , if you want to use data provider like ACE, then you have to install it first.
