General discussion

Locked

Auto-number in Access 2000 (multi-user)

By CristaBerry ·
I am building a db in Access 2000 to house records of incoming support calls. DB will support a max of 15 users. I need to generate an auto-number as a unique identifier for each record, & be able to pull up record to edit using the auto-number later. All records will be housed in a single table.

If multiple users are adding records to table at the same time, how I can ensure a unique auto-number for each record? Can number be assigned as primary key to auto-find when entered in edit form?I have done this kind of find/edit via a form with user-assigned identifying numbers, but need to auto-number in this instance.

I have moderate Access experience, but no access to other development tools & limited testing capabilities. Any advice?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Auto-number in Access 2000 (multi-user)

by DKlippert In reply to Auto-number in Access 200 ...

Add the Identifier field.Choose Data Type AutoNumber. It will be unique.
Yes it can be used as the primary key. The matching field will need to be Long Integer to set up the relationship.

Collapse -

Auto-number in Access 2000 (multi-user)

by CristaBerry In reply to Auto-number in Access 200 ...

The question was auto-closed by TechRepublic

Collapse -

Auto-number in Access 2000 (multi-user)

by CristaBerry In reply to Auto-number in Access 200 ...

Followup question... Got the auto number working. How can pull up a record in a Form to edit by entering the auto number?

Collapse -

Auto-number in Access 2000 (multi-user)

by Marty R. Milette In reply to Auto-number in Access 200 ...

The problem with the auto-number option is that if two users simultaneously try to add a record, the autonumber field will assign the same number. Whoever closes (saves) the record first wins. The other will get a 'duplicate key' error. A contributing factor is because the default is for 'optimistic' locking. Using 'pessimistic' locking may help, but may also prevent reasonable access as well. (You'd have to play with it).

(Access never really was designed as a multi-user database -- and you're gonna have other problems with performance later. The nice thing is that it's pretty easy to 'upsize' to SQL server when that happens..)

What WILL work is to create a stored procedure (action query) to access another table that has basically just a single row, with a unique identifier that you create and increment yourself.

When you open the new record, the stored procedure would be called to quickly generate and grab the next unique identifier from the other table. Since the process occurs very quickly, the chances of conflicts is much reduced.

If you need more help, just let me know! Regards, Marty...

Collapse -

Auto-number in Access 2000 (multi-user)

by CristaBerry In reply to Auto-number in Access 200 ...

The question was auto-closed by TechRepublic

Collapse -

Auto-number in Access 2000 (multi-user)

by CristaBerry In reply to Auto-number in Access 200 ...

This question was auto closed due to inactivity

Back to Desktop Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums