473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Access DB + ASP + auto-numbering = Q?

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 :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)

Jul 19 '05 #1
8 1805
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
Jul 19 '05 #2

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


"Steven Burn" <nobody@PVT_it-mate.co.uk> schreef in bericht
news:%2***************@TK2MSFTNGP09.phx.gbl...
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 :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)

Jul 19 '05 #3
hehe, I'd not thought of marking them when deleted :o\

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)
Maarten <no****@pandora.be> wrote in message
news:NJ*********************@phobos.telenet-ops.be...
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

Jul 19 '05 #4
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)
Maarten <no****@pandora.be> wrote in message
news:nT********************@phobos.telenet-ops.be...

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


"Steven Burn" <nobody@PVT_it-mate.co.uk> schreef in bericht
news:%2***************@TK2MSFTNGP09.phx.gbl...
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 :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)


Jul 19 '05 #5
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"
Jul 19 '05 #6
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)
Bob Barrows <re******@NOyahoo.SPAMcom> wrote in message
news:#U**************@tk2msftngp13.phx.gbl...
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"

Jul 19 '05 #7
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.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Steven Burn" <nobody@PVT_it-mate.co.uk> wrote in message
news:u7**************@TK2MSFTNGP11.phx.gbl...
Cheers for the reply Bob..... I've actually decided to go with his first
suggestion (marking unused/deleted one's)

Jul 19 '05 #8
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...
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.

Jul 19 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Berae | last post by:
Hello all, Quick question. I have several users using Access 97 and several using Access 2K. The db is written in 97. I want them to exist together peacefully but sometimes the db gets...
0
by: Arno R | last post by:
Hi all I still have clients with apps running on Access 2.0 It would be nice if I could use the Auto FE Utility for 1 of them Anybody tried the Auto FE Utility from Tony Toews with Access 2.0...
0
by: Arno R | last post by:
Hi all I still have clients with apps running on Access 2.0 It would be nice if I could use the Auto FE Utility for 1 of them Anybody tried the Auto FE Utility from Tony Toews with Access 2.0...
3
by: Lauren Quantrell | last post by:
I'm wondering how many different users on different workstations can open the same ade file residing on a server. Is there a limit and how would I trap any errors? Thanks lq
7
by: Rob | last post by:
I am an Access developer and have done many Access databases in the standard Front-end on the workstations, backend on the server (over a LAN) but have never worked with Access over Citrix, though...
26
by: Dragon | last post by:
I have an Access 2003 .mde sitting on an SQL Server. The tables for the application also sit on the Server. I'm having a problem with ODBC on only one of about 10 machines. All the other machines...
5
by: Brian | last post by:
I have a small application with 2 forms where the user logs into the system, then the form recordsource randomly pulls up an account ID. The application is basically an electronic tic sheet and...
8
by: Ralph Fico | last post by:
I have been experiencing a strange corruption problem with an application I developed using MS Access 2000 and deployed using an .MDE file. The application is being used in a networked environment...
22
by: Jordan S. | last post by:
SQL Server will be used as the back-end database to a non trivial client application. In question is the choice of client application: I need to be able to speak intelligently about when one...
3
by: Parthiban s | last post by:
Recently I migrated a MS Access app from mdb data tables to oracle db. It all works fine when connected locally to server but errors out in citrix for only two screens with below error. unable to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.