Acesss query help

[kibblerok]

Hi all,

After a bit of help regarding an access query (access 97 )

Have two queries one that retrieves the top row into another table and the second that deletes the row from the main table that was retrieved.

At the moment two queries called by vb code run to do this, however between retrieving and deleting it is possible for someone else to retrieve the same row as multiple users will be using the database.

Any ideas how to do this? in sql view it would appear you cannot combine the two by simply pasting the code and im pretty sure you cant lock rows in access 97 so i dont know what to do.

Cheers for any advice

 

[|Ric|]

I am a little rusty on SQL, but if you deleting a row and you specify its criteria which row it is, can't you just put something to the effect of row_id=(The query that returns the first row)
Where row_id is what ever your keyfield is, I know its not that elegant but it would reduce the time delay.

 

[kibblerok]

thats what im doing, deleting by the work_id of the record retrieved.

unfortunatly because I cant combine the insert into and delete queries into one transaction or query people are still able to retrieve duplicate records if they click between when the two queries are running