[AccessD] Sleuthing - query has become non-updateable

Thanks!  I do this, but always by manually building a query.  I've never
seen ALTER COLUMN/COUNTER before.  I'm going to take your code and modify
it so I can pass in the table name and field name, then put it in my
standard toolkit module.

Doug

On Thu, Jan 12, 2012 at 11:41 AM, Joe O'Connell <joeo at appoli.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>