Moving a database from ms access to ms sql server

Moving a database from MS Access to MS SQL server

Introduction

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

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.
   

2. 
   Select SQL Server from the list of drivers.
   

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

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

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

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

7. 
   The default database is already selected.
   

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

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

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

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

Selecting a Data Source

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

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

3. 
   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.
   

2. 
   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
   

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

5. 
   Close the Report window
   

Renaming the original Access database

To ensure the correct database is used, rename the original Access database as follows-

The database from which users will now be working will be called .adp.

Using your database

Limitations of Access Project