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

 

[Crag]

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.

My apologies. It is not an autonumber in the true sense. It is a text
box (data type - integer) that contains a counter that increments by one
whenever a new record is added. The reason is that when making new
entries a user may input 5 records for one company and these 5 need to
be sequential, not for instance, records 1,2,3 6,8, which could happen
if the database was opened in shared mode. In light of this:

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 your time,
Crag

 

[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