Q

Migrating from MS Access to SQL Server 2000

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

How can I migrate from MS Access 2000 to SQL Server 2000? How can I migrate the forms from Access to SQL Server?
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

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close