[AccessD] Sleuthing - query has become non-updateable

Doug,

An application that runs at a client site has a table with an auto
number primary key.  Occasionally Access loses track of the next value
for the auto number field and attempts to reuse a number that already
exists.  We have never been able to figure out why this happens, but the
solution is to reseed the next value with this subroutine:

Public Sub FixEmpID()
    Dim lngSeed As Long
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT Max(tblEmployee.atnEmpID) AS MaxOfatnEmpID FROM
tblEmployee;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    lngSeed = rs("MaxOfatnEmpID") + 1
    strSQL = "ALTER TABLE tblEmployee ALTER COLUMN atnEmpID COUNTER(1,"
& lngSeed & ")"
    CurrentDb.Execute strSQL
    rs.Close
    Set rs = Nothing
    strSQL = ""
End Sub

Joe O'Connell


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele
Sent: Wednesday, January 11, 2012 5:33 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sleuthing - query has become non-updateable

Backups:  As far as I can remember we did have a backup, but it was at
least 24 hours old and the client was reluctant to take the chance of
losing a day's work.  And I had I initially assured him it would just
take a few minutes to fix....The other two times it happened, I figured
it out pretty quickly.

Autonumber: The situation has only occurred in tables with autonumber
primary keys.

Doug

On Wed, Jan 11, 2012 at 10:02 AM, Mark Simms <marksimms at verizon.net>
wrote:

> In the start-up form, this should be fairly trivial to check for this 
> problem via VBA, correct ?
>
> Does it also happen in the case where the primary key is NOT
autonumber ?
>
> > I can't tell you why, but every once in a while I have the problem 
> > where a corrupted row in a table changes the table definition, 
> > removing
> the
> > primary key designation of an autonumber field.  At the same time 
> > the autonumber value sequence has gone bad, and the database tries 
> > to create
> duplicate
> > autonumber values.  I have had to rebuild the table, as Lambert 
> > suggests.
> >
> > Doug
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com