Knowledgebase: General QA
How do I upgrade my MS Access 2000 database to SQL Server?
Posted by Barry Tarbet on 26 August 2006 06:17 AM
Upgrading a Access 2000 Database to SQL

Before beginning the upsizing process, make sure that your host is certified with SQL server, or has equivalent experience. SQL Server is a complex and powerful enterprise level database application, and should be managed only by an experienced technician.
To begin, have the Server Administrator create an empty database and grant the user Database Ownership rights. The user should also be given permissions to log in to the SQL server remotely. (This is generally done for you by us when we set up your SQL Server account)

Begin by downloading a copy of the Access 2000 database to the local machine. Be sure to make a backup copy of the database in the web before you begin downloading; if your connection is lost in the middle of the transfer, the database could become corrupt.

Once the database has been downloaded, open it in Access and select Tools|Database Utilities|Upsizing Wizard. From the menu that appears, select Use Existing Database and click Next.

In the next menu, click on the Machine Data Source Tab, select New, System Datasource, and click Next. In the next screen, double-click SQL Server, then click Finish.
Enter the datasource name in the Name field (this can be anything, but I'd suggest keeping it fairly short)This datasource name will be used later when you wish to connect to your SQL Database and make any modifications. Leave the Description blank, and in the Server field enter the IP Address of the SQL Server. Click Next.

Select the radio button for Use SQL Server Authentication, then click Client Configuration. Under client configuration, select Use Named Pipes if you are connecting through a LAN; if you are connecting from a remote machine, through a dial-up or cable modem, select TCP/IP. You don't need to change the entry for the server alias. Click ok.

In the login and password fields, enter your SQL Server login and password and click Next.

On the next screen, check the "Change Default database to:" check box and choose your database from the drop down (this will be the database that the host created for you on the SQL Server). When the access database is upsized, this is the database that the information will be upsized into. Click next, then finish.

Click the Test button to test the connection. If the test is successful, proceed to the next step by clicking ok. If you encounter an error during the test, contact the SQL Server Administrator.

Doubleclick on the data source name you just created, then reenter your SQL Server username and password. Click the >> button to select all tables for upsizing. Click NExt.
Leave all settings on the next screen to the defaults, and click next.
Leave the radio button set to no application changes, and click finish.

Once the Access database has been upsized, there are 3 fields that need to have their data types changed. In Access, these data types are memo fields which are upsized as ntext fields. These fields will need to be changed to nvarchar fields. If you do not have enterprise manager, you will need to create an Access front end database for the SQL Database.

To do this, open Access and click cancel on the first screen so you do not select an existing Access database. Click on the new page icon then double click on the Project (Existing Database) option. Enter a name for the front end database and save it in any location, then click on Create.

The next window that will appear is the connection screen for the SQL Server Database. Enter the IP address of the SQL Server in the server name field. Uncheck the Blank Password check box and enter you SQL username and password in the appropriate fields. Click on the Test Connection button. If the test does not succeed, contact your SQL Server administrator. If the test is successful, click on the drop down box to select your database on the server. Highlight your database and click OK. This Access database is now a front end database for you SQL database and you have the basic functionality of the SQL Server Enterprise Manager to administer your database.
(213 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).