Exclusive use notification

[B. Meincke]

We have an attendance database that all of our teaching staff uses. Those who
access it via a mapped drive can see the lock file when the database is in
use and understand to leave it alone. Those who do not have the mapping
access it via a desktop shortcut and do not see the contents of the folder,
and there is the rub, so to speak...

Now I realize that what I am asking is not the ideal solution, but it fits
with my learning curve at the moment. If there are any more intelligent
solutions, I would be open to hearing them!

Here goes,
Is there any way I can code an autoexec-type form that would check when the
database is opened to see if the user opening it has exclusive use, and if it
is currently open by another user, that second user gets a textbox message
alerting them to try again later?

Thanks for both your patience with my inexperience and any advice or
suggestions offered.

 

[Marshall Barton]

We have an attendance database that all of our teaching staff uses. Those who
access it via a mapped drive can see the lock file when the database is in
use and understand to leave it alone. Those who do not have the mapping
access it via a desktop shortcut and do not see the contents of the folder,
and there is the rub, so to speak...

Now I realize that what I am asking is not the ideal solution, but it fits
with my learning curve at the moment. If there are any more intelligent
solutions, I would be open to hearing them!

Here goes,
Is there any way I can code an autoexec-type form that would check when the
database is opened to see if the user opening it has exclusive use, and if it
is currently open by another user, that second user gets a textbox message
alerting them to try again later?

Thanks for both your patience with my inexperience and any advice or
suggestions offered.

You could use the Dir function in your unbound startup form
(before opening any bound forms) to check if the LDB file
exists. BUT that sounds kind of complicated.

OTOH, if you require every user to have exclusive use of the
database (WHY??), then you can set the Default Open Mode
option to Exclusive (in Tools - Options - Advanced).

 

[B. Meincke]

Thank you, Marshall, for your prompt reply.

I know, in theory, more than one person ought to be able to enter data into
an Access 2000 database at the same time without incident, but we have found
that this is not always true. We have seen instances of one person's data
being recorded and not the other. "Should'a" isn't much of a consolation to
the teacher who has spent the past half an hour entering the week's
attendance records!

Would you be so kind as to expand on your first suggestion? I understand
what you are proposing but I am not sure about how to implement it.

Again, thanks.

 

[Douglas J. Steele]

Is your application split into a front-end (containing the queries, forms,
reports, macros and modules), linked to a back-end (containing the tables
and relations)? Only the back-end should be on the server: each user should
have his/her own copy of the front-end, ideally on his/her hard drive.

 

[Marshall Barton]

Thank you, Marshall, for your prompt reply.

I know, in theory, more than one person ought to be able to enter data into
an Access 2000 database at the same time without incident, but we have found
that this is not always true. We have seen instances of one person's data
being recorded and not the other. "Should'a" isn't much of a consolation to
the teacher who has spent the past half an hour entering the week's
attendance records!

Would you be so kind as to expand on your first suggestion? I understand
what you are proposing but I am not sure about how to implement it.

I suppose you could try using code like this in the unbound
startup form's Open event:

If Dir(CurrentProject.Path & "database name.LDB") <> "" _
Then
Me.textbox = "database is in use"
Exit sub
Else
'it is ok. do normal processing
End If

What don't you like about having the database default to
Exclusive mode?

If you are having trouble with multiple users, then I
suspect that the database is not split and/or the users are
trying to use a single copy on a server. Both of which are
known to cause all kinds of problems.

 

[B. Meincke]

I suppose you could try using code like this in the unbound
startup form's Open event:

If Dir(CurrentProject.Path & "database name.LDB") <> "" _
Then
Me.textbox = "database is in use"
Exit sub
Else
'it is ok. do normal processing
End If

What don't you like about having the database default to
Exclusive mode?

If you are having trouble with multiple users, then I
suspect that the database is not split and/or the users are
trying to use a single copy on a server. Both of which are
known to cause all kinds of problems.

Marshall/Douglas
Thank you both for your prompt replies. I can't tell you how much help this
forum has been to me over the past four years...

And, yes. Guilty as charged. Our database is a single, server-side file. I'm
afraid we had no idea how the thing would grow when we first began developing
it. I know to split it is better, but I can't make such a big change until
July, when no one is using it. The other issue is that teachers don't use the
same workstation all the time, so I would have to pretty much install the
front end on every workstation in the domain...definitely a summer break job!

Until that time, however, I can see that your syntax, with a bit of
tweaking, will do what we need to do to alert users when the database is in
use.

 

[James Gaylord via AccessMonster.com]

I suppose you could try using code like this in the unbound
startup form's Open event:

[quoted text clipped - 14 lines]

trying to use a single copy on a server. Both of which are
known to cause all kinds of problems.

Marshall/Douglas
Thank you both for your prompt replies. I can't tell you how much help this
forum has been to me over the past four years...

And, yes. Guilty as charged. Our database is a single, server-side file. I'm
afraid we had no idea how the thing would grow when we first began developing
it. I know to split it is better, but I can't make such a big change until
July, when no one is using it. The other issue is that teachers don't use the
same workstation all the time, so I would have to pretty much install the
front end on every workstation in the domain...definitely a summer break job!

Until that time, however, I can see that your syntax, with a bit of
tweaking, will do what we need to do to alert users when the database is in
use.

There are programs on the net that allows you to place a shortcut on each
users desk. This then checks a central location for an updated front end
before opening the current one. If there is one it copies that down to the
local machine and then opens it. I have found that this is extremely helpful
in my development of the database. You don't have to go from machine to
machine each time you make a front-end change. I know this doesn't help with
your current issue but I thought I would throw is out there for when you are
ready to redeploy in September.

--
James B Gaylord
From the Wolf Comes the Strength of the Pack
From the Pack Comes the Strength of the Wolf
- R Kipling

Message posted via AccessMonster.com

 

[Douglas J. Steele]

There are programs on the net that allows you to place a shortcut on each
users desk. This then checks a central location for an updated front end
before opening the current one. If there is one it copies that down to the
local machine and then opens it. I have found that this is extremely
helpful
in my development of the database. You don't have to go from machine to
machine each time you make a front-end change. I know this doesn't help
with
your current issue but I thought I would throw is out there for when you
are
ready to redeploy in September.

Tony Toews has a free one at http://www.granite.ab.ca/access/autofe.htm

And realistically, splitting an application shouldn't take more than a few
minutes.

 

[B. Meincke]

Thank you both for your continued support.

With all due respect, however, Marshall, I think it might take my
inexperience more than "a few minutes" <G> to implement the split, but I
think it's within my reach to do so.

Could either of you indulge me one more question?

From what I am reading, the split process ideally sees only tables and
relationships on the BE with all other dB objects hosted by an mbe on the
client workstations, however, our database imports external csv data and
updates the database tables with this data via a series of queries that only
administrators can run, as only they have permissions on the dump folder.
Would it, therefore, be acceptable to keep the update queries and the macros
that facilitate running them on the BE and continue to execute them from
there? Would this have any sort of negative connotations on the split's
functionality?

Thanks again for all your help.

 

[Douglas J. Steele]

My advice would be to introduce a third temporary database during the import
process.

Tony Toews has an example at http://www.granite.ab.ca/access/temptables.htm

 

[Marshall Barton]

Thank you both for your continued support.

With all due respect, however, Marshall, I think it might take my
inexperience more than "a few minutes" <G> to implement the split, but I
think it's within my reach to do so.

Could either of you indulge me one more question?

From what I am reading, the split process ideally sees only tables and
relationships on the BE with all other dB objects hosted by an mbe on the
client workstations, however, our database imports external csv data and
updates the database tables with this data via a series of queries that only
administrators can run, as only they have permissions on the dump folder.
Would it, therefore, be acceptable to keep the update queries and the macros
that facilitate running them on the BE and continue to execute them from
there? Would this have any sort of negative connotations on the split's
functionality?

You could do that. Just make sure that the BE db is opened
exclusively when running the imports.

Personally, I agree with Doug that creating a separate,
little FE db that links to the BE tables just for the import
task would be better. The only trick is to make sure you
have exclusive acccess to the table(s?) used by the import.
This can be done by using the Execute method with the
dbDenyWrite option.

 

[B. Meincke]

Thank you both again for your continued support...

:
Personally, I agree with Doug that creating a separate,
little FE db that links to the BE tables just for the import
task would be better. The only trick is to make sure you
have exclusive acccess to the table(s?) used by the import.
This can be done by using the Execute method with the
dbDenyWrite option.

Yes. I agree that the second FE would be the ideal solution. It would also
prevent other users from even seeing the update options. Thank you for the
suggestion.

I am afraid I am having a tough time getting the earlier recommended code to
test for the lock file to work. The code is, as suggested, attached to an
unbound form that opens when the database is launched.

If I use:
If Dir(CurrentProject.Path & "SRLP Attendance EXCLUSIVE TEST.LDB") <>
"" _
as you recommended, the results are always to report the lock file not
present.

If, however I tweak the syntax to read:
If IsNull(Dir(CurrentProject.Path & "SRLP Attendance EXCLUSIVE
TEST.LDB")) _
the reverse is true, the results always reporting the lock file present.

What am I doing wrong?

 

[Marshall Barton]

Thank you both again for your continued support...

:
Personally, I agree with Doug that creating a separate,
little FE db that links to the BE tables just for the import
task would be better. The only trick is to make sure you
have exclusive acccess to the table(s?) used by the import.
This can be done by using the Execute method with the
dbDenyWrite option.

Yes. I agree that the second FE would be the ideal solution. It would also
prevent other users from even seeing the update options. Thank you for the
suggestion.

I am afraid I am having a tough time getting the earlier recommended code to
test for the lock file to work. The code is, as suggested, attached to an
unbound form that opens when the database is launched.

If I use:
If Dir(CurrentProject.Path & "SRLP Attendance EXCLUSIVE TEST.LDB") <>
"" _
as you recommended, the results are always to report the lock file not
present.

If, however I tweak the syntax to read:
If IsNull(Dir(CurrentProject.Path & "SRLP Attendance EXCLUSIVE
TEST.LDB")) _
the reverse is true, the results always reporting the lock file present.

What am I doing wrong?

What you did wrong was just copy my idea without double
checking it for accuracy ;-)

I left out the \

If Dir(CurrentProject.Path & "\SRLP Attendance EXCLUSIVE
TEST.LDB") <> "" _

 

[B. Meincke]

What am I doing wrong?

What you did wrong was just copy my idea without double
checking it for accuracy ;-)

I left out the \

If Dir(CurrentProject.Path & "\SRLP Attendance EXCLUSIVE
TEST.LDB") <> "" _