This article describes how to move your Access database data to SQL server.
The instructions here are for SQL Management Studio 2008 Express. Other versions of SQL Management Studio are similar.
- Open SQL Management Studio and connect to the MS SQL server that you want to import the Access database to. You can find the IP address of your SQL server in HostPilot > MS SQL Server > Databases.
- Right-click the database you are going to import into, and select Tasks > Import Data...
The Import Wizard opens.
- In the Import Wizard, click the Data Source box and select Microsoft Access from the drop-down list. Type the path to the Access database you are importing, or browse to it.
- Change the Authentication type from Windows Authentication to SQL Authentication and enter the login information that you used to connect to the SQL server. Make sure that the database that is selected is the one you want to import into.
- You can leave the Copy option at Copy data from one or more tables or views.
- Select which tables or views you want to import from the Access database, and ensure that they are going to the tables that you want.
- Select when you want to run this import (in SQL Management Express, you can only run it immediately).
- Review the information on the Complete Wizard page and make sure that these are the settings that you want to run. Click Finish to run the commands.
The wizard shows you whether each action was completed successfully or not.
- After the import process is finished, you may want to check that the information was transferred correctly, and there aren’t any errors in the data contained in the database.