ACC1x: Implementing Counter Values on Attached SQL Server Tables

• Microsoft Access versions 1.0, 1.1

SUMMARY

When you create a table in Microsoft Access and port it to SQL Server, some tasks are difficult to implement because SQL Server does not include a Counter data type. This article discusses how to maintain the counter functionality of an attached SQL Server table.

MORE INFORMATION

With a multiuser system, you must take special precautions. Assume that the Counter column in SQL Server is called ID and that ID is the unique index for this table. In most cases, users try to simulate counters with a SQL Server OnInsert trigger that computes the maximum value and adds 1. However, Microsoft Access uses that index (bookmark) to point to the record and is not aware that the trigger is changing the index value. Thus, Microsoft Access loses track of that record until the next time the table is queried.

Since SQL Server triggers do not work and SQL Server does not have a unique Counter data type as does Microsoft Access, you must use an Access Basic function to control the counter value, as in the procedure below:

1. In a single-column, single-row Microsoft Access table, store a value that represents the next highest counter value.
   
   NOTE: You must store the table in Microsoft Access, since the OpenTable action does not work on an attached SQL Server table and, therefore, cannot open the table exclusively. For more information on the OpenTable action, refer to the Microsoft Access "Language Reference" manual.



2. In the data entry form, apply a function to the BeforeUpdate event so that the function completes the following tasks:
   
   
   • Open the table that contains the counter value
   
   
   
   • Grab the value for the current record
   
   
   
   • Add 1 to the value in the table
   
   
   
   • Close the table
   
   
   
   Each user must have exclusive access to the table to ensure that no duplicate values are recorded. You must do error checking when trying to open the table exclusively because another user may have the table open at the same time. If this occurs, the program loops and retries several times before alerting the first user.



3. Once the value has been retrieved, set the ID of the current record equal to that value and commit the record to the SQL Server database.

Keywords : kbusage OdbcOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo