How do I upgrade from a MS Access database to SQL database?

How do I upgrade from a MS Access database to SQL database?

To view a printable PDF version of these instructions, please download the document at the end of the article. 

Many of our customers run the process themselves with great success. We are available to run the process for you or we can be available on standby from 9:00-5:00(Eastern) to provide assistance if needed. The whole process takes about 3 hours to setup, then another hour to run the conversion and connect users. The SQL server setup can be completed in advance while users continue to work in ProTracker Advantage and the final conversion can be run at a later time. 

***Before you begin, contact ProTracker for a new SQL License key***

DATABASE BACKUP

Make a backup copy of your AdvantageData.mdb. (Copy and paste works fine) If you are not sure where your database resides, open ProTracker and Select Help> About ProTracker Advantage from the menu.

SERVER SETUP

Use any version of SQL Server or use the steps in this article to install and configure 2012 SQL Server Express: http://help.protracker.com/entries/36731845

1. Install SQL Server 2012 R2 Express or create a new instance of SQL Server. 

2. Install SQL Server Management Studio

3. Download and attach a blank ProTracker advantage SQL database from here: http://www.protracker.com/Download/Files/SetupProTrackerAdvantageServer_DB.exe

4. Create a nightly backup file. See this article: http://help.protracker.com/entries/73323936-SQL-Server-Backup-and-Maintenance

5. Schedule a task to make a .bak file every night (overwrites previous .bak file)

6. Enable TCP/IP and Named Pipes, enable SQL browser service

 

WORKSTATION PREP

Close ProTracker on all workstations.

RUN THE CONVERSION PROCESS FROM ANY WORKSTATION

***Before you begin, contact ProTracker for a new SQL License key***

  1. Go to a workstation and open ProTracker Advantage. Make sure everyone else is out of ProTracker Advantage. Go to Operations> Users to find out who is connected.
  2. Select Tools> Convert 4.1 Database to SQL from the menu
  3. Browse to your current Access database (AdvantageData.mdb)
  4. Select the destination SQL Server on the server. Typical connection values are:
    Authentication: SQL Server
    Database: SERVERNAME\SQLEXPRESS
    Username: ******
    Password: *******     Contact ProTracker support for the default  username and password.
  5. Wait for the conversion process to complete.
  6. When prompted, connect to the SQL database
  7. Enter new SQL registration key which we will provide to you at no cost for existing customers. Please contact customer support for a new key.

ON EACH ADDITIONAL WORKSTATION

The next time users open ProTracker Advantage, they will be prompted with a message that the database has been converted to SQL, connect now? If they select Yes, the connection is made to the new SQL database. If they select No, they will connect to the Access version. If they connect to the Access version, each time they open it, they will be reminded that the database has been converted and given a chance to connect to the SQL.

When they connect to ProTracker, they will be prompted to connect to the new SQL database. After they click YES on the prompt, this window should appear.

sql_login.png
Enter the Server Name. Database name is usually as shown. If the user cannot connect, try switching to SQL Server authentication.

AFTER THE CONVERSION

We recommend that once you make the switch to a SQL database and confirm that the data is complete, you move and/or rename the Access (.mdb) version of the database so users cannot accidently connect to it.

As long as your Access version license key is valid, you can still open the Access version but data entered into the Access copy will not show up in the SQL copy and vice versa. To get to the Access version after a SQL connection, go to Tools> Options> Connect to Access database in the Assistant area. Browse to locate the database. To get to a SQL version, go to Connect to SQL Server database in the same window.

**Please confirm that the backup script is working correctly and that the SQL database is getting backed up each night.**

DOCUMENTS AND PHOTOS

If this upgrade involves a new server, you may want to move the Documents folder to the new server. Try to keep the path to the documents exactly the same. Go to Operations> Database Admin to view the current path. If you remap the mapped drive to the new location, the files paths will allow the links in ProTracker to work.

Have more questions? Submit a request

0 Comments

Article is closed for comments.