55. How can I migrate the local Audit database from MS Access to a networked SQL Server Database

On the SQL Server:

1. Ask the SQL Server DBA to create a database called ezescan_audit for you.

2. The ezescan_audit database will contain 4 tables (Completed_Work, Current_Work, Deleted_Work and Routed_Work).

3. There is a database table creation script for SQL Server that can be used by the DBA to create the 4 tables. Look under Downloads → Application Patch Updates. Download and run the applicable script.

4. Create an ‘ezescan’ user, and set the password for now to ‘ezescan’ (Note: You can change the password later once it's all working. Don't forget to do this!)

5. Grant the EzeScan user all rights to the 4 tables (Completed_Work, Current_Work, Deleted_Work and Routed_Work) in the EzeScan Audit database.

6. Using a SQL test program, make sure that the ‘ezescan’ login/password can successfully connect to the ezescan_audit database, and perform a select * query on each of the 4 tables (Completed_Work, Current_Work, Deleted_Work and Routed_Work).

On each EzeScan PC that is going to use the remote Audit Database:

1. Make sure the SQL client for SQL Server is installed on the each PC.

2. Configure a SQL client connection called ezescan_audit and connect it to the remote ezescan_audit database running on the SQL Server. Using a SQL test program, make sure that the ‘ezescan’ login/password can successfully connect to the ezescan_audit database, and perform a select * query on each of the 4 tables (Completed_Work, Current_Work, Deleted_Work and Routed_Work).

3. Configure an ODBC connection called ezescan_audit. This will use the underlying SQL client connection configured in step 7. Using an ODBC test program, make sure that the ‘ezescan’ login/password can successfully connect to the ezescan_audit database, and perform a select * query on each of the 4 tables (Completed_Work, Current_Work, Deleted_Work and Routed_Work).

4. By default EzeScan uses a local ezescan_audit database located in a MS Access database file called Ezescan_Audit.mdb. (Please note that Auditing is only enabled if the workstation is running with a PRO evaluation license, or a Production license with the AUDIT module enabled.)

5. Use the Ezescan Admin → Options form, Audit tab settings to change the Audit database from using an MSAccess database file to using an ODBC connection to a remote database server. Tick the 'Use ODBC' option, and enter the following details for the ODBC connection:

   • ODBC DSN = ezescan_audit , ODBC User = ezescan ,ODBC password = the password you configured

   • Save and close the Admin-Options form.

   • Please note that Use ODBC option should only be used with a remote SQL Server. Do not use this option with a MS Access database. We do NOT currently support Oracle ServerFor each job/route that you want auditing enabled for, you'll need to enable the 'Update Audit Database' option. This can option can be found on the Job Admin form Profiling tab, or on the Route Admin form Logging tab.

6. For each job/route that you want auditing enabled for, you'll need to enable the 'Update Audit Database' option. This can option can be found on the Job Admin form Profiling tab, or on the Route Admin form Logging tab.

7. Run one of the jobs/routes that has had 'Update Audit Database' enabled.

8. Then use the Admin → Audit Reporting from to run the completed Work report for that job/route. A web browser based activity report should appear on the screen. The report should contain at least 1 row of details for every document processed by that job/route.