Direct Database Integration

1Stop PageGate

• Be sure to stop ALL of the PageGate programs from running (i.e. PG Server, PG Admin, PG Monitor, PG GUI Clients, etc.)

2Backup

• If you have existing data in your PageGate program, be sure to make a backup of your PageGate database. The database file is named pagegate.mdb and is found in the c:\PageGateData\Database\ folder by default.

3Create a user for the PageGate services and give it rights to the SQL server

1. Create a new user named something like pguser. The new user must have local administrator rights.
2. Run the Microsoft SQL Server Management Studio.
3. Expand the 'Security' object.
4. Right-click on 'Logins' and choose 'New Login'.
5. Select the 'General' object on the left.
6. Add the new pguser account to the 'Login name' field (use the Search button to get the full user name).
7. Select the 'Server Roles' object on the left.
8. Check the 'ssadmin' box.
9. Hit 'OK'.
10. Close out of the Microsoft SQL Server Management Studio.

4Update the PageGate services to use the new user account

1. Start the Windows Service Manager (Start → Administrative Tools → Services).
2. For each PageGate service listed (they all start with the letter 'p'), do the following:
   1. Right-click on the service name and choose 'Properties' from the menu.
   2. Select the 'Log On' tab.
   3. Select the 'This account' option.
   4. Enter (or use the 'Browse' button) the pguser account and password.
   5. Hit 'OK' — the first time you do this you should get a popup saying the user has been granted the Log On As A Service right.
3. Close out of the Service Manager.

5Log in with the newly created user account

1. Log out as the current user.
2. Log back in using the new pguser account.

6Import PageGate Database Structure and Data into SQL server

1. Run the SQL Server 2014 Management Studio.
2. Enter necessary information to connect to your SQL server.
3. Go to 'Tools' → 'Options' → 'Designers' and make sure 'Prevent saving changes that require table re-creation' is not checked.
4. Right-click on 'Databases' and choose 'New Database'.
5. Name the database pgsqldatabase or something similar and hit 'OK'.
6. Expand the 'Databases' object to show the newly created database.
7. Right-click on the new database object and go to 'Tasks' → 'Import Data', this will start the Import Wizard.
8. Hit 'Next' to get past the wizard's welcome screen.
9. For 'Data source' choose 'Microsoft Access (Microsoft Jet Database Engine)', and hit 'Next'.
10. Use the 'Browse' button to find the PageGate database file (by default: c:\pagegatedata\database\pagegate.mdb).
11. Unless you have setup a password on the PageGate database file (not typical), leave the 'User name' and 'Password' fields blank.
12. Hit 'Next'. For 'Destination' choose 'Microsoft OLE DB Provider for SQL Server'.
13. Leave all the default values, unless you need to use SQL Server Authentication.
14. Hit 'Next'. Choose 'Copy data from one or more tables or views' and hit 'Next'.
15. Select all the tables as source tables and hit 'Next'.
16. Leave 'Run immediately' checked and hit 'Next', then hit 'Finish'.
17. After all the tables are finished being imported successfully, hit 'Close'.

7Modify Database Structure

Some changes to the new SQL database need to be made after the import.

1. While still in SQL Server 2014 Management Studio expand the 'Tables' object under the new PageGate database object.
2. Right-click on the dbo.lookup table and choose 'Design'. Select the 'id' field. Under 'Column Properties', expand 'Identity Specification', change 'Is Identity' to 'yes'. Right-click on 'id' and choose 'Set Primary Key'. Close and save.
3. Right-click on the dbo.messages table and choose 'Design'. Select the 'message_id' field. Change 'Is Identity' to 'yes'. Set as Primary Key. Close and save.
4. For all remaining tables: right-click, choose 'Design', add a new column named record_id with data type int, set 'Is Identity' to 'yes', set as Primary Key, close and save.
5. Close out of the SQL Server 2014 Management Studio.

8Create a DSN (data source name) for the new PageGate SQL database

1. Run c:\windows\syswow64\odbcad32.exe (the 32-bit ODBC Data Source Administrator).
2. Select the 'User DSN' tab.
3. Hit 'Add'. Select the 'SQL Server' driver from the list and hit 'Finish'.
4. Use pgdatasource for both the 'Name' and 'Description' fields.
5. Enter the SQL server in the 'Server' field and hit 'Next'.
6. Make any necessary changes to the authentication settings and hit 'Next'.
7. Check 'Change the default database to:' and select the PageGate database (pgsqldatabase) from the list.
8. Hit 'Next', leave defaults on the next screen, and hit 'Finish'. Test the data source if desired, then hit 'OK'.

9Create linked tables in MS Access

1. Run MS Access 2010 and open the PageGate database (default: c:\pagegatedata\database\pagegate.mdb).
2. Choose 'External Data' → 'ODBC Database' from the toolbar.
3. Choose 'Link to the data source by creating a linked table' and hit 'OK'.
4. Select the 'Machine Data Source' tab, choose pgdatasource from the list and hit 'OK'.
5. Select all the dbo.* tables (there should be 15 of them). Check 'Save password' and hit 'OK'. Hit 'Save Password' each time prompted.
6. Rename all the existing pagegate tables (the ones without a dbo_ prefix), e.g. ahm can be renamed to original_ahm.
7. Remove the dbo_ prefix from all the SQL tables so their names match the original PageGate table names.
8. Select 'Linked Table Manager' from the toolbar. Hit 'Select All', then 'OK'. After relinking completes, quit MS Access.

10Try it out

1. Start PG Monitor.
2. Start PG Admin.
3. Start the PageGate server.