Access DB + ASP + auto-numbering = Q?

S

Steven Burn

I'm curious, I've written a very simple PIS (personal info store) that
allows one to store whatever they wish..... the problem is, when one deletes
an entry, the auto-numbering isn't corrected to account for said
deletion..... for example;

1. entry 1
2. entry 2
3. entry 3

delete entry 2, and #2 is no longer available (i.e. 3 should become 2, 4,
become 3 etc etc).....

Anyone know of a way to tell Access to re-number them when an entry is
deleted?

Apologies if this makes absolutely no sense.... I've been awake for hours
and am absolutely shattered :eek:\

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Disclaimer:
I know I'm probably wrong, I just like taking part ;o)
 
M

Maarten

A auto num is to indentify a record in a unique way. Whats matter is this 1
or 10 or 23678

To solve your problem: mark the deleted record(s) and reused when adding a
new one

fieldIs = "1892627" the autonum
fieldActive = "Y"
fieldNr = "4"
FieldMemo = whatever

after delete

fieldIs = "1892627" the autonum
fieldActive = "N"
fieldNr = "4"
FieldMemo = whatever

SELECT TOP 1 FROM MyTable WHERE fieldActive="N" ORDER by fieldNr
 
S

Steven Burn

hehe, I'd not thought of marking them when deleted :eek:\

Cheers ;o)

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Disclaimer:
I know I'm probably wrong, I just like taking part ;o)
 
M

Maarten

Second solution:
you don't use an Auto Num

make a new number
RS.Open "SELECT max(fieldNr) FROM myTable"
newNr = RS("fieldNr")+1



fieldNr = "1"
FieldMemo = whatever

fieldNr = "2"
FieldMemo = whatever

fieldNr = "3"
FieldMemo = whatever


You delete the second record: then


RS.OPEN "SELECT * FROM MyTable ORDER BY fieldNr"

ct=0

DO WHILE NOT RS.eof
ct=ct+1
RS("fieldNr") = ct
RS.MoveNext
Loop

RS.Close
 
S

Steven Burn

Maarten,
Thats pretty much the way I've done it before....... just
figured I'd use AN this time to save a little time (and to keep it as simple
as possible as the PIS is for a friend, not for myself).

cheers for the suggestion though ;o)

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Disclaimer:
I know I'm probably wrong, I just like taking part ;o)
 
B

Bob Barrows

Maarten said:
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
 
S

Steven Burn

Cheers for the reply Bob..... I've actually decided to go with his first
suggestion (marking unused/deleted one's)

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Disclaimer:
I know I'm probably wrong, I just like taking part ;o)
 
A

Aaron Bertrand [MVP]

Now, can you explain why you care about gaps? If you want a seamless range
of numbers for display purposes, then clearly the ID number doesn't matter,
and you can generate a "ranking" during a SELECT.
 
E

Egbert Nierop \(MVP for IIS\)

Aaron Bertrand said:
Now, can you explain why you care about gaps? If you want a seamless range
of numbers for display purposes, then clearly the ID number doesn't matter,
and you can generate a "ranking" during a SELECT.

That's right and according to the rules of DB design, a primary key should
never change.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

[OT] Server 2003 + Access 23
Replace Function 6
Quick Q regarding querystrings 10
[Perhaps OT] Zip 2
Block "HEAD" method? 2
Just wondering 11
Can you help? 14
IIS 4

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top