This topic is intended for AmigoPOS 7 users that wish to migrate an existing MS Jet database to SQL Server Express prior to installing AmigoPOS 8. This step is not required when starting with a new database. In addition, you may upload a compressed backup of your current database using a support ticket and we will migrate it and return the SQL Server database backup to you. This should be done when the business is closed to allow enough time for the migration. The SQL Server database backup can then quickly be restored as shown here.

The MS SQL Server Migration Assistant (SSMA) is a free utility that will automatically convert your exiting AmigoPOS MS Jet database to a MS SQL Server database. Do not use the import utility provided with SQL Server Management Studio. The Migration Assistant is the only utility that can successfully import the existing database relationships and column keys required by AmigoPOS.

Create an uncompressed (not zipped) backup of the current MS Jet database and save it to the desktop for later use.

Download and install the Microsoft SQL Server Migration Assistant for Access here. The most recent version at the time of this writing is v6.0.

Microsoft Access Runtime 2010 32-Bit

The MS Access Runtime provider is required and can be downloaded free from the Microsoft Download Center here (or available as part of Microsoft Office). Install the 32-Bit version, even on 64-Bit computers.

The migration assistant does not have to be installed on the same workstation as the database server. Most often it will be used only once and can be uninstalled. Use the following images as a guide to converting the AmigoPOS MS Jet database to a SQL Server database.

Run Microsoft SQL Server Migration Assistant for Access from Windows Start | All Programs | Microsoft SQL Server Migration Assistant for Access (32-bit).

If using a release prior to version 6.0, the following screen will appear the first time the utility is run. Click the link to submit a brief survey to receive the license file. The license file should be downloaded to the directory shown in the box. Click the Refresh License button and then the Close button after the confirmation message appears.

The welcome screen appears with a summary of the steps required for successful conversion. Click Next.

Change the project name and location if required. This project will appear under File - Recent Projects if you need to access it later. Click Next.

Click Add Databases and select the AmigoPOS MS Jet database (the database backup should be located on the desktop as described earlier). If you did not create a database backup and are unsure of the location, open the Database Configuration Utility and use the file shown in the Path box. Click Next.

Wait 30 seconds for the database to load and expand the tree and check the box next to the database. Only Tables should be checked, as the queries are not used by SQL Server Express. Click Next.

Connect to the SQL Server using the parameters configured in the previous topic. Enter a name for the database that has not been used before. Do not select an existing database from the list. Un-check the box next to Encrypt Connection and click Next.

The database does not exist and you will be prompted to create it. Click Yes at the prompt. If this screen does not appear, the database already exists. Start over and enter a unique database name.

Ensure that the Link Tables box is not checked. Click Next.

If the following error appears in the output window at the bottom of the migration screen, the prerequisite MS Access Runtime is not installed and the migration cannot continue. This error will also occur if the 32-bit MS Access Runtime is installed and the 64-bit Migration assistant is used:

Access Object Collector error: Database

     Could not load file or assembly 'Microsoft.Office.Interop.Access.Dao, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.

     Error occurred while loading children.

The migration utility will load all of the table names from the MS Jet database.

Click OK to confirm. The migration will take a few minutes to complete. Do not interrupt the process.

If any errors exist, click Report to send this information to technical support (warnings are normal and are not cause for concern). Click Close to exit the Migration Wizard.

The output and error list windows also contain any error messages. Click Copy to paste the messages into a support ticket. Close the MS SQL Server Migration Assistant. Open Back Office | Settings | Database Settings and enter the connection settings for the new database as described here.