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 cbxDatabaseTable_AfterUpdate()
Dim MyDB As Database
Dim tdf As TableDef
Dim fld As Field

If IsNull(cbxDatabaseTable.Value) Then
cbxIDFieldName.RowSource = ""
cbxBackupIDFieldName.RowSource = ""
cbxIDFieldName.Value = Null
cbxBackupIDFieldName.Value = Null
Exit Sub
End If
'Put the field names in cbxIDFieldName and cbxBackupIDFieldName
Set MyDB = CurrentDb
cbxIDFieldName.RowSourceType = "Value List"
cbxBackupIDFieldName.RowSourceType = "Value List"
For Each fld In MyDB.TableDefs(cbxDatabaseTable.Value).Fields
If Nz(cbxIDFieldName.RowSource, "") = "" Then
cbxIDFieldName.RowSource = fld.Name
Else
cbxIDFieldName.RowSource = cbxIDFieldName.RowSource _
& ";" & fld.Name
End If
If Nz(cbxBackupIDFieldName.RowSource, "") = "" Then
cbxBackupIDFieldName.RowSource = fld.Name
Else
cbxBackupIDFieldName.RowSource = cbxBackupIDFieldName.RowSource _
& ";" & fld.Name
End If
Next fld
Set MyDB = Nothing
End Sub

Private Sub cmdFixAutonumber_Click()
If IsNull(cbxDatabaseTable.Value) Then
MsgBox ("No table was selected.")
Exit Sub
End If
If IsNull(txtNewTableName.Value) Then
MsgBox ("No new table name was selected.")
Exit Sub
End If
If IsNull(cbxIDFieldName.Value) Then
MsgBox ("No ID Field was selected.")
Exit Sub
End If
If IsNull(cbxBackupIDFieldName.Value) Then
MsgBox ("No Backup ID Field was selected.")
Exit Sub
End If
Call FixAutoNumber(cbxDatabaseTable.Value, txtNewTableName.Value, _
cbxIDFieldName.Value, cbxBackupIDFieldName.Value)
MsgBox ("Done.")
End Sub

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

Set MyDB = CurrentDb
cbxDatabaseTable.RowSourceType = "Value List"
For Each tdfLoop In MyDB.TableDefs
If Left(tdfLoop.Name, 4) <> "MSys" Then
If Nz(cbxDatabaseTable.RowSource, "") = "" Then
cbxDatabaseTable.RowSource = tdfLoop.Name
Else
cbxDatabaseTable.RowSource = cbxDatabaseTable.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(strOriginal As String, strNew As String, _
strIDFieldName As String, strBackupIDFieldName 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).Indexes
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.CreateTableDef(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.Attributes
End If
tdf.Fields.Append fld
Next fldAuto
MyDB.TableDefs.Append tdf
tdf.Fields.Refresh
For Each idxAuto In MyDB.TableDefs(strOriginal).Indexes
If idxAuto.Name <> "PrimaryKey" Then
Set idx = tdf.CreateIndex(idxAuto.Name)
If idxAuto.Name = strIDFieldName Then idx.Primary = True
If idxAuto.Required Then idx.Required = True
idx.Fields.Append idx.CreateField(idxAuto.Name)
tdf.Indexes.Append idx
End If
Next idxAuto
tdf.Indexes.Refresh
DoEvents
strSQL = "SELECT * FROM " & strOriginal & " ORDER BY " _
& strBackupIDFieldName & ";"
Set AutoRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM " & strNew & ";"
Set NewRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If AutoRS.RecordCount > 0 Then
AutoRS.MoveLast
lngCount = AutoRS.RecordCount
AutoRS.MoveFirst
For lngI = 1 To lngCount
lngKey = 0
Do Until lngKey = AutoRS(strBackupIDFieldName)
NewRS.AddNew
DoEvents
lngKey = NewRS(strIDFieldName)
Loop
For Each fldAuto In tdfAuto.Fields
If fldAuto.Name <> strIDFieldName Then
NewRS(fldAuto.Name) = 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

Nov 13 '05 #1

Tony D'Ambra

The issue of using AutoNumber keys in joins is problematic for the reasons
outlined. You should use hardcoded id's for joins to avoid the issue
altogether...
Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

<ji********@compumarc.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

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 cbxDatabaseTable_AfterUpdate()
Dim MyDB As Database
Dim tdf As TableDef
Dim fld As Field

If IsNull(cbxDatabaseTable.Value) Then
cbxIDFieldName.RowSource = ""
cbxBackupIDFieldName.RowSource = ""
cbxIDFieldName.Value = Null
cbxBackupIDFieldName.Value = Null
Exit Sub
End If
'Put the field names in cbxIDFieldName and cbxBackupIDFieldName
Set MyDB = CurrentDb
cbxIDFieldName.RowSourceType = "Value List"
cbxBackupIDFieldName.RowSourceType = "Value List"
For Each fld In MyDB.TableDefs(cbxDatabaseTable.Value).Fields
If Nz(cbxIDFieldName.RowSource, "") = "" Then
cbxIDFieldName.RowSource = fld.Name
Else
cbxIDFieldName.RowSource = cbxIDFieldName.RowSource _
& ";" & fld.Name
End If
If Nz(cbxBackupIDFieldName.RowSource, "") = "" Then
cbxBackupIDFieldName.RowSource = fld.Name
Else
cbxBackupIDFieldName.RowSource = cbxBackupIDFieldName.RowSource _
& ";" & fld.Name
End If
Next fld
Set MyDB = Nothing
End Sub

Private Sub cmdFixAutonumber_Click()
If IsNull(cbxDatabaseTable.Value) Then
MsgBox ("No table was selected.")
Exit Sub
End If
If IsNull(txtNewTableName.Value) Then
MsgBox ("No new table name was selected.")
Exit Sub
End If
If IsNull(cbxIDFieldName.Value) Then
MsgBox ("No ID Field was selected.")
Exit Sub
End If
If IsNull(cbxBackupIDFieldName.Value) Then
MsgBox ("No Backup ID Field was selected.")
Exit Sub
End If
Call FixAutoNumber(cbxDatabaseTable.Value, txtNewTableName.Value, _
cbxIDFieldName.Value, cbxBackupIDFieldName.Value)
MsgBox ("Done.")
End Sub

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

Set MyDB = CurrentDb
cbxDatabaseTable.RowSourceType = "Value List"
For Each tdfLoop In MyDB.TableDefs
If Left(tdfLoop.Name, 4) <> "MSys" Then
If Nz(cbxDatabaseTable.RowSource, "") = "" Then
cbxDatabaseTable.RowSource = tdfLoop.Name
Else
cbxDatabaseTable.RowSource = cbxDatabaseTable.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(strOriginal As String, strNew As String, _
strIDFieldName As String, strBackupIDFieldName 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).Indexes
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.CreateTableDef(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.Attributes
End If
tdf.Fields.Append fld
Next fldAuto
MyDB.TableDefs.Append tdf
tdf.Fields.Refresh
For Each idxAuto In MyDB.TableDefs(strOriginal).Indexes
If idxAuto.Name <> "PrimaryKey" Then
Set idx = tdf.CreateIndex(idxAuto.Name)
If idxAuto.Name = strIDFieldName Then idx.Primary = True
If idxAuto.Required Then idx.Required = True
idx.Fields.Append idx.CreateField(idxAuto.Name)
tdf.Indexes.Append idx
End If
Next idxAuto
tdf.Indexes.Refresh
DoEvents
strSQL = "SELECT * FROM " & strOriginal & " ORDER BY " _
& strBackupIDFieldName & ";"
Set AutoRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM " & strNew & ";"
Set NewRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If AutoRS.RecordCount > 0 Then
AutoRS.MoveLast
lngCount = AutoRS.RecordCount
AutoRS.MoveFirst
For lngI = 1 To lngCount
lngKey = 0
Do Until lngKey = AutoRS(strBackupIDFieldName)
NewRS.AddNew
DoEvents
lngKey = NewRS(strIDFieldName)
Loop
For Each fldAuto In tdfAuto.Fields
If fldAuto.Name <> strIDFieldName Then
NewRS(fldAuto.Name) = 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

Nov 13 '05 #2

Tony Toews

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

The issue of using AutoNumber keys in joins is problematic for the reasons
outlined. You should use hardcoded id's for joins to avoid the issue
altogether...

Eh? I've been using autonumber primary keys in all my systems since the first one I
created in A2.0 using natural keys. Thus the autonumber keys are present in all the
joins. Or am I misunderstanding something?

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

Nov 13 '05 #3

david epsom dot com dot au

In A97 you can APPEND a record to an autonumber table/field,
to put any unused value into the field.

In later versions you can ALSO change the table permissions
to allow you to edit autonumbers.

In both A97 and later versions you can APPEND a value to
an autonumber table/field in SQL Server, but it is a two-step
process in A97: later versions have /different/ problems
with SQL Server.

(david)

<ji********@compumarc.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

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 cbxDatabaseTable_AfterUpdate()
Dim MyDB As Database
Dim tdf As TableDef
Dim fld As Field

If IsNull(cbxDatabaseTable.Value) Then
cbxIDFieldName.RowSource = ""
cbxBackupIDFieldName.RowSource = ""
cbxIDFieldName.Value = Null
cbxBackupIDFieldName.Value = Null
Exit Sub
End If
'Put the field names in cbxIDFieldName and cbxBackupIDFieldName
Set MyDB = CurrentDb
cbxIDFieldName.RowSourceType = "Value List"
cbxBackupIDFieldName.RowSourceType = "Value List"
For Each fld In MyDB.TableDefs(cbxDatabaseTable.Value).Fields
If Nz(cbxIDFieldName.RowSource, "") = "" Then
cbxIDFieldName.RowSource = fld.Name
Else
cbxIDFieldName.RowSource = cbxIDFieldName.RowSource _
& ";" & fld.Name
End If
If Nz(cbxBackupIDFieldName.RowSource, "") = "" Then
cbxBackupIDFieldName.RowSource = fld.Name
Else
cbxBackupIDFieldName.RowSource = cbxBackupIDFieldName.RowSource _
& ";" & fld.Name
End If
Next fld
Set MyDB = Nothing
End Sub

Private Sub cmdFixAutonumber_Click()
If IsNull(cbxDatabaseTable.Value) Then
MsgBox ("No table was selected.")
Exit Sub
End If
If IsNull(txtNewTableName.Value) Then
MsgBox ("No new table name was selected.")
Exit Sub
End If
If IsNull(cbxIDFieldName.Value) Then
MsgBox ("No ID Field was selected.")
Exit Sub
End If
If IsNull(cbxBackupIDFieldName.Value) Then
MsgBox ("No Backup ID Field was selected.")
Exit Sub
End If
Call FixAutoNumber(cbxDatabaseTable.Value, txtNewTableName.Value, _
cbxIDFieldName.Value, cbxBackupIDFieldName.Value)
MsgBox ("Done.")
End Sub

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

Set MyDB = CurrentDb
cbxDatabaseTable.RowSourceType = "Value List"
For Each tdfLoop In MyDB.TableDefs
If Left(tdfLoop.Name, 4) <> "MSys" Then
If Nz(cbxDatabaseTable.RowSource, "") = "" Then
cbxDatabaseTable.RowSource = tdfLoop.Name
Else
cbxDatabaseTable.RowSource = cbxDatabaseTable.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(strOriginal As String, strNew As String, _
strIDFieldName As String, strBackupIDFieldName 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).Indexes
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.CreateTableDef(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.Attributes
End If
tdf.Fields.Append fld
Next fldAuto
MyDB.TableDefs.Append tdf
tdf.Fields.Refresh
For Each idxAuto In MyDB.TableDefs(strOriginal).Indexes
If idxAuto.Name <> "PrimaryKey" Then
Set idx = tdf.CreateIndex(idxAuto.Name)
If idxAuto.Name = strIDFieldName Then idx.Primary = True
If idxAuto.Required Then idx.Required = True
idx.Fields.Append idx.CreateField(idxAuto.Name)
tdf.Indexes.Append idx
End If
Next idxAuto
tdf.Indexes.Refresh
DoEvents
strSQL = "SELECT * FROM " & strOriginal & " ORDER BY " _
& strBackupIDFieldName & ";"
Set AutoRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM " & strNew & ";"
Set NewRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If AutoRS.RecordCount > 0 Then
AutoRS.MoveLast
lngCount = AutoRS.RecordCount
AutoRS.MoveFirst
For lngI = 1 To lngCount
lngKey = 0
Do Until lngKey = AutoRS(strBackupIDFieldName)
NewRS.AddNew
DoEvents
lngKey = NewRS(strIDFieldName)
Loop
For Each fldAuto In tdfAuto.Fields
If fldAuto.Name <> strIDFieldName Then
NewRS(fldAuto.Name) = 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

Nov 13 '05 #4

jimfortune

david epsom dot com dot au wrote:

In A97 you can APPEND a record to an autonumber table/field,
to put any unused value into the field.

In later versions you can ALSO change the table permissions
to allow you to edit autonumbers.

In both A97 and later versions you can APPEND a value to
an autonumber table/field in SQL Server, but it is a two-step
process in A97: later versions have /different/ problems
with SQL Server.

(david)

Thanks for the excellent information. I learned a lot about how
indexes are implemented in Access from prior posts so writing the code
was not a total waste. The other post about rolling your own key, such
as MAX + 1, is also worthy of consideration in a case like mine where
collisions are rare. I'm simply trying to get the best of both worlds.

James A. Fortune
Access numerical data to pdf histogram:
http://www.oakland.edu/~fortune/Histogram.zip

Nov 13 '05 #5

jimfortune

> Access numerical data to pdf histogram:

http://www.oakland.edu/~fortune/Histogram.zip

I had an experimental version there. I will replace it tonight with
the correct one.

James A. Fortune

Nov 13 '05 #6

Tony D'Ambra

Firstly, I find your use of "Eh?" offensive. Your MVP status does not exempt
you from the rules of simple courtesy...

By hard-coded joins I mean joins made using a custom ID field: look at the
sample Northwinds .mdb join: Cutomers-Orders, which uses a custom ID field,
CustomerID, which is not an autonumber field.

Also, the following article excerpt is relevant:
Teach your Access users to be wary of AutoNumbered primary keys

Feb 5, 2002
Peter Nelson
© 2002 TechRepublic, Inc.

As an active Microsoft Access developer, I've got a good view of one side of
the database development environment. But as an active Microsoft Access
instructor, I also get to see what the rest of the nontechnical developer
world is doing with Access. It's this latter view of Access that has
revealed how common it is for nontechnical Access users to incorrectly use
an AutoNumber field type as a table's primary key.

Here is why it's so important to steer end users away from this dangerous
practice.

Ignorance is rarely bliss when it comes to Access
Most business users, or even junior developers, do not have an in-depth
understanding as to what a primary key is, let alone the ramifications of an
ill-conceived primary key selection.

When a user saves a new table in Access, a dialog box pops up asking if the
user wants to create a primary key if one doesn't exist. The message says,
"Although a primary key isn't required, it's highly recommended. A table
must have a primary key for you to create a relationship between this table
and other tables in the database. Do you want to create a primary key now?"

Many of the users I've talked to in introductory, intermediate, and
sometimes even advanced Access classes usually say, "I saw the message and I
figured, what the heck, seems like a good thing to do." So, while they are
actually taking the right step in creating a primary key, they don't know
how to correctly complete the task.

A simple plan
Poorly designed applications are usually more cumbersome and costly to
maintain than those with the benefit of good initial planning.

Well-designed databases are generally characterized by a group of tables
storing related data that is joined together through keys. For example, a
database that stores information on customers and their related orders would
likely have two tables: Customers and Orders. The Orders table would not
contain any information about an order's related customer (address, phone
number, and so forth). Instead, it would contain the key that identifies the
row containing the customer's information in the Customers table.

AutoNumber: A double-edged sword
When choosing a key, it's generally a bad idea to choose a field that can be
edited by a user. Doing so forces you either to restrict the user from
editing the field after the record's creation or to provide a way of
detecting and correcting key collisions. If you restrict the user from
editing the field, you may discover that your application isn't flexible
enough. The second choice is problematic as well; providing for the
detection and correction of key collisions can be too complicated, seriously
hindering your application's performance.

The AutoNumber datatype offers a handy solution to this problem but not
without making your application more vulnerable to failure. On the positive
side, using the AutoNumber datatype provides a field that will give you a
unique value for every record, while also paving the way for establishing
relationships between multiple tables. The negative side is that the
application stands a much better chance of failing if the AutoNumbered
values become corrupt.

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:uo********************************@4ax.com...

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

The issue of using AutoNumber keys in joins is problematic for the reasons
outlined. You should use hardcoded id's for joins to avoid the issue
altogether...

Eh? I've been using autonumber primary keys in all my systems since the
first one I
created in A2.0 using natural keys. Thus the autonumber keys are present
in all the
joins. Or am I misunderstanding something?

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

Nov 13 '05 #7

Tony Toews

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

Firstly, I find your use of "Eh?" offensive. Your MVP status does not exempt
you from the rules of simple courtesy...
Sorry you found usage of that offensive. I did not realize that it was discourteous.
By hard-coded joins I mean joins made using a custom ID field: look at the
sample Northwinds .mdb join: Cutomers-Orders, which uses a custom ID field,
CustomerID, which is not an autonumber field.
Yup, I see what you mean. And as an example that's adequate. But what about the
OrderID field on the order table?

As far as I'm concerned Northwinds should have two versions. One with autonumber
primary keys and one with natural keys.
Also, the following article excerpt is relevant:
Teach your Access users to be wary of AutoNumbered primary keys
He's wrong. Autonumber keys work and work well. Finding a natural key and coding
it can be a lot of work. For example how do you get a natural key for a person?
Name? No. Name and birthdate? No. SSN? No. Name and address? Name and phone
number?

How do you handle the situation where you have invoice line items? Now you need code
to create the multipart key, ie invoice #, line 0001, line 0002, line 0003 and so on.
The negative side is that the
application stands a much better chance of failing if the AutoNumbered
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.

Also the Access GUI simply doesn't handle multi part keys well. Although Jet seems
to reasonably well.

That said this can become a religuous argument. Tom Ellison has argued very
eloquently for the user of natural keys. But I still disagree and am exceedingly
happy with autonumber keys.

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

Nov 13 '05 #8

rkc

Tony Toews wrote:

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

The negative side is that the
application stands a much better chance of failing if the AutoNumbered
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?

Nov 13 '05 #9

David W. Fenton

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

Tony Toews wrote:

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

The negative side is that the
application stands a much better chance of failing if the
AutoNumbered 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.

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

Nov 13 '05 #10

jimfortune

rkc wrote:

Tony Toews wrote:

"Tony D'Ambra" <td*****@swiftdsl.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

Nov 13 '05 #11

Steve Jorgensen

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

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

Tony Toews wrote:

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

The negative side is that the
application stands a much better chance of failing if the
AutoNumbered 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.

Nov 13 '05 #12

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

Nov 13 '05 #13

Rick Brandt

"Bas Cost Budde" <b.*********@heuvelqop.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

Nov 13 '05 #14

David W. Fenton

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

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

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

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

>The negative side is that the
>application stands a much better chance of failing if the
>AutoNumbered 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

Nov 13 '05 #15

David W. Fenton

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:32*************@individual.net:

"Bas Cost Budde" <b.*********@heuvelqop.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

Nov 13 '05 #16

Tony Toews

Bas Cost Budde <b.*********@heuvelqop.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

Nov 13 '05 #17

Bas Cost Budde

Tony Toews wrote:

Bas Cost Budde <b.*********@heuvelqop.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

Nov 13 '05 #18

Steve Jorgensen

On Wed, 15 Dec 2004 19:43:38 GMT, "David W. Fenton"
<dX********@bway.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********@bway.net.invalid> wrote:

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

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

>>The negative side is that the
>>application stands a much better chance of failing if the
>>AutoNumbered 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.

Nov 13 '05 #19

Trevor Best

Rick Brandt wrote:

"Bas Cost Budde" <b.*********@heuvelqop.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

Nov 13 '05 #20

David W. Fenton

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

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

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

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

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

> Tony Toews wrote:
>> "Tony D'Ambra" <td*****@swiftdsl.com.au> wrote:
>
>>>The negative side is that the
>>>application stands a much better chance of failing if the
>>>AutoNumbered 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.

Heavens! I've never had anything that large, and wouldn't be very
comfortable with it at all!

Well, that's good to know as another reason to try to convince
clients to move to a server back end when they get into the 50+
users territory.

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

Nov 13 '05 #21

david epsom dot com dot au

> They also get re-used when someone dies.

Not deliberately. The location of the SSN FAQ has changed:
it is now here:

http://archive.cpsr.org/cpsr/privacy/ssn/ssn.faq.html

Most of the links in that document are broken. The discussion
of problems with the use of SSN's as primary keys is here:

http://archive.cpsr.org/cpsr/privacy...-addendum.html

(david)

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41**********************@news.zen.co.uk...

Rick Brandt wrote:

"Bas Cost Budde" <b.*********@heuvelqop.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

Nov 13 '05 #22

Larry Linson

"Tony D'Ambra" wrote

Firstly, I find your use of "Eh?" offensive.
Your MVP status does not exempt
you from the rules of simple courtesy...

It's a Canadian speech habit thing that carries over into writing... not
intended to be offensive nor discourteous. Perhaps it has some different
meaning or usage in Oz?

Nov 13 '05 #23

Larry Linson

"Steve Jorgensen" wrote

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

Ah, that is near-record territory. Consider yourself added to the "people
whose observations we can trust" when we talk about the upper limits of
multiuser. Hey, it's good company, including michka, Stephen Forte, Drew
Wutka, and Mike Groh...

Like David, I don't have personal experience with that size user audience.
But that is quite a lot of users, and likely to stress a poor little
"desktop database" somewhat. But, as David also said, it'd be worth a check
that all users are up-to-date with SPs for Jet 4. Wouldn't it be nice if
that were a solvable problem, and you solved it for your client?

Larry Linson
Microsoft Access MVP

Nov 13 '05 #24

rkc

Larry Linson wrote:

"Tony D'Ambra" wrote

> Firstly, I find your use of "Eh?" offensive.
> Your MVP status does not exempt
> you from the rules of simple courtesy...

It's a Canadian speech habit thing that carries over into writing... not
intended to be offensive nor discourteous. Perhaps it has some different
meaning or usage in Oz?

Interesting spin. In my experience "Eh?" is usually tacked on to
the end of a sentence by afore referenced Canadians.

Nov 13 '05 #25

Larry Linson

"rkc" wrote

Interesting spin. In my experience "Eh?" is usually tacked on to
the end of a sentence by afore referenced Canadians.

That, too. I took Tony's use to be more-or-less equivalent to my saying
something like: "Interesting. My own observation is...".

Larry Linson

Nov 13 '05 #26

Trevor Best

Larry Linson wrote:

"rkc" wrote

Interesting spin. In my experience "Eh?" is usually tacked on to
the end of a sentence by afore referenced Canadians.

That, too. I took Tony's use to be more-or-less equivalent to my saying
something like: "Interesting. My own observation is...".

Bejeezus, that's what I thought :-)

--
This sig left intentionally blank

Nov 13 '05 #27