Moving a database from MS Access to MS SQL server

Introduction

Moving a database from MS Access to MS SQL Server is achieved using the Upsizing Wizard in Access: this assumes that you already have an empty database set up on the MS SQL Server. Your Access database will be upsized into this existing database.
NB: Before you upsize ensure that the tables within your existing database start with an alphabetic character, e.g. data_2003 rather than 2003. You cannot successfully upsize a database containing tables which are named using numeric characters only.
If your database is currently held within your web you will need to export it to your local drive before upsizing. Do this as follows:
  1. Open up the web containing your database
  2. Click on [View] and then on [Folder List]
  3. Navigate to and select the database (normally within the fpdb directory)
  4. Click on [File] then [Export...]. By default your My Documents folder will open but you can navigate to a different location if you wish. You can also choose to rename the database at this point.
  5. Once you are happy click on [Save] and your database will be exported to the chosen location.

Selecting the database

Instructions for upsizing are as follows:-
  1. In Access open the database you wish to upsize.
  2. Select Tools - Database Utilities - Upsizing Wizard.
  3. Select Use existing database. Click on [Next].

If you have previously created a local (ie on your personal machine) data source connection to your SQL server database, go straight to Selecting a Data Source.

Creating a Data Source

  1. Click on [New] to create new data source.

  1. Select SQL Server from the list of drivers.

  1. Enter a name for the connection. Click on [Next].

  1. Check details are correct and click on [Finish].

  1. Enter a description and the server to connect to and click on [Next].

  1. Check that Windows NT authentication is selected and click on[Next].

  1. The default database is already selected.

  1. If this is not the correct database select Change the default database to and choose the correct database from the drop-down list.

  1. Click on [Next] and then on [Finish].
  2. A setup screen will appear confirming the settings chosen.

  1. Click on [Test Data Source...] to check connection.
  2. A screen should appear confirming that the tests have been successful

  1. Click on [OK] and [OK] again.

Selecting a Data Source

  1. Select the DSN from the list and click on [OK].

  1. Select the tables to be exported and click on [Next].

  1. Amend the following screen to export table attributes required and click on [Next].

Creating an Access Data Project (ADP)

  1. On the next screen select Create a new Access client/server application.

  1. A new Access Data Project (adp) file is now ready to be created.By default, Access will create the project file in the My Documents Folder on your local computer: if you wish to save the file elsewhere click on the [Browse] button and navigate to the appropriate directory. We suggest that you keep this file on a network drive to ensure that it is regularly backed up. If the appropriate directory is selected the next window allows the user to either open the new ADP file or keep the MDB file open

  1. Click on [Finish].
  2. After a short time the Upsizing Wizard Report will be displayed. This can be printed out.

  1. Close the Report window
NB Each table in your SQL Server database must have a primary key defined if you wish to add or edit data via your Access Project. This can be done in your Access database before it is upsized, or in the SQL Server database using the Web Data Administrator on the same database.

Renaming the original Access database

Once the Access database has been moved to the SQL Server and an Access Project created, users must ensure that it is the Access Project version and not the original Access database which they maintain and update.
To ensure the correct database is used, rename the original Access database as follows-
<dbname>.mdb rename to<dbname>.orig
The database from which users will now be working will be called <dbname>.adp.

Using your database

The Access Project database can be used and maintained using the same features for inputting and updating data as an Access database.

Limitations of Access Project

One of the main limitations of Access Project is its inability to control the user permissions on the database. For this you should use Web Data Administrator on the same database.
TM Trademark owned by Microsoft Corporation.
© Screen shot(s) reprinted by permission from Microsoft Corporation.