How can I migrate from MS Access 2000 to SQL Server 2000? How can I migrate the forms from Access to SQL Server?

    Requires Free Membership to View

    Login

You can easily migrate Access data into SQL Server using Data Transformation Services. All you need to do is create a database on SQL Server that will be the target for the data. Then follow these steps using Enterprise Manager:
  1. Right-click the target database and select Import Data.
  2. Pick Microsoft Access as the Source in the Choose a Data Source dialog box.
  3. Browse or enter the file name of the source .mdb database.
  4. Pick Microsoft OLE DB Provider for SQL Server in the Choose a Destination dialog box. Select the target database and required authentication mode.
  5. Click Copy Tables in the Specify Table Copy or Query dialog box.
  6. Click Select All in the Select Source Tables dialog box.
  7. Click OK to finish the wizard and start the data migration.
Microsoft also provides the MS Access Upsizing Wizard. To invoke the Upsizing Wizard, go to the Tools menu, point to Database Utilities, and then click Upsizing Wizard. Read these documents to get familiar with all the ins and outs of the Upsizing Wizard: According to the Microsoft Small Business Server 2000 Resource Kit, there are a lot of issues when migrating your Forms from Access to SQL Server. The easiest route is to point your Access Forms at the new SQL Server database and then test them. The SBS Res Kit contains details about the various aspects of migrating forms and queries.

You might to read the white paper Managing SQL Server 2000 after Migrating Multiple Access Databases to a Single Server. It contains useful information tips and advice for administrating and maintaining a single SQL Server where many Access databases have been consolidated.

This was first published in August 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top