MS-Access / Getting Started

---

Analyzing Errors in the USysApplicationLog Table

So far in this tutorial, all the data macros you've created and tested executed without problems. In a working application used by many users, however, it's quite possible that Access can and will encounter errors executing data macros. Access can also run into errors while you are in the development phase of creating, testing, and debugging your data macros. Access manages any errors it encounters executing data macros through a special system table called USysApplicationLog. This special table serves three purposes:

• Access uses it to log any data macro failures that it encounters while executing data macros attached to table events and named data macros.
• You can use the table for debugging purposes when designing and testing data macros by utilizing the LogEvent data action.
• Access uses this table to record any compilation errors when publishing or synchronizing web objects to a SharePoint server running Access Services.

In the previous section, you deleted the test named data macro called ClearLogsTest attached to the tblErrorLog table. In the After Insert event of the tblAppointments table, we still have a RunDataMacro action that calls the now deleted ClearLogsTest named data macro. If we add a new appointment record to the tblAppointments table, Access attempts to run a named data macro that no longer exists. Let's try this out and see how Access responds to this problem. Open the tblAppointments table in Datasheet view, create a new appointment record, and then save the record. You'll notice that Access does not display any error dialog boxes or messages to you, even though we know that it must have encountered a problem. Access does, however, provide you a small error indication message in the status bar. If you look at the lower-right corner of the status bar, you can see the words "New Application Errors".

If you hover your mouse over New Application Errors in the status bar, Access displays a tooltip explaining that there are new errors in the USysApplicationLog table. These words in the status bar also serve as an entry point to open this error table. Click New Application Errors, and Access opens the USysApplicationLog table in Datasheet view.

The USysApplicationLog table contains the following fields: SourceObject, Data Macro Instance ID, Error Number, Category, Object Type, Description, Context, and Created. We've reproduced the information in Table-4.

Table-4 USysApplicationLog Table Results

Field Name 		Data
SourceObject 		tblAppointments.AfterInsert
Data Macro Instance ID 	{D76F9C0A-910E-402D-B20E-7BC802F11274}
Error Number 		-8979
Category 		Execution
Object Type 		Macro
Description 		The data macro 'tblErrorLog.ClearLogsTest'
			could not be found.
Context 		RunDataMacro tblErrorLog.ClearLogsTest
Created 		3/1/2010 8:23:14 PM

The SourceObject field lists the name of the table and the specific event where Access encountered an error. In our example, you can see that Access logged an error for the AfterInsert event attached to the tblAppointments table. The Data Macro Instance ID is the unique ID that Access uses to track the execution of the data macro. The Error Number is the specific internal error number that Access uses to record errors while executing data macros. In the Category field, you can see Access lists this error as an Execution error. The Object Type field indicates the specific type of object where Access encountered an error; in our example, it was a macro. In the Description field, Access lists more specific information about the nature of the issue that it encountered. Access informs you that it could not find the ClearLogsTest data macro attached to the tblErrorLog table. Access lists the specific context where it hit an error in the Context field. Access gives you information that it was trying to execute a RunDataMacro action that calls the ClearLogsTest named data macro attached to the tblErrorLog table. The last field, Created, lists the date and time when Access hit the error.

By default, there is no USysApplicationLog table when you create a new client or web database. Access creates this error table the first time that it encounters errors during data macro execution or if you use the LogEvent data action in a data macro. (You'll see examples of the LogEvent data action later in this tutorial.) If the USysApplicationLog table already exists in your database, Access appends new records to the table instead of creating an additional table. If you delete this table from the Navigation pane, Access recreates another table when it needs to log an error. You can also access the USysApplicationLog table at any time from the Backstage view. Click the File tab on the Backstage view, click Info, and then click the View Application Log Table button.

Access does not display the USysApplicationLog table in the Navigation pane by default because the table starts with the prefix USys. To see user-defined system tables-tables that start with the prefix USys-you need to change your navigation options to display system objects. To do this, right-click the menu bar at the top of the Navigation pane and then click Navigation Options on the shortcut menu. Access opens the Navigation Options dialog box.

Under Display Options, select Show System Objects, and then click OK. You'll notice that Access now displays the USysApplicationLog table in the Navigation pane. You'll also notice that Access now displays another table with the USys prefix- USysRibbons-as well as several tables that start with the MSys prefix. The USysRibbons table holds the data needed to create custom ribbons for the Back Office Software System sample web database. Tables that start with the MSys prefix are system tables created by Access to control your database application.

CAUTION! You should not attempt to modify any tables that start with the MSys prefix; you might cause irreparable damage to your database!

Let's change the navigation options back to what they were so we don't see all the extra Access system tables. Right-click the menu bar at the top of the Navigation pane and then click Navigation Options on the shortcut menu. Clear the Show System Objects option on the Navigation Options dialog box and then click OK.

You should now delete the After Insert event that you created earlier for the tblAppointments table. Remember, we deleted the ClearLogsTest named data macro, which means Access will continue to hit errors whenever you add new records to the tblAppointments table. To do this, open any table in Datasheet view, click the Table contextual tab, click the Named Macro button in the Named Macros group, and then click Rename/Delete Macro from the drop-down list to open the Data Macro Manager again. Click the Delete link next to the After Insert event under the tblAppointments table and then close the dialog box.

Access Logs Save Conflicts to USysApplicationLog Table When Access attempts to commit data using the SetField action inside an EditRecord block, it could encounter a save conflict because someone else is editing the data in the same record. If this happens, Access retries the SetField action up to five times. If Access fails to commit the requested changes on the fifth attempt, Access logs an entry into the USysApplicationLog table indicating that it encountered a save conflict and could not commit the data. If you have data macro logic attached to table events and named data macros in your application, we recommend that you periodically check the USysApplicationLog table for any types of errors, including save conflicts.