How to: Upgrade from MS Access to SQL Server

Upgrading/upsizing a MS Access database to SQL Server

The following steps will take you through the process of upgrading a local installation of UtiliTrak to use a SQL Server database. These steps are the same even if you are performing a fresh installation.

NOTE: Administration of a SQL Server database server is highly technical. Prior to attempting the steps outlined below, your IT personnel should be notified and on-site. MTS can not provide support for the installation and administration of a SQL Server. However, we are more than happy to assist you with the following MS Access upgrade process.

1. Create a SQL database named "utilitrak" and a user with full priviledges to that database. The username/password can be something simple like "utilitrak" for both. As this user will only have access to the UtiliTrak database you won't need to worry about security issues should you decide to add additional databases to the server.
2. Create an ODBC DSN connection. Use the settings from #1. Remember the name of this connection as it'll be used when configuring the Palm Hotsync conduit mapping.
3. By default, the MS Access database is located at C:\Program Files\UtiliTrak\UtiliTrak.mdb. Read and follow MS KB article #237980. This process will guide you through upsizing a MS Access database to the SQL Server. The database created in step #1 and the ODBC DSN you created in step #2 can be used to connect to the SQL database.
4. After the SQL Server database has been created and the data has been migrated, open UtiliTrak and change the database location:
1. Click on Preferences -> Tools -> Database.
2. Change the server type to "SQL Server".
3. Change the location to the server name running SQL Server (this will be the same name as defined in step #2 when creating the ODBC DSN connection). Enter the username and password for the server. Test the settings. Once the settings are verified to be OK then click on OK.
4. The data you see in UtiliTrak now should be the data contained on the server.
5. Clear the HotSync mapping. Click the Hotsync icon in the system tray. Choose "Custom...", click on "HanDBase 3.0" and then click "Change...". Click on the Sync Exchange button and remove all the UtiliTrak databases. Click OK, OK, Done.
6. Switch back to UtiliTrak and click Preferences -> Tools -> Synchronization.
7. IMPORTANT. Ensure that the version of HanDBase installed is the Enterprise edition!
8. Select the ODBC DSN connection and the handheld username. Click on "Auto-Map User..."

You can test the settings by modifying data, first on the PC and verifying the change on the handheld after a Hotsync, then by changing data on the handheld and verifying the change on the PC after a Hotsync.