> cool solutions home   > cool tools home
GroupWise Accounting Data Report 1.79
Generate a report on users' email traffic.
Reader Rating    from ratings rate this article
View a Printer Friendly Version of this Page Send this page to a friend
Updated: 6 May 2003
File Size: 1.09MB
License: Free
Download 1:  gw_acct_database.zip
Publisher: Chris Premo
E-mail: cpremo@TAKETHISOUTmedbd.ca.gov
Please read the note from our friends in legal before using this file.

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.

Text only changes for 5/6/2003:

I've received a lot of E-Mails regarding an error ("object doesn't support this property or method - 438") when opening the Data Import Form. I've not been able to duplicate this error on my PC. However,there is a Word document in the Zip file that talks about fixing this problem. It is called: "Fix GWAcctFiles Object.doc".

Also, another individual recommended the following change to the database. Change the naming of the Export file to "YYYY-MM Archive.TAB". I'll add this to the next version. However, if you wantto incorporate this change now, complete the following:

1. Open the "GW Accounting Data Module" module in design mode.

2. Find and change the following code in the "Public FunctionArchiveData" function from:

'This sets the export FileName
FileName = Month(FormName![Period].Value) & "-" &


'This sets the export FileName
FileName = Year(FormName![Period].Value) & "-" &

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.

  2. Copy the "Directory.ocx" and "mscomctl.ocx" files into one of the following directories:


  3. 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.

  4. Create a folder called "Agent Accounting Data" under the "C:\Program Files\GW Accounting Data" folder.

  5. 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.

  6. 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).

  7. 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.)