How to Import Data from MS Access To SQL Server?

  •   Written By
     
  • Published on
    March 24th, 2014
  • Updated on
    September 8, 2022
  • Read Time
    6 minutes
Summary:-In this article, we will discuss the detailed process to import MS Access data into SQL Server by using SSIS (SQL Server Integration Services). Despite that, if your MDB and ACCDB files of MS Access get corrupted or damaged, you can use the MS Access Recovery Tool, which can easily restore all the data from the corrupt MS Access Database files. 

Microsoft Access and Microsoft SQL Server both act as database management applications. Although the major difference between the two is in how the software is used. Microsoft Access is used in small business applications, but the SQL Server is used on small and large scales. But sometimes, users need to import data from MS access to SQL Server.

Why Need to Import Data from Access to SQL Server?

Organizations and users shift or migrate their data from Access to MS SQL for many reasons. It could be due to data expansion, security measures, performance, concurrent user limits, etc. As SQL is more capable than Access in all these factors, users migrate their data or whole database to the SQL server. It could be performed easily if the user has basic working experience with database management systems.

2 Best Methods to Import MS Access data to SQL Server

Importing the database from MS Access to Microsoft SQL is not that tricky, but you need a little experience working with the database tools like SSIS. Follow the below procedural steps to migrate MS Access data into SQL Server completely.

Method 1: Import Data from MS Access to SQL Server in SSIS

These are the simple steps using which you can easily perform the process to import MS Access data into SQL Server using SSIS:

  1. Open SQL Server Business Intelligence Development Studio application.
  2. Click on File menu> New >
  3. Select the “Integration Services Project” option and specify the location and name of the project.
  4. Open the toolbox and then drag and drop DataFlow to the Design window.
  5. Double-click on the DataFlow task.
  6. Then, drag and drop OLE DB Destination to the Design window.
  7. Join both tasks by dragging a green arrow between them.
  8. Open the OLE DB Source editor window and then click on the New button beside the OLE DB Connection Manager textbox.
  9. Select desired MS Access file from the OLE DB Provider Combobox.
  10. Select the desired table in the OLE DB Source editor
  11. Double-click on the OLE DB Destination editor task and then click on the New button beside OLE DB Connection Manager.
  12. Again click on New
  13. Select “Native OLE DB\SQL Server Native Client 10.0” from Provider Combobox and type the SQL Server name in the server name box.
  14. Click on the New button beside the “Name of the table or View” Combobox.
  15. “Create Table” window will be open, type following lines: CREATE TABLE [<table_name>]([ID] int,[FirstName] nvarchar(255)) and then click OK.
  16. Click Mappings and click on OK.
  17. Importing process is ready to start; you need to press the “start debugging” icon.
  18. Verify the imported data by opening SQL Server Management Studio.
  19. Now type the query of the table to check whether data was successfully imported.
  20. Finally, SSIS Project is create to import MS Access data into SQL Server.

Method 2:- Import Data from MS Access To SQL Server Using Enterprise Manager

  1. Start the SQL Server.
  2. Now, right-click on the database folder/node to import.
  3. Once done, click on All tasks>> Import Data.
  4. Here, the data transfer services wizard screen will appear and click Next.
  5. Here, your chosen Data Source screen will appear.
  6. In the Data Source field, choose Microsoft Access.
  7. Enter C:\taxrates\general.mdb in the filename field.
  8. NOTE: Make sure you have entered the name of the database you are importing: C:\taxrates\states.mdb and C:\taxrates\stateszip4.mdb
  9. After that, click on the Next button.
  10. Now, you will find a selected destination screen.
  11. It appears like Microsoft OLE DB Provider for SQL Server.
  12. Also, try to check that the Server should be the name of your SQL Server.
  13. Here, tap on the Use Windows Authentication if a password is not required. However, if it requires a password, click on Use SQL Server Authentication and enter your username and password.
  14. In the database field, choose New from the drop-down menu.
  15. Once you choose, a create Database dialog box will appear on your screen.
  16. In the Name field:- Enter the name of the Access database imported to the SQL Server. Do not change the default data file or log file sizes.
  17. NOTE: Also, make sure that the spell of the database name is the same in both MS Access and SQL Server. It is necessary to create a new folder.
  18. Click on OK>>Next.
  19. After that, you must see the Specify Table Copy or Query screen.
  20. Select the Copy table(s), view (s) from the source database, and click the Next button.
  21. Here, you will find the selected Source Tables and Views tab. It will look different for SQL Server 2005.
  22. Now, select the top two tables: Accum and Exempt. When you follow these steps later for the states and stateszip4 databases, Click Select All>>Next.
  23. You will find the save, schedule, and replicate package tab here.
  24. In the When box, ensure to Run immediately, choice is checked. Don’t click on anything else on this screen, and click Next.
  25. Again the completing DTS Import/Export Wizard will appear, and click Finish.
  26. Now, you will see the Executing Package screen, which shows the progress of importing data from Access to the SQL Server, as shown above.
  27. Now, you should view a screen with the Successfully copied 2 table(s) message.
  28. Finally, click on OK>>Done.

Also Read: How to fix Access Database Error 2950?

Conclusion

Database evolution is a regular event in business development. The importance, size, and user accessibility (growth) of a particular application can often shift the user or organization to other DBMS. Migrating to more secure and robust platforms makes it more reliable and easy to use. Upsizing the database can sometimes corrupt your files; thus, you can also use the MS Access database recovery to Import Data from MS Access To SQL Server. I hope it helped you out.

Related Post