Opening and locking Access and/ or tables

[imstuck]

I have a table that holds a value (lastidtab)--one record. In my program I have a command button assign the next id number by getting the value in the lastidtab and adding 1. It then updates the the lastidtab with the incremented number. And so on.

The problem I am having is that the user will often have the Access database open more than once on her desktop and it appears that the lastidtab is not being updated properly, because at a later time, when the user needs to assign another id number and then tries to save the data to the table with that id number, she gets a message that says the changes you requested to the table were not successful because it would cause duplicate values in the primary key etc. The ID field is the primary key. The Access database is on our network.

Is there a way either make sure a user can not open the Access database twice or somehow deal with the table not being updated properly?

Thank you! [sig][/sig]

 

[vbaspprogrammer]

Hi imstuck!
If you are using only Microsoft Access and not any other application like Visual Basic to access the database then you have got an option to overcome this problem in the following manner:

Open the Microsoft Access Database
Goto the Tools Menu and choose Options...
Go to the Advanced Tab and tick the checkbox
use record level locks

This overcomes the problem.

Note:This works only all the forms and reports are in Microsoft Access. It will not work if the database is being accessed using Action Quries from Visual Basic etc.

 

[Maquis]

Another idea:

We are using a similiar concept in another language (PICK) at my company. We store "auto ids" in a constant table and pick an id from the table when we need a new id. To avoid possible id conflicts we use this logic.

Id_Ok = 0
Loop until Id_Ok = 1 do
read next idnum from id_table
write idnum+1 to id_table
read rec from update_table with id = idnum
if no rec found then Id_Ok = 1
repeat
Maq B-)
<insert witty signature here>