Set exclusive access on specific database (Access 2000)

  • Thread starter Thread starter Sally
  • Start date Start date
S

Sally

Hi. I'm new to Access development, and have no knowledge
of VB/VBA, but I suspect I can only do what I want to by
using code.

I have created an 'autoexec' macro that carries out
certain tasks when a user wants to go into the database I
have created and put on the network. For reasons I won't
bore you with, it's important that anyone is prevented
from continuing to run the macro and enter the database if
it is already in use.

Having spent a few hours looking through the reference
books, the only thing I have found is the code 'Set db =
OpenDatabase("NWIND.MDB", True)'. I have tried putting
this (using my database name, obviously!)in as a Run Code
action at the beginning of my macro, but the function
isn't valid.

I would really appreciate any help on this. If it's not
possible, it means I have to re-write my database so that
consecutive access does not cause a problem :-(

Many thanks

Sally
 
Access can handle more than one user at a time, but since you don't mention
why you want this....

You could try giving all users only Read, Write permission on the folder
where the mdb is. Without the create/delete permission, the first person in
will open it exclusively.
 
Thanks for this idea. I tried it, but we can still log in
concurrently with no messages being displayed(we have
Windows 2000 server, and there is no 'create/delete'
permission, but we unchecked all allow permissions except
the read and write ones.

The reason I need this is (I'll try not to be long-winded
and unintelligeable!):

As I don't know how to set up user accounts, etc without
risking messing everything up, the login procedure is
incorporated in the autoexec macro. The first action in
this is to run a query that accesses data held in
the "Teams" table, where the Team Name, User Name and
Password are held. The query prompts the user to enter
their User Name and Password, and, assuming these match
one of the records in the table, and new table
called "CurrentLogon" is created containing their User
Name. I then reference this table/form in the majority of
the functionality, to automatically insert the correct
user in the relevant table and to perform certain actions
depending on the name in this table.

This works perfectly if only one person at a time accesses
the database (I must admit, I had assumed that it would
work in the same way as other MS products and would give a
message saying it is locked by ...), but if someone else
logs in when it's already in use, the CurrentLogon becomes
the newest one, which is no good at all!

Hope this makes sense.

Any other suggestions before I rewrite the whole thing??

Thanks

Sally
 
Hi Sally,

New to this myself BUT two ideas:-

1. In tools->options->advanced you can set a database to
be opened exclusively (Default Open Mode - Exclusive).

2. If its just a lock in the macro you want then:-

Make the first statement in the macro RunSQL and use
statement
"create table lock" - no quotes.

Make the last statement in the macro RunSQL and use
statement "drop table lock".

The table called lock will exist while the macro is
running and if a second person tries to run it they'll get
a nasty message about the table already existing.
Obviously if the macro fails elsewhere you'll have to
manually clear the lock table.
 
Hi Sally,

Replied earlier but found a better way (I think)

1. Create a table called lock with one field - also called
lock and of type Yes/No

2. Have following condition at the start of the macro
Dlookup("Lock","Lock") with action Msgbox "Locked"

3. Continue the condition actions - by having ...
(literally three dots) in the condition column and
StopMacro as the action

4. Next action (no need for more conditions) RunSQL
with SQL Statement update Lock set Lock=true

then rest of your macro ... finally make the last statement

5. RunSQL with SQL statement undate Lock set Lock=false

I've tried this and it works fine - just that you get a
couple of warnings about updating one row - just click yes
to them. Can probably turn these off - setwarnings = false
or some such.

Good luck!
 
Thanks, John! Your suggestion works perfectly - I've just
moved the final SQL statement, to set Lock to false, to
the macros attached to my exit buttons, so that it stays
locked the whole time they're in.

You've saved me a huge amount of time and frustration :-)

With much gratitude...

Sally
 
Back
Top