ACC1x: Implementing Counter Values on Attached SQL Server Tables

Last reviewed: May 20, 1997
Article ID: Q100166

The information in this article applies to:

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:

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

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.

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
Hardware : X86
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.