Using Microsoft SQL Server

Prerequisites

  1. Microsoft SQL Server must be installed on a server that is accessible to all client computers. Visit Microsoft to learn more about SQL Server.
  2. All client computers will need to have the ODBC Driver for SQL Server installed so that they can connect to SQL Server.

Configuration

  • The Database Administrator will need to create a new empty database. It may best to prefix the database name with “Emaster” to make it easier to find in the future.
  • The first user to connect to the new database will need Create Table permissions. Additionally the first user to connect to the database after an Emission Master DB version upgrade will also need Create Table permissions.

Connection

There are two ways to connect to an SQL Server Database:

  1. A 32 bit ODBC DSN can be created on each client computer and selected from the Emission Master UI. This may be best if special settings are needed to connect to your SQL Server and you have way to automate DSN deployment.
  2. A simpler option would be to use an ODBC Connect String that can be saved in either the program INI file (setting applies to all users) or the client computer registry (setting applies to only that client). In this case a DSN is not required. The Connect String is in the form of:

    Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;

    myServerAddress = The name of the SQL Server.
    myDataBase = The name of the Emission Master database.
    Additional or alternate settings may be required, refer to your Database Administrator for further information.

Migrating an Existing MDB Database into SQL Server

Emission Master (version 8.4.3.12 and above) contains a tool that can assist in migrating all the data from a currently connected MDB file into a new empty SQL Server database. A backup copy of the MDB file should be made before running the migration.

  1. Have a DB Admin create a new empty database in SQL Server.
  2. In Notepad create a connect string for the new database using the template above.  Make sure to use the correct computer name and database name. This connect string will first be used by the migration tool and can then be used to connect to the new database.
  3. Run Emission Master and make sure it is connected to the current MDB you want to migrate into SQL Server.
  4. In the Emission Master Options menu activate the Migrate Database menu command.
  5. Verify that the Source Database looks correct.
  6. Press the [Browse] button to the right of the Destination Database field.
  7. Select the +Enter Connect String item and press [OK].
  8. Paste the Connect String for the new SQL Database that you created in step 2 and accept the dialog.
  9. Press the [Migrate] button.  The table and database status will be shown as the data is migrated.
  10. Use the Options \ General Options menu command and the [Select Database] button to change to the new database.  Select the +Enter Connect String item and press [OK]. You can use the same Connect String as before.
  11. The program will now be using the new SQL Server Database.

Comments are closed.