Direct Database Integration with
Instructions for software versions:
Windows 2008 R2 (64-bit)
MS SQL Server 2014
MS Access 2010 (64-bit)
PageGate version 8.0 should be installed and running 'normally' before taking these steps. Make sure PageGate is setup to run as a service.
1. Stop PageGate
a. Be sure to stop ALL of the PageGate programs from running (i.e. PG Server, PG Admin, PG Monitor, PG GUI Clients, etc.)
a. 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.
3. Create a user for the PageGate services and give it rights to the SQL server
a. Create a new user named something like pguser. The new user must have local administrator rights
b. Run the Microsoft SQL Server Management Studio
c. Expand the 'Security' object
d. Right-click on 'Logins' and choose 'New Login'
e. Select the 'General' object on the left
f. Add the new pguser account to the 'Login name' field (use the Search button to get the full user name)
g. Select the 'Server Roles' object on the left
h. Check the 'ssadmin' box
i. Hit 'OK'
j. Close out of the Microsoft SQL Server Management Studio
4. Update the PageGate services to use the new user account
a. Start the Windows Service Manager (Start->Administrative Tools->Services)
b. For each PageGate service that is 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 windows saying that the user has been granted the Log On As A Service right
a. Close out of the Service Manager
5. Log in with the newly created user account
a. Log out as the current user
b. Log back in using the new pguser account
6. Import PageGate Database Structure and Data into SQL server
a. Run the SQL Server 2014 Management Studio
b. Enter necessary information to connect to your SQL server
c. Go to 'Tools'->'Options'->'Designers' and make sure the 'Prevent saving changes that require table re-creation' checkbox is not checked
d. Right-click on 'Databases' and choose 'New Database'
e. Name the database 'pgsqldatabase' or something similar and hit 'OK'
f. Expand the 'Databases' object to show the newly created database
g. Right-click on the new database object and go to 'Tasks'->'Import Data', this will start the Import Wizard
h. Hit 'Next' to get past the wizard's welcome screen
i. For 'Data source' choose 'Microsoft Access (Microsoft Jet Database Engine)', and hit 'Next'
j. Use the 'Browse' button to find the PageGate database file (by default: c:\pagegatedata\database\pagegate.mdb)
k. Unless you have setup a password on the PageGate database file (not typical), leave the 'User name' and 'Password' fields blank
l. Hit 'Next'
m. For 'Destination' choose 'Microsoft OLE DB Provider for SQL Server'
n. Leave all the default values, unless you need to use SQL Server Authentication, in which case a User name and Password must be supplied
o. Hit 'Next'
p. Choose the 'Copy data from one or more tables or views' option and hit 'Next'
q. Select all the tables as source tables and hit 'Next'
r. Leave 'Run immediately' checked and hit 'Next'
s. Hit 'Finish'
t. After all the tables are finished being imported successfully, hit 'Close'
7. Modify Database Structure - some changes to the new SQL database need to be made after the import
a. While still in the SQL Server 2014 Management Studio expand the'Tables' object under the new PageGate database object
b. Right-click on the dbo.lookup table and choose 'Design'
c. Select the 'id' field
d. At the bottom of the screen under 'Column Properties', expand the 'Identity Specification' object
e. Change 'Is Identity' to 'yes'
f. Right click on the 'id' field and choose 'Set Primary Key'
g. Close the table's design view and hit 'Yes' when prompted to save changes
h. Right-click on the dbo.messages table and choose 'Design'
i. Select the 'message_id' field
j. At the bottom of the screen under 'Column Properties', expand the 'Identity Specification' object
k. Change 'Is Identity' to 'yes'
l. Right click on the 'message_id' field and choose 'Set Primary Key'
m. Close the table's design view and hit 'Yes' when prompted to save changes
n. Follow these steps for all of the remaining tables
1. Right-click on the next table and choose 'Design'
2. Select the blank field at the end of the list of column names
3. Enter 'record_id' for the Column Name
4. Enter 'int' for the Data Type
5. At the bottom of the screen under 'Column Properties', expand the 'Identity Specification' object
6. Change 'Is Identity' to 'yes'
7. Right click on the 'record_id' field and choose 'Set Primary Key'
8. Close the table's design view and hit 'Yes' when prompted to save changes
o. Close out of the SQL Server 2014 Management Studio
8. Create a DSN (data source name) for the new pagegate sql database
a. Run c:\windows\syswow64\odbcad32.exe (the 32-bit ODBC Data Source Administrator)
b. Select the 'User DSN' tab (the 64-bit version of MS Acces can only see 64-bit System DSNs, but can see both 32-bit and 64-bit User DSNs - since PageGate can only use 32-bit odbc drivers, User DSNs are the only option)
c. Hit 'Add'
d. Select the 'SQL Server' driver from the list and hit 'Finish'
e. Use 'pgdatasource' for both the 'Name' and 'Description' fields
f. Enter the SQL server in the 'Server' field and hit 'Next'
g. Make any necessary changes to the authentication settings and hit 'Next'
h. Check the 'Change the default database to:' box and select the PageGate database from the list (pgsqldatabase in this example)
i. Hit 'Next'
j. Leave all the defaults on this next screen and hit 'Finish'
k. Test the data source if you want, and then hit 'OK' when done.
l. You will see your new User DSN listed. Hit 'OK' to close out of the ODBC Data Source Administrator
9. Create linked tables in MS Access
a. Run MS Access 2010 and open the PageGate database (default: c:\pagegatedata\database\pagegate.mdb)
b. Choose 'External Data'->'ODBC Database' from the toolbar
c. Choose 'Link to the data source by creating a linked table' and hit 'OK'
d. Select the 'Machine Data Source' tab, choose pgdatasource from the list and hit 'OK'
e. Select all the dbo.* tables (there should be 15 of them)
f. Check the 'Save password' box
g. Hit 'OK'
h. Each time you are prompted about the password, hit the 'Save Password' button
i. Rename all the existing pagegate tables (the ones without a dbo_ prefix). For example ahm can be renamed to original_ahm.
j. Remove the dbo_ prefix from all the sql tables (by renaming them) so their names match the original pagegate table names
k. Select the 'Linked Table Manager' from the toolbar
l. Hit the 'Select All'
m. Hit 'OK'
n. After MS Access is done relinking the tables, you can quit out of the program
10. Try it out
a. Start PG Monitor
b. Start PG Admin
c. Start the PageGate server
for Writing Directly to the Database