Error log table in access

Error Handling in VBA

Every function or sub should contain error handling. Without it, a user may be left viewing the faulty code in a full version of Access, while a run-time version just crashes. For a more detailed approach to error handling, see FMS’ article on Error Handling and Debugging.

The simplest approach is to display the Access error message and quit the procedure. Each procedure, then, will have this format (without the line numbers):

For a task where several things could go wrong, lines 7

8 will be replaced with more detail:

The Case Else in this example calls a custom function to write the error details to a table. This allows you to review the details after the error has been cleared. The table might be named «tLogError» and consist of:

Field Name Data Type Description
ErrorLogID AutoNumber Primary Key.
ErrNumber Number Long Integer. The Access-generated error number.
ErrDescription Text Size=255. The Access-generated error message.
ErrDate Date/Time System Date and Time of error. Default: =Now()
CallingProc Text Name of procedure that called LogError()
UserName Text Name of User.
ShowUser Yes/No Whether error data was displayed in MsgBox
Parameters Text 255. Optional. Any parameters you wish to record.

Below is a procedure for writing to this table. It optionally allows recording the value of any variables/parameters at the time the error occurred. You can also opt to suppress the display of information about the error.


Creating an Audit Log

(Note: Access 2010 contains Data Access Macros (effectively triggers), — a better way to create an audit trail if you use the new database format.)

Access cannot log changes to your data at the record level. This article describes one solution for logging inserts, deletions, and edits. The typical multi-user situation is supported, and the code can be called from different forms open at the same time. Limitations of this solution include:

  • each table to be audited must have an AutoNumber (or long integer) primary key;
  • your interface must limit users to manipulating data only through forms;
  • any cascading updates or deletes will not be logged;
  • replica databases are not supported;
  • the Confirmation options must be turned on for this to work. (Tools | Options | Edit/Find | Confirm in Access 95 — 2003; in Access 2007, Office Button | Access Options | Advanced | Confirm.)
  • Does not work with Multi-Valued Fields (Access 2007.)

This approach uses a temporary table to log the proposed changes in the form’s Delete and BeforeUpdate events. It then moves the proposed change tothe true audit table when the operation is assured in AfterDelConfirm/AfterUpdate. The code consists of four functions, called by the four form events:

  • AuditDelBegin() , called by the Delete event;
  • AuditDelEnd() , called by the AfterDelConfirm event;
  • AuditEditBegin() , called by the BeforeUpdate event;
  • AuditEditEnd() , called by the AfterUpdate event.

The end result is a copy of the record in the audit log, stamped with username, date and time, and type of operation (deletion, insertion, or edit). For deletions, the copy represents the data at the point it was deleted. For new inserts, the log contains the new entry. For edits, two copies are written: one marked «EditFrom» represents the record as it was before the update, and the other marked «EditTo» as is was after. In this way the log always contains redundant data. Combined with the fact that the audit log’s AutoNumber should always be sequential, this makes any human tampering with the log more obvious.

Although the code does nothing more than execute a few action queries, you need a good grasp of the Access form events to follow how it works.

Читайте также:  Illegal os version error

Debugging hint: Until you have the code working, remove the single quote at the start of the 2nd line in each procedure. When it fails, you will be able to see which line has not completed. For example, if you have not designed the tables exactly right, the SQL statement will fail.

Pitfalls in the Process

Logging Deletions

It is possible for a user to select multiple records for deletion in Continuous or Datasheet view. Access begins a transaction, removes the records, asks the user for confirmation, then commits or rolls back the transaction. The form’s Delete event occurs for each record being deleted. At this point you have access to the data, but no idea whether the deletion will be committed. When the form’s BeforeDelConfirm event occurs, you no longer have access to the data. The form’s AfterDelConfirm event occurs even if the user cancels, with the Status argument indicating whether the deletion was committed or cancelled.

In the form’s Delete event, the code below writes a copy of the record to the temp table. In the form’s AfterDelConfirm event, these records are copied into the true audit table only if the Status argument provided by Access indicates that the deletion proceeded. The copies in the temp table are then removed.

Logging Edits

We use the form’s BeforeUpdate and AfterUpdate events for logging edits. There is no guarantee the update will be committed when BeforeUpdate occurs, but the old values are no longer available in AfterUpdate. The code therefore uses BeforeUpdate to record the old values in the temp table, marked «EditFrom». AfterUpdate copies that record to the audit log along with the new values marked «EditTo», and then clears the temp table.

However, if the update fails after the BeforeUpdate code has executed, the AfterUpdate event does not occur. It is therefore possible for the temp table to contain spurious records representing failed edits. Fortunately, a user can edit only a single record in any one form at one time. The code therefore deletes any records in the temp table prior to writing the «EditFrom» copy in BeforeUpdate. If the database is distributed (front-end interface, back-end data), the temp table must be local to the workstation, so the user does not delete other workstations’ valid entries in the temp table. Realistically, the time delay between BeforeUpdate and AfterUpdate is minimal, but the local temp table is the safe choice.

If you have other validation code in the form’s BeforeUpdate, it makes sense to run that code first, and call AuditEditBegin() only if you do not cancel the update.

Logging Inserts

BeforeUpdate and AfterUpdate still occur when the user saves a new entry. In this case, we suppress generation of an «EditFrom» entry (since there are no old values), and mark the log entry as «Insert» instead of «EditTo» in the AfterUpdate event. To do this, the form must pass an argument to our code indicating whether this was a new record. That’s easy in BeforeUpdate, but by the time AfterUpdate fires, the form’s NewRecord property will always be false. The form needs a module level variable set to the value of Me.NewRecord in BeforeUpdate, so as to provide that value again in AfterUpdate.

Create the Four Functions

To create a general module with its functions:

  1. Select the Modules tab in the Database Window/Nav Pane. Click New. Access opens a new code window.
  2. Copy the code in this link, and paste it into the module.
  3. Either remove the four references to function LogError(), or set up error logging as described in Error Handling.
  4. Choose Compile from the Debug menu. Fix any errors Access highlights.
  5. Save the module with a name such as ajbAudit.
Читайте также:  Exception at address 0x006f5636 base

Create the Temporary Table and Audit Log Table

For each table you wish to audit, you will create two more tables, as follows. If your database is split into two mdb files, the table created at step 2 must be in the code mdb (workstation file), and the table at step 5 in the data mdb (server file.)

  1. In the Database window/Nav Pane, select the table to be audited.
  2. Copy (Ctrl+C) and paste (Ctrl+V). Choose the «Structure Only» option so the data is not copied. Supply a name for the temp table: for example, if the table to be audited it named tblInvoice, use the name audTmpInvoice .
  3. Open the temp table in Design view. Remove all indexes (including the Primary Key), but do not delete the fields. Change the AutoNumber field type to a Number, Long Integer. Remove any Validation rules from the table and all its fields. Make sure the Required property is set to No for all fields. Save. (Note: the temp table has no primary key.)
  4. Still in Design view of the temp table, insert these three new fields at the top of the table (i.e. before other fields):
Field Name Field Type Size
audType Text 8
audDate Date/Time
audUser Text 40

Enter the Form Events

You are now ready to enter 6 lines of code for your form. Replace the names in the example with the names of the tables and fields of your database, like this:

Where you see Use
tblInvoice the name of the table to be audited
audTmpInvoice the name of your temp table
audInvoice the name of your audit log table
InvoiceID the primary key of the table being audited
  1. In the Database window/Nav Pane, select the form used to enter/edit/delete data. Open the code window (View | Code in Access 95 — 2003; Design | Tools | Code in Access 2007.) Access opens the form in Design View, and displays the code window. On a fresh line in the General Declarations section, add the line:
    Dim bWasNewRecord As Boolean
  2. Select the form. In the form’s properties, locate OnDelete under the Event tab. Choose [Event Procedure], and click the build button (. ). Access opens the code window. Beneath any validation code you have there, enter a line like this:
    Call AuditDelBegin(«tblInvoice», «audTmpInvoice», «InvoiceID», Nz(Me.InvoiceID,0))
  3. In the form’s properties, locate AfterDelConfirm. Choose [Event Procedure], and click the build button. In the code window enter a line like this:
    Call AuditDelEnd(«audTmpInvoice», «audInvoice», Status)
  4. Locate the form’s BeforeUpdate property. Choose [Event Procedure]. Click the Build button. Enter these two lines:
    bWasNewRecord = Me.NewRecord
    Call AuditEditBegin(«tblInvoice», «audTmpInvoice», «InvoiceID», Nz(Me.InvoiceID, 0), bWasNewRecord)
  5. Locate the form’s AfterUpdate property. Choose [Event Procedure]. Click the Build button. Enter this line:
    Call AuditEditEnd(«tblInvoice», «audTmpInvoice», «audInvoice», «InvoiceID», Nz(Me!InvoiceID, 0), bWasNewRecord)
  6. Save the form.
  7. Repeat steps 1 to 6 for other forms as needed.
  8. Once you have it all working, activate the error handling by removing the single quote from the startof line 2 of each of the five functions, i.e.:
    ‘ On Error GoTo .

Each edit, insert, or delete will now be logged.


MySQL ERROR Log Table Explained

Over the decades we have been reading the MySQL error log from the server system file, if there are any issues in MySQL or any unknown restart happened , generally we look at the mysql error log.

By default MySQL error log can be found in the default path /var/log/mysqld.log , or it can be explicitly configured using the variable log_error .

Few drawbacks using MySQL error log as FILE

  • Possibility of missing genuine errors while reading lengthy information.
  • Filtering of errors for the particular date and timeframes.
  • Cannot provide the DB server access to developers because of fear of mishandling DB servers.

To overcome the above issues , from MySQL 8.0.22 we can access the error-log from the performance_schema.error_log table.

Granting the SELECT privilege for the error_log table will provide the read access to error log contents using a simple SQL queries for dev teams.

For demo purpose i have installed latest MySQL 8.0.23 in our test environment.

Overview to the columns


LOGGED corresponds to the time field of error events occurred , the time values which are stored in the error log table are displayed according to the log_timestamps system variable i.e UTC by default.

we can change the timestamp value by changing log_timestamps variable according to our time zones.


It is a MySQL thread ID similar to PROCESSLIST_ID , the thread_id is displayed for events occurred by foreground MySQL threads, for background threads the value be 0.


The event priority the permitted values are System , Error , Warning , Note .


Displays the MySQL error codes.


The subsystem in which the event occurred, example SERVER,INNODB.


The text representation of the error event.

Variables controlling error logging

Below are the major variables controls the error logging , which defines the output of the error log.

  • log_error – This variable defines the path of the MySQL error log.
  • log_error_services – This variable controls which log components to enable for error logging ,by default the values are set to log_filter_internal and log_sink_internal.
  • log_filter_internal : This value provides error log filtering based on the log event priority and error code, in combination with the log_error_verbosity and log_error_suppression_list system variables.
  • log_sink_internal : This value defines the traditional error log output format.
  • log_error_suppression_list – This variable helps to filter or ignore the errors, information and warnings which is not worthy or which creates unnecessary noise in the error log.

we can list of one or more comma-separated values indicating the error codes which we need to suppress. Error codes can be specified in symbolic or numeric form.


Suppose if we insist to filter out the below warning message from error log:

log_error_suppression_list takes effect based on the log_error_verbosity value defined

This variables specifies the verbosity of events which will be logged in the error log , permitted values from 1 to 3. default is 2.

log_error_verbosity Value Permitted Message Priorities

Now let us query the performance_schema.error_log table

with simple SQL queries , we can filter the logs based on priority

The older events from error_log able are automatically purged when there is a need of space for new events.

The error_log table status can be monitored from SHOW STATUS variables.

Limitations of error_log table

  • TRUNCATE TABLE is not permitted on error_log table.
  • The table cannot be index , each column is already indexed by default.

Finally ,introduction of error_log table in MySQL 8 has made error readability more convenient and easy, now the error log can be accessed from remote clients as well with few simple SQL queries without accessing the physical system file.

MySQL error logs table feature benefits the remote connections using MySQL shell and will benefits a lot in DBaaS like AWS RDS , AZURE MySQL , Google Cloud SQL platforms. Where the error log needs a console and log retention is complex.


Оцените статью