Migrate an ACCESS database to an SQL Server database

Migrate the ACCESS database to the SQL Server database ACCESS2000 file and use ACCESS2007 to open the ACCESS2007 database in SQLSERVER2005. Method 1: use the database migration tool that comes with ACCESS2007. 1. Open the database migration Wizard of ACCESS2007. 2. Click SQLSERVER to bring up the promotion wizard dialog box.

Migrate the ACCESS database to the SQL Server database ACCESS2000 file and use ACCESS2007 to open the ACCESS2007 database in SQLSERVER2005. Method 1: use the database migration tool that comes with ACCESS2007. 1. Open the database migration Wizard of ACCESS2007. 2. Click SQLSERVER to bring up the promotion wizard dialog box.

Migrate an ACCESS database to an SQL Server database

ACCESS2000 File

Open it with ACCESS2007 and migrate it to SQLSERVER2005

Open ACCESS2007 database tool

Method 1: Use the database migration tool that comes with ACCESS2007

1. Open the database migration wizard for ACCESS2007

2. Click SQLSERVER to bring up the promotion wizard dialog box.

3. Select create database

4. Enter the computer name. My local computer name is joe. Because SQLSERVER is installed locally, you can use a trusted connection.

The default database name is enough.

5. Select All Tables and move them to the right border.

6. Promote table indexes to SQLSERVER.

7. Because there is no application, skip this step.

8. Click "finish" to start database migration to SQLSERVER.

9. Start migration

10. Refresh the database in the object Resource Manager in SQLSERVER2005.

After the database is migrated to SQLSERVER, SQLSERVER automatically adds the SQL letters after the name of the ACCESS database.

11. Open a data table in LygSQL to check whether the migration is successful.

12. Table constraints are also completely migrated

Method 2: Use the data import and export wizard that comes with SQLSERVER2005

Restrictions: The data import/export wizard tool provided by SQLSERVER2005 only supports ACCESS2003 or the following files

1. Create a database Lygl with the same name as the ACCESS Database in SQLSERVER.

2. Select the Lygl database, right-click the database, and choose task> import data.

3. Open the SQLSERVER Import and Export wizard

4. Next Step

Tip: If the ACCESS database has a password, an error occurs when you click Next. Therefore, remove the ACCESS Database Password Before you click Next!

5. click Next.

6. click Next.

7. click Next.

Check the first box. SQLSERVER will automatically check all the tables in the ACCESS database.

8. click Next.

9. Click Finish.

10. Refresh the database in the SQLSERVER object Resource Manager.

11. Open a table

All data is imported.

However, the table constraints are not imported, which is a little worse than the database migration Wizard provided by ACCESS.

Method 3: Use SSMA for ACCESS 5.2

Migrate data to SQLSERVER2012 using SSMA for ACCESS 5.2

Introduction to SQL Server Migration Assistant (1)

Introduction to SQL Server Migration Assistant (2)

Note: to migrate the ACCESS2010 database to SQLSERVER, you must install ACCESS2010 on your computer.

That is to say: to migrate an ACCESS database of any version, you need to install the corresponding version of the ACCESS Database on your computer.

1. Installation

In fact, SSMA supports multiple database types. You can download them at the following link.

Http://files.cnblogs.com/lyhabc/SSMAforMySQL5.2.zip
Http://files.cnblogs.com/lyhabc/SSMAforAccess5.2.zip
Http://files.cnblogs.com/lyhabc/SSMAforOracle5.2.zip
Http://files.cnblogs.com/lyhabc/SSMAforSybase5.2.zip
Http://files.cnblogs.com/lyhabc/access-ssma.rar

The access-ssma.license is the certificate file, installed on double-click SSMA for Access 5.2.exe

You can click "yes" for any rollback operation in the middle. Even if you roll back, the installation will not be affected.

Double-click to open

If you have not applied for a certificate, clickLicense registration page,Open the webpage and enter some mailbox information. This is free of charge.

If you have downloaded the certificate, select the access-ssma.license file you just downloaded, which is the folder where the certificate file is located.

When you start using the software, the wizard will pop up automatically and a new project will be created. If you don't need it, click "close ".

2. Settings

In fact, you can select the default value for these settings.

Global Settings

Default Project Settings

In fact, the above settings are all used by default and have not been changed.

3. Interface

3. Create a project and start data migration

Select SQLSERVER2012 as the database. This tool also supports migration to SQL AZURE.

Click OK

The ACCESS-METADATA Tree node appears in the ACCESS window

Some files are generated under the new folder.

4. Add a database

The following figure is displayed after opening:

Before migrationIntroduction to SQL Server Migration Assistant (1)Create a migration evaluation report

The evaluation report is an html file. It will estimate the approximate time required for Migration. If there is any data that cannot be migrated, an error will be reported.

The output window at the bottom of the software also contains the output information for evaluation conversion.

5. Connect to SQLSERVER2012

It is best not to create the database to be migrated in SQLSERVER2012 in advance, so that ssma for access can be created.

In the Database column, enter the Database you want to create in SQLSERVER. When you click connect, the software will automatically help you create the Database

Click "Yes"

The newly created database Course is displayed in the SQLSERVER window.

At the same time, we can see the new database Course in SSMS.

6. Architecture Conversion

In the ACCESS window, select the Course DATABASE. During the conversion process, an SSMA_TimeStamp column is added to the table, and the primary key is renamed.

7. synchronize with ACCESS

In the SQLSERVER window, right-click the Course database and choose "Synchronize ".

At this time, we can see in SSMS that the table, primary key, and index have been generated, and the data will be poorly exported.

The software generates the SSMA_TimeStamp column in the table, and changes the primary key name to Course_baseinfo $ PrimaryKey.

Extended attributes of Indexes

7. Import Data

In the ACCESS window, right-click the Course database and choose "Migrate Data ".

We can view the data in SSMS.

8. If you are too involved in data conversion, synchronization, and data import, you can click a button on the interface to complete the steps 1, 2, 3, and data import.

This is the migration process.

If anything is wrong, you are welcome to make a brick o