February 19, 2009

How to log errors in Microsoft Access

  • Date: October 31st, 2008
  • Author: Susan Harkins

If you Google “error logging in Microsoft Access,” you’ll find a number of complex solutions. But the process doesn’t have to be that difficult. Most of us need only a simple function to log specific information about the current error.


Despite all your best efforts, errors occur in every database. Most developers include adequate error-handling routines in their code, but that might not be enough. Knowing when an error occurs and how often it occurs can be important to resolving the issue and avoiding future errors. Access doesn’t track errors, but you can add that functionality to any database. All you need is a table and a little code.

Note: Our companion download includes a demonstration database and a several .bas module files you can import into any Access database. Don’t cut and paste the code directly from here into an Access module, as the article text contains formatting that will generate errors.

The easiest way

If you have just one database to maintain and you’re its only user, error logging isn’t a critical issue because you’re around when the error occurs. However, it’s difficult to display internal error information at the time of the error. It isn’t impossible, but even if your application displays it, you have to remember all of it. For that reason alone, adding a log can be helpful, especially during the testing stage.

The simplest way to log errors is to let each procedure’s error-handling routine do it. The error routine shown in Listing A (basErrorLog1.bas) inserts a record with error information into an existing table. To create the code, select Module in the Database window and then click New on the Database toolbar. Enter the code and save the module. (You can import the .bas file into any Access database, even an empty one.)

Listing A: ThrowError()

Function ThrowError()  On Error GoTo errHandler Dim strSQL As String Dim strDescription Err.Raise 6 Exit Function

errHandler:  strDescription = Chr(34) & Err.Description & Chr(34) strSQL = "INSERT INTO tblErrorLog (ErrDate, CompName, UsrName, " _ & " ErrNumber, ErrDescription, ErrModule)" _ & " VALUES(#" & Now() & "#, '" & Environ("computername") _ & "', '" & CurrentUser & "', " & Err.Number _ & ", " & strDescription & ", '" & VBE.ActiveCodePane.CodeModule & "')" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True MsgBox "Error has been logged", vbOKOnly, "Error"

End Function

The function intentionally throws an overflow error in order to execute errHandler. This code builds a SQL INSERT INTO statement using the following information: the date, the computer’s name, the user’s name, the internal error number and description, and the module that generated the error.


About INSERT INTO

SQL’s INSERT INTO statement works well as long as you use it correctly:

  • The target table (in this case that’s the error log table) must contain every field in the INTO and VALUES clause.
  • You can omit a field from the INTO clause as long as you also omit it from the VALUES clause; these two clauses must match.
  • When including field references in the INTO clause, their order must match their placement in the table (check the table in Design view, which won’t always agree with Table view).
  • Don’t include an AutoNumber field in either clause.
  • Jet won’t complain if there’s no data for a specified field, unless doing so violates a field constraint. That behavior could help or hinder.
  • You can omit all of the fields from the INTO clause, but you must account for every field in the target table in the VALUES clause.


You can customize the information to fit your needs. For instance, if no one else is using the database, you won’t need the user or computer names. Just remember to delimit each item correctly. Delimiters cause a bit of a problem for the error’s description text, as quite often this text includes single quotation marks. Assigning the text to a string variable, separate from the SQL string variable, makes the text easier to handle.

You must add this capability to every procedure to maintain a comprehensive log. In addition, before executing the code, create the error log table. The name of the example’s table is tblErrorLog, and it contains the following fields:

ErrDate: Date/Time

CompName: Text

UsrName: Text

ErrNumber: Numeric

ErrDescription: Text

ErrModule: Text

UserName and Computer Name are reserved words; even though those labels are more meaningful, you can’t use them to name a field or variable.

You can execute the code from inside the VBE. To do so, position the cursor anywhere inside the procedure’s code and press F5. Clear the message shown in Figure A. Open tblErrorLog to see the error record shown in Figure B.

Figure A

figure a

Clear the informational message.

Figure B

figure b

The table shows the new error record.

A project-level routine

Inserting error -logging code into every procedure works, but it’s inefficient. Consider calling a dedicated function, like the one in Listing B, instead. Call LogError() from each procedure’s error handling routine, as the procedure in Listing C shows. (Listing B and Listing C are in the download as basErrorLog2.bas.)

Listing B: LogError()

Public Function LogError()  'Log error information to tblErrorLog. Dim strDescription As String Dim strSQL As String strDescription = Chr(34) & Err.Description & Chr(34) strSQL = "INSERT INTO tblErrorLog (ErrDate, CompName, UsrName, " _ & " ErrNumber, ErrDescription, ErrModule)" _ & " VALUES(#" & Now() & "#, '" & Environ("computername") _ & "', '" & CurrentUser & "', " & Err.Number _ & ", " & strDescription & ", '" & VBE.ActiveCodePane.CodeModule & "')" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True

End Function

Listing C: ThrowError()

Function ThrowError()  On Error GoTo errHandler Err.Raise 6 Exit Function

errHandler:  Call LogError MsgBox "Error has been logged", vbOKOnly, "Error"

End Function

When ThrowError() experiences an error, VBA passes that error to errHandler, which calls LogError(). The LogError() function inserts a new record using the error information into tblErrorLog and then returns to ThrowError().

A more compact solution

The one disadvantage to using pure SQL is that the statement can grow rather long and unwieldy. If you’re not comfortable writing SQL, this solution might be a little difficult for you. There are other reasons to avoid SQL:

  • If there are any plans for upsizing to SQL Server, avoid using Jet SQL, as SQL Server uses Transact-SQL (T-SQL). You’ll have to modify the SQL code so that SQL Server can use it.
  • If you’re using Access to work with SQL Server tables, the Jet SQL will fail. You could use a Pass-Through query, but you must be familiar with T-SQL.

An ActiveX Data Object (ADO) Recordset presents a more versatile solution than pure SQL. The code in Listing D (basErrorLog3.bas in the download) uses ADO to log errors.

Listing D: LogError()

Public Function LogError()  'Log error information to tblErrorLog using ADO. Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset

  Set cnn = CurrentProject.Connection  rst.Open "SELECT * FROM tblErrorLog", cnn, _ adOpenKeyset, adLockOptimistic

  With rst    .AddNew !ErrDate = Now() !CompName = Environ("computername") !UsrName = CurrentUser !ErrNumber = Err.Number !ErrDescription = Err.Description !ErrModule = VBE.ActiveCodePane.CodeModule .Update End With

  rst.Close  Set rst = Nothing Set cnn = Nothing

End Function

Execute ThrowError(), and the results are the same. The only difference is that the ADO version is more versatile because it will run against a SQL Server backend, as long as you establish the right connection (update the CurrentProject.Connection shortcut the example uses, appropriately).

Add error handling to the error logging

So far, the error-logging routines haven’t included error handling. For that reason, if you add one of these solutions as is, thoroughly test it to make sure you accommodate all possible errors.

You might want to add error handling to the error-logging routine, because any error within the error-logging routine will write over the current error properties you’re trying to log. Passing the error-logging information as shown in Listing E is a simple solution. As with all the previous examples, call the error logging function by executing ThrowError() in Listing F. (Listings E and F are in download file as basErrorLog4.bas.)

Listing E: LogError()

Public Function LogError(errdte As Date, comname As String, _ usernme As String, errno As Long, errdes As String, errmod As String) As Boolean 'Log error information to tblErrorLog using ADO. Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim varErrors As Variant Dim varFields As Variant On Error GoTo errHandler

  Err.Raise 5

  varFields = Array("ErrDate", "CompName", "UsrName", _   "ErrNumber", "ErrDescription", "ErrModule") varErrors = Array(errdte, comname, usernme, errno, errdes, errmod)

  Set cnn = CurrentProject.Connection  rst.Open "SELECT * FROM tblErrorLog", cnn, _ adOpenKeyset, adLockOptimistic

  rst.AddNew varFields, varErrors  rst.Update

  rst.Close  Set rst = Nothing Set cnn = Nothing LogError = True Exit Function

errHandler:  Debug.Print errdte Debug.Print comname Debug.Print usernme Debug.Print errno Debug.Print errdes Debug.Print errmod Debug.Print Err.Number Debug.Print Err.Description

  Set rst = Nothing  Set cnn = Nothing LogError = False End Function

Listing F: ThrowError()

Sub ThrowError()  On Error GoTo errHandler Dim booLogError As Boolean Err.Raise 6 Exit Sub

errHandler:  booLogError = LogError(Now(), Environ("computername"), CurrentUser, _ Err.Number, Err.Description, VBE. _ ActiveCodePane.CodeModule) If booLogError Then MsgBox "Error has been logged", vbOKOnly, "Error" ElseIf booLogError = False Then MsgBox "Error wasn't logged", vbOKOnly, "Error" End If

End Sub

This version of ThrowError() passes the current error information to LogError(), which purposely throws another error in order to execute its error-handling routine. Of course, when applying this to your own work, you’ll want to delete LogError()’s Err.Raise statement. The error-handling routine prints the error values only to verify that the original error values still exist, even after a new error occurs. How you handle those values will be up to you. Just remember, as is, their scope is specific to LogError(). Once VBA returns flow to ThrowError(), they’re gone. The array presents an easy but alternate way to update the log table. Stick with the ADO solution if you’re working with SQL Server tables.

Notice that LogError() is now a Boolean function procedure (no longer a subprocedure as before). If VBA successfully logs the error, the function returns True; if not, the function returns False. The calling subprocedure (Listing F) contains an enhanced If statement that returns the appropriate message, depending on whether LogError() returns True or False. Figure C shows the simple message shown when the code fails to log an error. When you apply this technique to your own procedures, you’ll want to include specific error handling. As is, LogError() merely maps the flow for you.

Figure C

figure c

Let users know when the VBA is unable to log an error.

Worth noting

This is a good procedure to add to your code library. Just drop it into any database. cialis weekend pill If you’re working with an older database, make sure to reference the ActiveX Data Objects library (ADO).

Some developers don’t like Environ(); they complain that it’s unstable. If you have trouble with this function and you need to track the computer, use the API GetComputerNameA() function instead.

Remember, you must create the log table manually. You can, however, add code that creates the table if it doesn’t already exist.

Easy error log

If you Google error logging in Microsoft Access, you’ll find a number of complex solutions. The process doesn’t have to be hard to be good. Most of us need only a simple function to log specific information about the current error, for later use.

Permalink • Print • Comment

Don’t let disappearing error handling send you into a panic

  • Date: November 3rd, 2008
  • Author: Susan Harkins

Last week, I added a simple procedure to an existing Access database. While testing it, I noticed that VBA was ignoring the error-handling statement and routine. I ran a quick Compile and Repair and crossed my fingers, but VBA was still breaking at errors instead of passing control to the procedure’s error-handling routine.

I sent out a What’s going on? e-mail to several colleagues. I pulled up Microsoft’s Knowledgebase and began searching for answers. Finally, I sacrificed my favorite SQL book upon my altar to the Access gods.

I guess the gods were appeased because within minutes, friend and developer extraordinaire Jim Dettman responded to my e-mail and told me to check the Error Trapping settings in the Visual Basic Editor (VBE). I found Break On Unhandled Errors cleared and Break On All Errors checked. I checked On Unhandled Errors, and VBA executed my error-handling routines as expected.

november2008blog1fig1r.jpgcialis vs generic cialis />

I felt stupid for not thinking of it myself. How the option changed, I’ll probably never know. I certainly didn’t do it.

If you’d like to try it yourself, do the following:

  1. In the VBE, choose Options from the Tools menu.
  2. On the General tab, check Break On All Errors in the Error Trapping Section.
  3. Click OK.
  4. Run any routine that’s apt to return an error, as long as it has an error-handling routine. If necessary, create a temporary procedure that uses an Err.Raise statement.
  5. Repeat steps 1 through 3 to reset Break On Unhandled Errors.

When writing a procedure, you’ll want to add its error-handling statements. However, while developing and testing code, it’s good to turn off error trapping so you can experience all the errors. Instead of commenting out all the error-handling statements and routines, set Break On All Errors. VBA will ignore your error-handling statements and break on every error. That way, you’ll know whether you’re trapping everything appropriately. When you’re done, simply reset the option so your error handling can take over.

Just remember that the settings exist and you’ll avoid an unnecessary panic if your error trapping suddenly stops performing as expected.

Permalink • Print • Comment

Use custom field headers to create more professional Access reports

  • Date: November 4th, 2008
  • Author: Mary Ann Richardson

Query-based reports give you the opportunity to turn those unfriendly field names into better-looking headers. See how this quick tweak can polish up your reports.


When you run a report, the report field headers are based on the field names in the source table. If you base your report on a query, you have an opportunity to change the field headers. For example, say you’re basing your report on a query that includes the fields CompanyName, ContactName, and ContactTitle. Follow these steps:

  1. Open the query in Design mode.
  2. Right-click the CompanyName field and select Properties.
  3. Click in the Caption property box and type Client Company.
  4. Right-click the ContactName field and select Properties  (Figure A).

Figure A

client name

  1. Click in the Caption property box and type Name (Figure B).

Figure B

cialis usa border=”0″ alt=”name” title=”name” width=”327″ height=”311″ />

  1. Right-click the ContactTitle field and select Properties.
  2. Click in the Caption property box and type Title.
  3. Run the Query (Figure C).

Figure C

report query

Although the query results and any report based on the query use the new caption names, the field names remain unchanged in the source table.

Permalink • Print • Comment

Easy tricks to make your Access forms run faster

  • Date: October 2nd, 2008
  • Author: Mary Ann Richardson

By giving a form less data to digest, you can make it run more efficiently. These two tips can help.


cialis tadalafil 5mg width=”100%” size=”2″ />
The less data Access has to load into memory when you open a form, the better the performance. For example, if a form will be used solely for entering data rather than for data searches, you should change the form’s data entry property so a blank record opens directly. Otherwise, Access will read in all the records in the file before it displays the blank record at the end of the record set. Follow these steps to change the form’s data entry property:

 

  1. Open the form in Design View and click the Selector button.
  2. In the form’s property sheet, click the Data tab.
  3. Click in the DataEntry property text box and select Yes.

Another way you can improve performance is to use only default formatting and properties for most or all of the form controls. Your form will load faster because Access does not have to load the non-default form and control properties. If you must change the defaults for most of the controls in your form, create one control with the desired properties and make that the default control. To do so, follow these steps:

  1. Add a control to your form and change its defaults according to your form’s requirements.
  2. With the control selected, go to Format | Set Control Defaults.

Now, when you add the control to your form, it will have the same properties as the first one. Access saves only the properties of the default control; it does not need to store each control’s individual properties.

Permalink • Print • Comment

More natural SQL display and efficient sorting in Access list controls

  • Date: October 27th, 2008
  • Author: Susan Harkins

Concatenating data can increase readability and friendliness without losing a bit of functionality.

cialis tabs


It’s common to populate a list control with a SQL statement, especially if you want to concatenate fields. For instance, the following SQL statement displays a list of names in multiple columns:

SELECT EmployeeID, LastName, FirstName

FROM Employees

ORDER BY LastName

october2008officeblog10fig1r.jpg

The control’s Column Count property is 3 and the Column Width property is 0. That way, the control can pass a record’s primary key value (EmployeeID) unseen.

The ORDER BY clause sorts the rows by LastName. The solution is typical, but Access can do better. We’re just not used to viewing names in columnar format. Concatenating the names into a single column, as follows, displays a more familiar format:

SELECT EmployeeID, LastName & ", " & FirstName

FROM Employees

ORDER BY LastName

october2008officeblog10fig2r.jpg

I want to caution you against sorting on the concatenated field as follows:

SELECT EmployeeID, LastName & ", " & FirstName

FROM Employees

ORDER BY LastName & ", " & FirstName

It’ll work, but if the data source contains a lot of data, it won’t perform well. Sorting by individual columns is more efficient, especially if the fields are indexed; Jet can’t use an index against the concatenated results of the ORDER BY clause. Besides, it’s awkward and unnecessary. Just specify sort fields in the ORDER BY clause as you normally would — displaying concatenated data doesn’t change the way you sort.

There’s still a way to improve the display. Perhaps it really isn’t an improvement, but a different way to present the data. Transpose the names, displaying the first name first, in a more natural format. You can still sort by last names, as follows:

SELECT EmployeeID, FirstName & " " & LastName

FROM Employees

ORDER BY LastName

october2008officeblog10fig3r.jpg

Don’t worry if a sort field isn’t in the SELECT clause’s field list — SQL doesn’t require that. SQL requires only that the sort field be in the underlying data source.

Permalink • Print • Comment
« Previous PageNext Page »
Made with WordPress and a healthy dose of Semiologic • Sky Gold skin by Denis de Bernardy