Point when autonumber field is updated?

[lgbatdw]

I am using access 2000 and would like to know at what point is a table's autonumber value updated? I ask this because I have a one to many relationship between 2 tables and the autonumber is the link and in a multiuser environment I want to avoid any possibility of having duplicates due to the parent record not being saved timely - for instance user walking away for whatever reason before saving rec.

Linda in MN

 

[sfreeman]

If I understand your question, I do not believe that can happen. Duplicate primary key values are impossible.

In a more common case, you might have two users modifying the same record if no locks, but Access is smart enough not to update the record twice. It will prompt one or both of the users advising of the issue and ask if you want to discard or save the chagnes.

 

[MichaelRed]

While Ms. A. will generate an err if Autonumber is used as a PK, you can have one (Autonumber) which is not a PK. While there are disagreements re the use of Autonumber in a multiuser environment I do not recommend it. While there can be an error, it may also (or alternatively( corrupt one of the records. See faq700-184 for a variation on the theme.





MichaelRed
mlred@verizon.net

 

[lgbatdw]

Thank you everyone for responding to my question with helpful information. It is much appreciated. This is a wonderful forum.
I'm still curious, does access 2000 update (incremented for the nexrt use) the autonumber right after the first character is typed into a new record? (it seems to appear that way) - or when does that occur?

Linda in MN

 

[MichaelRed]

Yes. and, it is clearly stated in the 'docs' see the ubiquitous {F1} (aka HELP). When the (bound form) property Dirty is true, the AutoNumber is generated.




MichaelRed
mlred@verizon.net

 

[willir]

Hmmm
Popular topic today...
[link Recent post]

http://www.tek-tips.com/viewthread.cfm?qid=907570

[/url]

 

[lgbatdw]

THank you MichaelRed - I checked out faq700-184 on "Why AutoNumber shouldn't be used in Multiuser databases". If I understand it correctly the table to generate the "autonumber" should be in a separate database from the backend or frontend, in essence having 3 databases for one application - is that correct? Also, the article mentions that the key is the locking of the table ". . . which is 'locked' by the User until the value has been updated." How exactly do I accomplish the "lock" on the table? I'm using Access 2000. I would appreciate any information, but I am not experienced on doing locking, so please be as specific as you can. Thank-you in advance.

Linda in MN

 

[MichaelRed]

The code snippets / procedures include the locking. It is preferable to have the table in a seperate db, but not actually REQUIRED. What IS necessary is the implementation of Secutity, so the username is unique. The 'string' returned by the procedure is the "Key Field Value", and in the sample is constructed to have a specific pattern using the month and year and a 'seqquence' value. If you need (or just want) a different value, hte code will need to be modified.




MichaelRed
mlred@verizon.net