AutoNumber Regeneration

[jimfortune]

Sometimes I use Autonumber fields for ID fields. Furthermore,
sometimes I use those same fields in orderdetail type tables. So it's
important in that case that once an autonumber key value is assigned to
a record that it doesn't change. Occasionally I find that due to
corruption or an accidental deletion and restore of a record from a
backup the autonumber field needs to be tidied up. So when I create
(through AddNew) the autonumber key to be used for joins, I also save a
copy in a backup ID field (Long). I could get by with always using the
backup ID for the join but I don't like having backup ID's that are
different from the autonumber value. I decided that I really wanted to
regenerate the autonumber field to match the Backup ID values. I
couldn't get the 'force update on autonumber field to previously
deleted values' idea from a recent post to work so I created some code
to do it. It's still a little rough but might suffice to get someone
to point out an easier way. The code is in A97. I didn't have any RI
to deal with. The form shows the tables in the database and once the
table is selected, the fields populate two comboboxes for choosing the
primary key field and the backup ID field. txtNewTableName is for the
name of the new table with the repaired autonumber values. The main
idea is to use AddNew without an Update until the next backup ID is
reached.

'-------Form Code
Option Compare Database
Option Explicit

Private Sub cbxDatabaseTabl e_AfterUpdate()
Dim MyDB As Database
Dim tdf As TableDef
Dim fld As Field

If IsNull(cbxDatab aseTable.Value) Then
cbxIDFieldName. RowSource = ""
cbxBackupIDFiel dName.RowSource = ""
cbxIDFieldName. Value = Null
cbxBackupIDFiel dName.Value = Null
Exit Sub
End If
'Put the field names in cbxIDFieldName and cbxBackupIDFiel dName
Set MyDB = CurrentDb
cbxIDFieldName. RowSourceType = "Value List"
cbxBackupIDFiel dName.RowSource Type = "Value List"
For Each fld In MyDB.TableDefs( cbxDatabaseTabl e.Value).Fields
If Nz(cbxIDFieldNa me.RowSource, "") = "" Then
cbxIDFieldName. RowSource = fld.Name
Else
cbxIDFieldName. RowSource = cbxIDFieldName. RowSource _
& ";" & fld.Name
End If
If Nz(cbxBackupIDF ieldName.RowSou rce, "") = "" Then
cbxBackupIDFiel dName.RowSource = fld.Name
Else
cbxBackupIDFiel dName.RowSource = cbxBackupIDFiel dName.RowSource _
& ";" & fld.Name
End If
Next fld
Set MyDB = Nothing
End Sub

Private Sub cmdFixAutonumbe r_Click()
If IsNull(cbxDatab aseTable.Value) Then
MsgBox ("No table was selected.")
Exit Sub
End If
If IsNull(txtNewTa bleName.Value) Then
MsgBox ("No new table name was selected.")
Exit Sub
End If
If IsNull(cbxIDFie ldName.Value) Then
MsgBox ("No ID Field was selected.")
Exit Sub
End If
If IsNull(cbxBacku pIDFieldName.Va lue) Then
MsgBox ("No Backup ID Field was selected.")
Exit Sub
End If
Call FixAutoNumber(c bxDatabaseTable .Value, txtNewTableName .Value, _
cbxIDFieldName. Value, cbxBackupIDFiel dName.Value)
MsgBox ("Done.")
End Sub

Private Sub Form_Load()
Dim MyDB As Database
Dim tdfLoop As TableDef

Set MyDB = CurrentDb
cbxDatabaseTabl e.RowSourceType = "Value List"
For Each tdfLoop In MyDB.TableDefs
If Left(tdfLoop.Na me, 4) <> "MSys" Then
If Nz(cbxDatabaseT able.RowSource, "") = "" Then
cbxDatabaseTabl e.RowSource = tdfLoop.Name
Else
cbxDatabaseTabl e.RowSource = cbxDatabaseTabl e.RowSource _
& ";" & tdfLoop.Name
End If
End If
Next
Set MyDB = Nothing
End Sub
'-------End Form Code

'-------Module Code
Option Compare Database
Option Explicit

Public Sub FixAutoNumber(s trOriginal As String, strNew As String, _
strIDFieldName As String, strBackupIDFiel dName As String)
Dim MyDB As Database
Dim AutoRS As Recordset
Dim NewRS As Recordset
Dim strSQL As String
Dim tdfAuto As TableDef
Dim fldAuto As Field
Dim lngCount As Long
Dim lngI As Long
Dim lngKey As Long
Dim tdf As TableDef
Dim fld As Field
Dim idxAuto As Index
Dim idx As Index
Dim boolFound As Boolean

'Place contents of table called strOriginal into table called
'strNew whenever the new autonumber matches BackupID
Set MyDB = CurrentDb
'Make sure index names and fields match
For Each idxAuto In MyDB.TableDefs( strOriginal).In dexes
If idxAuto.Name <> "PrimaryKey " Then
boolFound = False
For Each fld In idxAuto.Fields
If idxAuto.Name = fld.Name Then
boolFound = True
Exit For
End If
Next fld
If boolFound = False Then
MsgBox ("An index name doesn't match a field name.")
Set MyDB = Nothing
Exit Sub
End If
End If
Next idxAuto
'Delete the new table if it already exists
For Each tdf In MyDB.TableDefs
If tdf.Name = strNew Then
MyDB.Execute "DROP TABLE " & strNew & ";"
Exit For
End If
Next tdf
Set tdf = MyDB.CreateTabl eDef(strNew)
Set tdfAuto = MyDB.TableDefs( strOriginal)
For Each fldAuto In tdfAuto.Fields
If fldAuto.Type = dbText Then
Set fld = tdf.CreateField (fldAuto.Name, dbText, fldAuto.Size)
Else
Set fld = tdf.CreateField (fldAuto.Name, fldAuto.Type)
fld.Attributes = fldAuto.Attribu tes
End If
tdf.Fields.Appe nd fld
Next fldAuto
MyDB.TableDefs. Append tdf
tdf.Fields.Refr esh
For Each idxAuto In MyDB.TableDefs( strOriginal).In dexes
If idxAuto.Name <> "PrimaryKey " Then
Set idx = tdf.CreateIndex (idxAuto.Name)
If idxAuto.Name = strIDFieldName Then idx.Primary = True
If idxAuto.Require d Then idx.Required = True
idx.Fields.Appe nd idx.CreateField (idxAuto.Name)
tdf.Indexes.App end idx
End If
Next idxAuto
tdf.Indexes.Ref resh
DoEvents
strSQL = "SELECT * FROM " & strOriginal & " ORDER BY " _
& strBackupIDFiel dName & ";"
Set AutoRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM " & strNew & ";"
Set NewRS = MyDB.OpenRecord set(strSQL, dbOpenDynaset)
If AutoRS.RecordCo unt > 0 Then
AutoRS.MoveLast
lngCount = AutoRS.RecordCo unt
AutoRS.MoveFirs t
For lngI = 1 To lngCount
lngKey = 0
Do Until lngKey = AutoRS(strBacku pIDFieldName)
NewRS.AddNew
DoEvents
lngKey = NewRS(strIDFiel dName)
Loop
For Each fldAuto In tdfAuto.Fields
If fldAuto.Name <> strIDFieldName Then
NewRS(fldAuto.N ame) = AutoRS(fldAuto. Name)
End If
Next fldAuto
NewRS.Update
If lngI <> lngCount Then AutoRS.MoveNext
Next lngI
End If
AutoRS.Close
Set AutoRS = Nothing
NewRS.Close
Set NewRS = Nothing
Set MyDB = Nothing
End Sub
'-------End Module Code

James A. Fortune

[jimfortune]

rkc wrote:

Tony Toews wrote:

"Tony D'Ambra" <td*****@swiftd sl.com.au> wrote:

The negative side is that the
application stands a much better chance of failing if the AutoNumberedvalues become corrupt.

This was a problem in some versions of Jet 4.0 but SPs fixed those. While I still mostly work in A97 I've never had a problem with Autonumber values

becoming corrupt.
What are the symptoms of a corrupt autonumber field? Do the values
already stored mysteriously change or are duplicates suddenly allowed
in a primary key field?

Or something else?

I haven't had to nuke an autonumber field for about eight months so my
details might be a little sketchy. When that happened it seems that
values of other fields, perhaps indexed, reverted back to previously
stored values. Even values changed directly in the table would revert.
I think the values that showed up were the values associated with the
primary key just before it got corrupted. I don't think the field
index was a problem because recreating the autonumber primary key
solved the problem. IIRC, before the recreation the new autonumber
values were very large. The large autonumber values didn't seem to be
duplicating. Is there a simpler way to handle this?

James A. Fortune

[Steve Jorgensen]

On Wed, 15 Dec 2004 02:51:19 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in
news:Ov******* ************@tw ister.nyroc.rr. com:

Tony Toews wrote:

"Tony D'Ambra" <td*****@swiftd sl.com.au> wrote:

The negative side is that the
applicati on stands a much better chance of failing if the
AutoNumbere d values become corrupt.
This was a problem in some versions of Jet 4.0 but SPs fixed
those. While I still mostly work in A97 I've never had a problem
with Autonumber values becoming corrupt.

What are the symptoms of a corrupt autonumber field? Do the values
already stored mysteriously change or are duplicates suddenly
allowed in a primary key field?

Or something else?

The most common is the "loss of seed value" error, where each new
record gets the same Autonumber, sometimes a previously-used value,
sometimes not.

This was a very common weakness in the first versions of Jet 4, one
that I'd never seen in any previous version of Jet. It was fixed
about SP4 or so, if I'm remembering correctly.

Of course, it wasn't until SP6 that Jet 4 was really stable.

Actually, the seed problem is much improved, but not fixed. In a networked
environment with a large number of simultaneous users, it still happens fairly
regularly in my experience.

[Bas Cost Budde]

Tony Toews wrote:

how do you get a natural key for a person? SSN? No.

Hey! Why not? I am not familiar with SSN but there is a pendant
structure in Holland, the number should be unique. Is SSN not?
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Rick Brandt]

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cp******** **@news2.solcon .nl...

Tony Toews wrote:

how do you get a natural key for a person? SSN? No.

Hey! Why not? I am not familiar with SSN but there is a pendant
structure in Holland, the number should be unique. Is SSN not?

From what I hear "it's supposed to be" would be the answer to your
question. Fraud and screw-ups dictate otherwise.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

Steve Jorgensen <no****@nospam. nospam> wrote in
news:t4******** *************** *********@4ax.c om:

On Wed, 15 Dec 2004 02:51:19 GMT, "David W. Fenton"
<dX********@bw ay.net.invalid> wrote:

rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in
news:Ov****** *************@t wister.nyroc.rr .com:

Tony Toews wrote:
"Tony D'Ambra" <td*****@swiftd sl.com.au> wrote:

>The negative side is that the
>applicatio n stands a much better chance of failing if the
>AutoNumber ed values become corrupt.
This was a problem in some versions of Jet 4.0 but SPs fixed
those. While I still mostly work in A97 I've never had a
problem with Autonumber values becoming corrupt.

What are the symptoms of a corrupt autonumber field? Do the
values already stored mysteriously change or are duplicates
suddenly allowed in a primary key field?

Or something else?

The most common is the "loss of seed value" error, where each new
record gets the same Autonumber, sometimes a previously-used
value, sometimes not.

This was a very common weakness in the first versions of Jet 4,
one that I'd never seen in any previous version of Jet. It was
fixed about SP4 or so, if I'm remembering correctly.

Of course, it wasn't until SP6 that Jet 4 was really stable.

Actually, the seed problem is much improved, but not fixed. In a
networked environment with a large number of simultaneous users,
it still happens fairly regularly in my experience.

I haven't seen it at any of my clients since SP4.

How many simultaneous users are you talking about?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Rick Brandt" <ri*********@ho tmail.com> wrote in
news:32******** *****@individua l.net:

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cp******** **@news2.solcon .nl...

Tony Toews wrote:

> how do you get a natural key for a person? SSN? No.

Hey! Why not? I am not familiar with SSN but there is a pendant
structure in Holland, the number should be unique. Is SSN not?

From what I hear "it's supposed to be" would be the answer to your
question. Fraud and screw-ups dictate otherwise.

Plus there's the huge privacy issue.

Also, some people don't have them.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

Bas Cost Budde <b.*********@he uvelqop.nl> wrote:

how do you get a natural key for a person? SSN? No.

Hey! Why not? I am not familiar with SSN but there is a pendant
structure in Holland, the number should be unique. Is SSN not?

Not unique outside your country. Besides I won't give out my SIN (Cdn equivalent)
to anyone who is not a bank or government. And fraud and identity theft problems.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Bas Cost Budde]

Tony Toews wrote:

Bas Cost Budde <b.*********@he uvelqop.nl> wrote:

how do you get a natural key for a person? SSN? No.

Hey! Why not? I am not familiar with SSN but there is a pendant
structure in Holland, the number should be unique. Is SSN not?

Not unique outside your country. Besides I won't give out my SIN (Cdn equivalent)
to anyone who is not a bank or government. And fraud and identity theft problems.

Yes; you and David point me out I should not restrict myself to
technical thinking :-) I gave just thought to the uniqueness. Well,
thanks anyway.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Steve Jorgensen]

On Wed, 15 Dec 2004 19:43:38 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

Steve Jorgensen <no****@nospam. nospam> wrote in
news:t4******* *************** **********@4ax. com:

On Wed, 15 Dec 2004 02:51:19 GMT, "David W. Fenton"
<dX********@b way.net.invalid > wrote:

rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in
news:Ov***** **************@ twister.nyroc.r r.com:

Tony Toews wrote:
> "Tony D'Ambra" <td*****@swiftd sl.com.au> wrote:

>>The negative side is that the
>>applicati on stands a much better chance of failing if the
>>AutoNumbe red values become corrupt.
>
>
> This was a problem in some versions of Jet 4.0 but SPs fixed
> those. While I still mostly work in A97 I've never had a
> problem with Autonumber values becoming corrupt.

What are the symptoms of a corrupt autonumber field? Do the
values already stored mysteriously change or are duplicates
suddenly allowed in a primary key field?

Or something else?

The most common is the "loss of seed value" error, where each new
record gets the same Autonumber, sometimes a previously-used
value, sometimes not.

This was a very common weakness in the first versions of Jet 4,
one that I'd never seen in any previous version of Jet. It was
fixed about SP4 or so, if I'm remembering correctly.

Of course, it wasn't until SP6 that Jet 4 was really stable.

Actually, the seed problem is much improved, but not fixed. In a
networked environment with a large number of simultaneous users,
it still happens fairly regularly in my experience.

I haven't seen it at any of my clients since SP4.

How many simultaneous users are you talking about?

Well, lots. The place I was working where it was happening routinely had over
100 users hitting the same back-end.

[Trevor Best]

Rick Brandt wrote:

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cp******** **@news2.solcon .nl...

Tony Toews wrote:

how do you get a natural key for a person? SSN? No.

Hey! Why not? I am not familiar with SSN but there is a pendant
structure in Holland, the number should be unique. Is SSN not?

From what I hear "it's supposed to be" would be the answer to your
question. Fraud and screw-ups dictate otherwise.

They also get re-used when someone dies.

--
This sig left intentionally blank