In this article we look into a problem which can occur if you are writing triggers on tables linked to Access which write data into other tables. This is a linked table specific problem, and only occurs if both tables have an Identity (autonumber) column.

In the example here, we have a table called tblCompany that has an auto incrementing primary key, and we want to make a copy of each new record entered in this table into another table. One efficient solution is to write a trigger to automatically copy all newly inserted records to the target table.

Below we see the structure of the main table. This table has a timestamp field for supporting multi-user optimistic locking.

In creating a table to receive the copied rows, we have changed the timestamp field into a varchar field and the CompanyId has had the Identity property switched off (otherwise the inserts will not work). We have also added at the bottom of the table a new unique auto incrementing field. The reason that we have added a new counter is that we also want to later record updates to the records; so the CompanyId would not be unique in the target table.

Creating the trigger

Next we create a trigger on the source table, which copies any inserted records into the target table.

Top of Page

Inserting data using linked tables

We are going to start with having some data that already exists in the tblCompany table and with the tblCompanyImage table empty. We do this is to see clearly how the problem occurs; this is discussed in more detail later in the article.

Below we start entering a new record.

Then by pressing the Shift-Enter key we force Access to save the record.

Now something very mysterious seems to have happened, rather than displaying the new record, an old record is displayed!

We can open the other table and verify that the correct entry was made in this secondary table.

We can also press Shift-F9 in Access to re-query the main table, and we see that the data is correctly displayed, as shown below.

Top of Page

Explanation

The above is easily explained, once you realise that the record that Access displays has a unique identifier corresponding to the new unique number of InsertCount in the secondary table (in the case the value is 1). Access then incorrectly syncronises to display the record in the main table with primary key value 1; what is interesting here is that normally timestamps would prevent this problem, but as it is a new record being inserted Access would not know the timestamp value; which has not yet been allocated.

This happens because when inserting the record, Access gets back the last auto incrementing number created, and now the last number is not the autonumber of the primary table but the number in the secondary table.

In SQL Server, there is a variable called @@Identity which returns the last auto incrementing number created, and another function called SCOPE_IDENTITY() which returns the last auto number created for the main table you are operating on.

Unfortunately in this case linked tables in Access receive the @@Identity value, which is not what we want.

Top of Page

Solutions

One solution is not to use the trigger, but to write code in the front-end application to perform the copy operation when the data is inserted using a form; unfortunately this could not be used for linked tables in datasheet view; which is why triggers as a preferred soloution.

A second solution is not to have the field InsertCount on the target table, this does leave the table without a good choice of primary key, but it will stop the problem.

A third solution is to start with both the tables emptied, in which case the two counters CompanyId and InsertCount will be synchronised. In that case @@Identity = SCOPE_IDENTITY() and again the problem goes away. This was why we had to start with some data in the main table; to force the problem to be visible.

Top of Page

LINQ

An interesting question is to ask what happens when we use the new LINQ in Visual Studio 2008.

Once we have saved the record, we see the following display.

In the tests we conducted, we were unable to see any problematic effects in using LINQ.

Top of Page

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz