Auto increment (is this the right code?)

[John J.]

I'm looking for code to increment an ID-number in a multiuser environment.
Autonumber is not an option in this case.

I found this code: http://support.microsoft.com:80/kb/210194. In this code,
however, I don't see what part takes care of the risk that 2 users run the
code at the same time, resulting in duplicate keys.

Is this the right code to use? Or does someone have better code for this?
Thanks, John

 

[a a r o n . k e m p f]

you shouldn't need code to auto-increment.

if your desktop database doesn't fit your needs-- then move to SQL
Server

 

[a a r o n . k e m p f]

_WHY_ aren't autonumbers an option?

because they have gaps?

because you can't set the seed and increment values? that is my big
annoyance with Jet autonumber fields (and that they're not reliable)

 

[BruceM]

Please ignore Aaron. He is monomaniacal at best.

Look for AutonumberProblem at this link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1
It includes a way to handle an incrementing number in a multi-user
environment.

 

[Arvin Meyer [MVP]]

message
_WHY_ aren't autonumbers an option?

because you can't set the seed and increment values? that is my big
annoyance with Jet autonumber fields (and that they're not reliable)
-------------------------------------------------------
Once again there is a deficiency in your knowledge:

INSERT INTO Table1 ( ID )
SELECT 1234 AS Expr1;

Will seed 1234 to the ID autonumber field in Table 1. Obviously, you cannot
seed a lower number than has been already used.

 

[John J.]

Thanks Bruce, but this solution uses the form error event to prevent ID
doubles. I'm looking for code that doesn't need a form.

Will the code in the link I provided do this job?

John

 

[Trek]

I guess the approach in the kb is correct.
You simply lock table in which you maintain counter, then unlock. Value
usage is should be on insert.

In the database server you would have e.g. sequence (or similar table) and
use before insert trigger.

I just do not understand exactly what is you requirement. Maybe you could be
more particular.

 

[John J.]

Thanks,
My requirements are:
- The db controller should be able to easily set the initial start ID-number
(in a separate counter table)
(Therefore autonumber is not an option).
- An ID-number should not be re-used after is has been deleted
(Therefore using Dmax is not an option)

John

 

[Ken Sheridan]

See if this fits the bill:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[a a r o n . k e m p f]

John;

that sounds to me like you should be using SEED and INCREMENT
properties of SQL Server to generate the numbers you want.

-Aaron

 

[a a r o n . k e m p f]

what -ARE- you talking about

If Jet just worked reliably-- predictably-- and logically then maybe
you could do what you want.

-Aaron

 

[a a r o n . k e m p f]

Bruce;

At least I'm _EDUCATED_.

Jet ****ing sucks, anyone using it for anything should be out of a job

-Aaron

 

[Tom Wickerath]

Aaron,

Here is an example DDL query that allows one to set the seed AND increment
values in JET:

Create Table Employees (CustID COUNTER (1000,10), EmpFirstName TEXT(25),
EmpLastName TEXT(25));

FYI - JET works very reliably, as long as one has installed the latest
service pack for JET.
http://support.microsoft.com/kb/239114/


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[BruceM]

The code in the link is run from a form. What is your interface if it is
not a form? Why the objection to using a form? I must have missed
something in your question, but I can't figure out what.

I notice that the linked code uses an ADO recordset, and therefore requires
a reference to that library in all installations using the code. This link
has more information about ADO and DAO recordsets:
http://allenbrowne.com/ser-38.html
No doubt a search will turn up more information. If you are going to use
the code in the link I'm don't see why it needs to be an ADO recordset.
Seems to me DAO should work if you are using the Jet database engine (the
one that ships with Access), but I will have to leave that part of the
question to others who are more knowledgeable about such matters. However,
in any case it uses a form.

 

[Trek]

Hi Aaron,

you can easily reach this also with Jet database.

PS:
In past I almost in each case silently agreed with you, but in the last time
you are blind to requirements people are asking for.
Just distinguish when SQL server is really required.
Otherwise I personally would not also put any critical data to Jet database
in multiuser environment, but if it is some sort of simple app?

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]> píse v
diskusním príspevku
John;

that sounds to me like you should be using SEED and INCREMENT
properties of SQL Server to generate the numbers you want.

-Aaron

 

[John J.]

Bruce,
I'd rather have the whole autoincrement code together in one function in
stead of in different events in a form. That's what I meant. You're right
that I will use the code in a form. Sorry if I wasn't clear. Other aspect is
that I don't want a key to be reused after it is has been deleted so I need
a solution with a counter table.
John

 

[John J.]

Thanks, I'll look into that.

See if this fits the bill:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[BruceM]

The only time a key will be "reused" is if you delete the record with the
highest number. If that could happen then I suppose you will have to use a
counter table. However, in the case of the highest number being deleted I
don't see what difference it makes if another record takes its place, unless
the information in the deleted record has been printed or is otherwise part
of your systems, but in that case I don't see why you deleted it in the
first place.

I have to say it strikes me as a somewhat arbitrary decision that it all
must be in a single function. I frequently create my own functions for
actions that need to be performed in two places such as the After Update
event of a combo box and the Current event of a form. Otherwise I would
need to write the same code twice, and change it in both places if an update
is needed. Access doesn't care if it has to "leave" the original code for a
function call or error handling. If there is a performance difference it is
negligible. In the case of using the form's error event it will occur only
if there is a specific error; otherwise it is ignored. But that's up to
you.

BTW, the default value can be assigned in code as well as by way of a text
box Default Value property.

I am going to retract something I said before. I believe you need to use an
ADO recordset, as the example with the counter table shows, instead of DAO,
because of record locking. The counter table is locked when you run the
code, preventing other users from updating the table. I don't know if this
type of record locking is possible with DAO. This would have to be the
subject of another thread, unless somebody who knows the details of this is
watching this thread. With ADO you will need to assure the library
reference is available, as stated in the code example. I don't know if this
reference would need to be set or checked for each computer using the
program. Again, you may need to start another thread. Check the link about
references to find out more.

 

[John J.]

Thanks for taking the time to comment.
See inline.

don't see what difference it makes if another record takes its place,
unless the information in the deleted record has been printed or is
otherwise part of your systems, but in that case I don't see why you
deleted it in the first place.

Indeed the last thing is the case and there was a flaw in my thinking.

is needed. Access doesn't care if it has to "leave" the original code for
a function call or error handling. If there is a performance difference
it is negligible. In the case of using the form's error event it will
occur only if there is a specific error; otherwise it is ignored. But
that's up to you.

Agree. The reason was just my personal preference that I find the code
easier to maintain if I'd have it is in one place.

I am going to retract something I said before. I believe you need to use
an ADO recordset, as the example with the counter table shows, instead of
DAO, because of record locking. The counter table is locked when you run
the code, preventing other users from updating the table. I don't know if
this type of record locking is possible with DAO. This would have to be
the subject of another thread, unless somebody who knows the details of
this is watching this thread. With ADO you will need to assure the
library reference is available, as stated in the code example. I don't
know if this reference would need to be set or checked for each computer
using the program. Again, you may need to start another thread. Check
the link about references to find out more.

I'll look into that. Thanks again.
John