Microsoft KB Archive/100166
ACC1x: Implementing Counter Values on Attached SQL Server Tables
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
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.
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:
- 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.
- 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
- 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
Issue type : kbinfo
Last Reviewed: November 4, 2000