Edit

Migrating Access Data into SQL Server - Azure SQL Database (AccessToSQL)

  • Article

After you have successfully created the database objects into SQL Server, you can migrate data from Access to SQL Server or SQL Azure.

Setting Migration Options

Before you migrate data into SQL Server or SQL Azure, review the project migration options in the Project Settings dialog box. In this dialog box, you can set the migration batch size, table locking, constraint checking, insertion trigger firing, identity and null value handling, and how to handle dates that are out of the SQL Server range. For more information, see Project Settings (Migration).

Migrating Data

Migrating data is a bulk-load operation that moves rows of data into SQL Server or SQL Azure in transactions. The number of rows to be loaded into SQL Server or SQL Azure in each transaction is configured in the project settings.

To view migration messages, make sure the Output pane is visible. If it is not, on the View menu, select Output.

To migrate data

  1. Make sure you have loaded the Access database objects into SQL Server or SQL Azure.

  2. In Access Metadata Explorer, select the objects that contain the data that you want to migrate:

    • To migrate data for an entire database, select the check box next to the database name.

    • To migrate data from individual tables, expand the database, expand Tables, and then select the check box next to the table. To omit data from individual tables, clear the check box.

  3. Right-click Databases and then select Migrate Data.

You also can migrate data outside of SSMA by using the SQL Server bcp command-line utility or Integration Services. For more information about these tools, see SQL Server Books Online.

Next Step

If you have Access database applications that you want to continue to use after migration, link the Access database tables to the SQL Server or SQL Azure tables. For more information, see Linking Access Applications to SQL Server.

See Also

Migrating Access Databases to SQL Server
Setting Conversion and Migration Options