Check for Exclusive

[B. Meincke]

My thanks to Marshall for his patience with me in trying to code a database
to check for the presence of the lock file before opening. His suggestion was
to attach the following to the open event of an unbound form that opens at
startup:

If Dir(CurrentProject.Path & "\SRLP Attendance.LDB") <> "" _
Then
MsgBox "The database is in use", vbCritical, "Halt"
Exit Sub
Else
DoCmd.OpenForm ("frmSwitchboard")
DoCmd.Close acForm, "autoexec", acSaveNo

Unfortunately, this is still not working. In my elementary understanding,
this makes sense, as, once the database window is open, the lock file is
already present and the database window opens before the unbound form.

Is there somewhere else I coud put this code that would have it execute
before the lock file appears?

Thanks.

 

[B. Meincke]

I'm sorry. I should clarify:

The function returns a value of true (that is that the lock file is present)
whether you are the first user to open the database or a subsequent one.

If a user opens the database when it was not previously open, I would like
it to open seamlessly. If it's already open, however, I would like the user
to be alerted to that fact, and blocked from opening a second instance of it.

I know! I know! But the thing has grown out of control. I plan to split it
into FE and BE over the summer break, but we need a method to control its use
for the remainder of this semester.

Thanks again in advance for any advice or suggestions.

 

[Klatuu]

A simple solution.
Use a shortcut to open the database.
Use the command line option /excl
whoever first opens the database will be able to use it.
Anyone else trying to open it will another has it open will get an error
message.

 

[Paolo]

Hi B. Meincke,
Look at the size of the ldb file.
If the size of the ldb file is =64 bytes you are the first to open the DB.
If the size of the ldb file is >64 bytes someone already opened the DB.

HTH Paolo

 

[B. Meincke]

That works like a charm! And so easy, how embarrassing...

However, as teachers currently access the database from a mapped network
share, is there any way I could put a shortcut in the host folder that would
take multiple versions of Access throughout the domain (and thus variations
in the Run command path to the application) into consideration? Or do I have
to put individual shortcuts into local workstation profiles for each teacher
specific to his or her workstation's version of Access?

There are too many staff members that would not be comfortable with going
near the Run dialog box!

Again, thank you for the solution.

 

[Klatuu]

No need to use the run box.
A shortcut needs to be on each user's desktop.
As to the path, NEVER use drive map paths in a multi user environment. use
UNC paths. Like:
\\MyServerName\SomeFolder\SomeSubFolder

That way, individual drive mapping doesn't matter.

I know you didn't want to be preached to about your configuration, so please
consider this useful tips.

First split the database so that all the data is in one mdb and the
application objects in another. When you run the Database Splitter wizard in
Access, you will end up with two mdb files. One with the original name, and
one with _be added to it.

SuperApp.mdb (front end) and SuperApp_be.mdb

Now, put _be in a shared folder all users have read/write permissions to.

Open the app and use the Linked Table Manager to relink the tables using UNC
paths as suggested above.

It is also important that each user have their own copy of the application
on their computer. Sharing one app file among several users degrades
performance and is more subject to corruption.

Now, deployment would be a hassle that way. But wait! here is a link to a
really useful tool you can include in your application so that when you have
made mods to the system, each user will get the latest version whenever they
open the app.

http://www.amazecreations.com/datafast/Download.aspx

Look for:
Auto Download New Client

And download the zip file.

 

[B. Meincke]

Thanks again to both of you for your responses.

I know the best solution is to split the database. I fully intend to do this
when the semester is over and I can have some time when the database is not
in use.

Paolo,
In the meantime, can you give me a hint as to what syntax I could use in a
function, say attached to the Open event of the switchboard that would check
the lock file's size?

Again, thanks.

 

[Paolo]

Here's the code to retrieve the size of a file.

Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile("path and name of your file")
s = UCase(f.Name) & " uses " & f.Size & " bytes."
MsgBox s, 0, "file Size Info"
set f=nothing
set fs=nothing

Cheers Paolo

 

[B. Meincke]

Man, what would I ever do without you folks...

I can't begin to tell you how grateful I am for this forum.

Paolo,
That works beautifully. When I get back to work after March Break, I will
revise and implement it into our live database.

I've run a copy of our databse through the split process and begun to take a
look at the resources you've recommended to reconnect linked tables. I'm
pretty confident that once July comes I can get the database into a more
stable condition for the 2008-2009 school year.

Thank you again.

 

[Douglas J. Steele]

There's absolutely no reason to use FSO (File System Objects).

Here's a far simpler version that does the same as Paolo's code:

Dim strFile As String
Dim strMsg As String

strFile "path and name of your file"
strMsg = UCase(strFile) & " uses " & FileLen(strFile) & " bytes."
MsgBox s, 0, "file Size Info"