GroupWise Accounting Data Report 2.0


Purpose: I created this database to enable our organization to "audit" email traffic. After deploying this database, I received requests for improvements. This version contains those improvements. The database enables us generate a report on individual or all users. You can also select a date range for review.

Changes for 3/21/2006 - Version 2.0

  1. I changed the way the program stores and displays the "Period" totals of Inbound and Outbound messages on the Main Menu. These totals are calculated/recalculated during the import/export process. This results in a faster loading of the Main Menu and resolved an error message.

  • I changed the way the program calculates the "Actual Date/Time" of the message in relationship to the "Daylight Savings Time" entry you select/enter. The process will automatically adjust the DST time based on your entry.

Changes for 5/30/2003 - Version 1.81

  1. I added a "Report" (list) on the main menu that displays by period, the total "Inbound and Outbound" messages in the database.

  • I added two fields on the main menu that displays the "First and Last Date" data was imported into the database.

  • I added Hotlinks to documents that assist in correcting errors and setting up the "GroupWise Accounting File" process. (NOTE: This process causes a cosmetic error. Just press "OK". I am working on correcting this.)

  • I changed the "Exported data File Naming convention" from "M-YYYY" to "YYYY-M"

Changes for 1/15/2003 Version 1.79:
I corrected the "GroupWise - User Statistics" report that was reporting an Error Code.

Changes for 1/8/2003 Version 1.78:
I corrected two reports that were not working. ("GroupWise - Total Messages - Daily Graph" and "GroupWise - Total Messages - Daily") The fix on the "GroupWise - Total Messages - Daily Graph" required the creation of a new table. However, for the timebeing, this table will reside in the front-end.

NOTE: You should periodically perform maintenance on the Front andBack-end databases by running the "Tools - Database Utilities - Compact and Repair Database" option. This will reduce the size of the databases which grow over time.

Changes for 1/6/2003 version 1.77:
Added two new reports
    a. Inbound Message Senders with over X messages
    b. Inbound Message Domains with over X messages
2. Where appropriate, reports are columnized to reduce paper wastage.
3. Added a line in several reports to display the date range selected or the default dates of the data included in the report. (Thedefault dates would be the first and last dates in the "Accounting Data Dates Imported" table.)

Changes for 11/15/2002 version:
Replaced "Loaded References.PCX" and "Installation Instructions.DOC" files in the zipped file.

Changes for 11/5/2002 version:
1. Changed the Direcotry object on the Import and Export Menus to pass the location to the Import and Export process. As a result, it doesn't matter where the files are located. Simply "path" to the files and the program will properly select the files. To acomplish this, I added a field to the table structure. This will require you to Export your data, replace the tables databaseas well as to front-end, and re-import the data.
2. Added the total size of the E-Mail to the "GroupWise - Total Messages - Daily" report.
3. Added the ability to filter the "GroupWise - User Details" and "GroupWise - User Statistics" reports to multiple users.
4. Included instructions ("Fix GWAcctFiles Object.doc") to fix the GWAcctFiles object on the Import/Export menus.
5. Changed the import process to delete the files instead of renaming them with a different extension. Make a copy before importing if you need to keep a copy of the files for historical purposes. (The way I do it now is to import all the data for a given month. Then, I export the data for that month [creating one file] and then re-import the data. I'll work on a process to just export the data without deleting from database.)

Changes for 9/6/2002 version:
1. Incorporated process to calculate actual message date/time based on your Time Zone setting. A few users informed me that the record dates in the GWIA Acct file were shown as GMT regardless of your GWIA's/Server's timezone setting. I created a form for you to enter your GMT differential so the import process can create an entry in the record showing the actual date/time of the message compared to the GMT.
2. Added a few more reports based on user requests.
3. The Data Archive process works.
4. Zip file contains Access97 File version. However, you will need to relink the "GW Accounting Data 97.mdb" to the "GW Accounting Data Tables 97.mdb". (See instructions.)

The 7/26/02 version includes the following enhancements to the Import function:
a) Skipping files with errors and displaying a message regarding skipped files.
b) Re-importing "Archived" data by using a "Radio" button.

The 7/17/02 version includes the following enhancements:
a) Number of inbound and outbound messages daily (for a month or week).
b) Top 10 users - receivers and senders daily.
c) Top 10 domains - receivers and senders daily.

NOTE: Directory location of the back-end database tables is important because the program looks for the data in the specified "Link" location. If you want to place the data-tables in a different location you must edit the Access database table link.

  1. 1. Place the "GW Accounting Data.mdb" and "GW Accounting Data Tables.mdb" databases in following location:

        C:\Program Files\GW Accounting Data

    If you desire, you can place the "GW Accounting Data Tables.mdb" database in a different location. If you do, complete steps a. and b. below.
    1. Place the "GW Accounting Data Tables.mdb" database in the desired location on the server. (The drive location must be the same for all users.)
    2. Open the "GW Accounting Data.mdb" database. Press the "Tools-Database Utilities-Linked Table Manager"
    3. Check the "Always prompt for new location" box, press the "Select All" button and then press the "OK" button.
      1. Browse to the appropriate location.
      2. Double-Click on the "GW Accounting Data Tables.mdb" database.
      3. Click the "OK" button when the process completes successfully.
    4. Copy the "Directory.ocx" and "mscomctl.ocx" files into one of the following directories:


    5. Open the "GW Accounting Data.mdb" database and press "Ctrl-F4" to close the main menu.
      1. Select the "Tools-ActiveX Controls..." menu option to open the "ActiveX Controls" dialog box.
      2. Click on the "Register..." button and browse to the directory where you placed the "Directory.ocx" and "mscomctl.ocx" files. Select the files and press the "OK" button. This will register the files with your Access program.
      3. Open any of the Modules in edit mode. Click on the "Tools-Preferences" option. Compare your loaded options to the "Loaded References.pcx" picture. At a minimum, you must have these preferences loaded. (NOTE: Be sure that the names of the preferences are the same.) If required, scroll down the list until you find the required option.
    6. Create a folder called "Agent Accounting Data" under the "C:\Program Files\GW Accounting Data" folder.
    7. Open NetWare Administrator and open the GWIA details page.
      1. Select the "Gateway Administrators" button.
      2. Select yourself from the list of "Gateway Administrators" and check the "Accountant" option.
    8. Nightly, you will receive an "E-Mail" message with the following subject: "Agent Accounting Data File".
      1. Right click on the attached file (called "acct") and select the "Save" option.
      2. Save the files in the "C:\Program Files\GW Accounting Data\ Agent Accounting Data", giving it a unique name (e.g., "February 21, 2002.txt" for the file received on that date).
    9. Open the database and select the "Open Import Menu" button. This opens the "Data Import Menu" form. Click on the "Import GW Accounting Data" button to import the data. (NOTE: If your GWIA locked-up and had to be restarted, the "Acct" file may be corrupted. Open the file to determine if there are "corrupt" entries. If so, delete all corrupt entries before importing the data.)


How To-Best Practice
Comment List