Upgrade/Upload a Microsoft Access Database to SQL Database Using DTS
Written by Naveed Ashraf Thursday, 13 January 2011
The following article will give you steps to convert your existing Microsoft Access database to a SQL Server database using components of Microsoft SQL Server Management (available at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en). You will need to have a copy installed on your local computer.
Step 1. Open the Import and Export Data link (that can be opened from the Windows menu), to start the wizard to export your Microsoft Access data to an online SQL database. Click Next. This will open a dialogue asking about your source Microsoft Access database details like the location, ID/password. This dialogue box is shown below. After providing the information, click Next.
Step 2. Now, you need to provide your target SQL server database details. Select the provider (data source) as per installed driver on your local computer. The server name is your online server (like penther.arvixe.com). Select SQL Server Authentication and provide the proper user name and password. After that, you may write the target SQL database name in the Database drop down menu or click Refresh to update this drop down menu with the available SQL databases (then select the proper database). The final screen of this step is shown below. Now click Next.
Step 3. After step 2, you will be asked to either use this wizard to copy the source database to the target directly, or you may write your own SQL query (in this case, the result of the query will be copied to the target database). Click Next
Step 4. A new dialogue box will be appear, showing your tables in the Microsoft Access database and the target table names in SQL database. You may change the target names here by double clicking on the target name. Clicking next will ask you to run this task immediately. If you have installed Microsoft SQL Server Management Studio Express/Web/Workgroup you cannot schedule this wizard to run later. To run this task immediately, click Next. This will show you a summary of the provided information. Click Finish.
Step 5. Completing Step 4 will start the process for copying your local Microsoft Access database to the SQL server database. You can view each step of this process in the same dialogue box. Once this process is completed, this dialogue box will inform you that it was successful (or if there were any problems during the process). The detailed report for this process can be viewed or saved after completion.
This process can also be used for other compatible sources like FoxPro database or Microsoft Excel.