Maarten wrote:
Second solution:
you don't use an Auto Num
make a new number
RS.Open "SELECT max(fieldNr) FROM myTable"
newNr = RS("fieldNr")+1
1. This will not solve the gap problem. ID's will still not be re-used if
rows are deleted from the "middle" of the table.
2. This will not be reliable in a multi-user situation. Two users creating
records simultaneously will get the same ID.
There are ways to generate unique ID's without using select max(), but the
specifics depend on the database. In general, you use a separate table
containing a row which contains the last-used number. To get a new number,
lock the row (pessimistic locking), read the number into a variable,
increment it, update the row so it contains the new number, and unlock the
row. Your variable contains the new number.
This will still not address the gap problem. If gaps are a problem for some
reason, Then you need to make the ID's re-usable, either by using the "soft"
delete method suggested in Maarten's first reply, or by extending the
suggested method in this reply. The extension involves adding another column
to the generator table to identify rows containing hard-deleted ID's. When a
record is deleted from your data table, add the ID of that record to the
generator table, using the new column to flag it as an ID to re-use. The
idea is to first search this table for a re-usable ID. If re-usable ID's
exist, lock the table, read one of the ID's into a variable, delete that
record, and unlock the table. If no re-usable ID's exist, use the method in
the previous paragraph to generate a new ID.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"