Exclusive mode & user feedback

[Llyllyll]

Good morning,

I have a database that needs to be opened in exclusive mode because of the
way that the autonumbers in the db are allocated. It is is a networked
enviroment with each user having a copy of the front end and the backend
residing on the server. I also have a function that I use to ascertain the
username of the currently logged on user and was wondering:
1. Is there a way I can plug the currently logged on username to into a
form/message box telling the next person who tries to log on (if it is still
opened exclusively by the other one) who that user is.
2. Is it possible to have the database close automatically for the second
user if it is currently is use.

Thanks for all the advice. This NG is improving my databases all the time.
L

 

[Arvin Meyer]

It seems to me that you are using autonumbers for something other than a
blind database key. They are not meant to be anything other than unique, if
the are designated as a unique index or primary key.

That said, you can add an update statement to your username function to
write the value to a single record table. You can guarantee that there will
always only be a single record by assigning the default value of 1 to the
key and building a validation rule that only allows the number 1 in that
field. You must be certain that all users will exit properly and your code
deletes the record upon exit. If not, you will always be going into the back
end to manually delete the record.

The opening form can check for the record in the table and close the
database with a message if it exists. Use a recordset or DLookup to check
for the record.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

I have a better solution for you than opening and closing the database for
other users. Use a local temporary table bound to the form for data entry.
When all the data is complete and verified, use a command button to:

1. Append all the detail records to the real table on the server.
2. If necessary verify the count.
3. Delete the records on the local temp table.

This will lock the table and insert all the records sequentially.

I'm still not sure why you can't have non-sequential records. As long as
their record numbers are in the same order they were entered they'll still
fill the detail section properly. Remember, you do not need to expose the
record numbers.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access