How to Migrate from Access to SQL Server
Access databases for projects are usually considered to be easier to use than SQL Server databases. Access databases are stored in individual files that are readily accessible. These database files can be easily copied to send to clients or to create a back-up, they do not require database user permissions, and they can usually be edited without further software because the Microsoft Access program is often already installed.
Despite being less accessible, SQL Server databases offer: better performance, particularly for multiple concurrent users, database security via SLQ Server user permissions, and inherent network sharing.
You may also be in a position where you want to out-source the initial project creation to a subcontractor but you want the final project stored in a SQL Server instance that the subcontractor cannot connect to. For this case it is useful if the subcontractor can create an Access project that you will then migrate to SQL Server when it is delivered.
If you want to migrate from Access to SQL Server for performance or other reasons, you can use this procedure inside Instrument Manager.
NOTE: This procedure does not merge with existing contents in a SQL Server database; it copies the data from the current project into a new empty SQL Server database file.
Procedure
Ensure no other users have this project open and ensure they will not open it during the migration procedure.
From the menu, select .
You will be asked to connect to a SQL Server and enter a database name.
Click the button to load a list of available servers (this may take considerable time). Alternatively enter the server name in the box.
An entry for SQL Server Express will be forcibly added to the list regardless of whether you have SQL Server Express installed. This is done because SQL Server Express is not found by the search function. If you do not have SQL Express installed then connecting to that server entry will fail.
Click the button and the databases on the chosen server will be displayed in the list.
Instrument Manager first tries to connect to SQL Servers using Windows Authentication (also known as "Integrated Security"), using the windows credentials of the currently logged in user.
If Instrument Manager cannot do this automatically (ie, the selected server does not allow Windows Authentication, or your Windows account doesn't have permission to access the server), you will be prompted to login using standard SQL Server Authentication (username and password).
Click the button then enter the name for the database.
Alternatively, if your IT department has created an empty database with the correct permissions, then you should choose that database from the list.
Click .
If you have chosen to create a new database, you will be prompted to enter administrator credentials for the database server.
Instrument Manager will then ask if the current user should be granted access to the new database. Click if the database administrator will manually configure user access to the database, otherwise, click .
The contents of the original Access database will then be copied into the new SQL Server database. Wait for the process to complete.
The original project using the Access database will be renamed along with its .DSN file by inserting "pre-migration" at the end of the names.
e.g. "Project1.prj" and "Project1.dsn" would be renamed as "Project1.pre-migration.prj" and "Project1.pre-migration.dsn".
After this migration procedure, the original project name will refer to the new SQL Server database.
The procedure finishes with a message confirming the migration and the name of the pre-migration backup project.
When you click on the confirmation message the original Access project will be closed.
Re-open the project using the original project name, which will now load from the SQL Server database.
This will be the case for all users. The original project name now refers to the SQL Server project.
If you need to open and check something in the original Access project, this is still available in the backup that was created with the name ending in "pre-migration", e.g. the project "Project1.prj" was backed up as "Project1.pre-migration.prj".