Open Exclusive

[Connie]

I'm using Access 2002. I assign permissions to groups not individual users.
One group I assigned Open/Run & Open Exclusive permissions to the database
object. I was under the impression by checking Open Exclusive a user
logging in under that group would be opening the database exclusively &
anyone else trying to log in would get some kind of message stating such.
While testing I had someone log in using that group (no one else was in the
database at that time), then I logged in under a different group that didn't
have the exclusive permission. I was able to get in & add/edit data. What
am I doing wrong?

TIA
Connie

 

[TC]

Assigning someone Open Exclusive permission, just gives them
/permission/ to open the database exclusively. It does not /make/ them
open the database exclusively.

(Like giving someone Add Data permission (or whatever it's called).
This gives them permission to add new records - it does not make them
add new records.)

What are you really trying to achieve, from a business or end-user
viewpoint?

HTH,
TC

 

[Connie]

oh that makes sense now that you've explained it. What I'm trying to do is
when this user logs on, I want the database to open exclusively so no one
else can open it & change things, until she's done & logs out & if others
have the db open already, this user would receive a msg stating such. So I
guess I do this via code ... can you explain how? Do I put code behind the
switchboard's OnOpen ... however, isn't that too late as the database is
already open at that point?

Thanks for your help TC!
Connie

 

[TC]

Connie wrote:

(snip)

What I'm trying to do is when this user logs on,
I want the database to open exclusively so no one
else can open it & change things, until she's done
& logs out

Why? Access is a multi-user database. In general, you would expect
several people to use it simulataneously.

So I guess I do this via code ... can you explain
how? Do I put code behind the switchboard's OnOpen
however, isn't that too late as the database is
already open at that point?

Yes, it is too late at that point.

One way to open the db exclusively, is to give the relevant user a
shortcut which uses the "/excl" switch. I think you'll find that switch
documented under "command line options" (or somesuch) in the online
help.

But, tell me /why/ you need to do this. Then some other approach might
spring to mind!

Thanks for your help TC!

Glad to help out.

HTH,
TC

 

[Connie]

because this user will be reconciling & I don't want other users in there
editing data while she is doing this as she goes thru several steps ... 1st
getting reports ... then processing. If another user comes in after her 1st
step & changes some numbers or adds data, this person reconciling would be
unaware that she's using in correct data. So I've created a user/group
specifically for this (open exclusive) & when this user needs to do her
reconciling she logs on using this group.

I've tried the switch & it does work .. thanks!! Now I have another
question (which will take care of 2 problems for me). When another user
logs in, while she has it opened exclusively, is there a way to prevent
Access's standard msg stating someone has it open exclusively (which also
shows the name & path of the db) & display my own msg .. like you can do in
a form's OnError event using the arguments to bypass Access's standard &
inserting your own? If I know where to do this, then I'll also be able to
prevent the db from opening in share mode automatically, as is the case for
2002, when the exclusive user tries to open exclusively but there are other
users in the db. When this is the case, instead of opening in share mode,
I'd like it to display a msg (my own) & then close Access.

Thanks so much for all your help TC!!
Connie

 

[TC]

because this user will be reconciling & I don't want other users in there
editing data while she is doing this as she goes thru several steps .... 1st
getting reports ... then processing. If another user comes in after her 1st
step & changes some numbers or adds data, this person reconciling would be
unaware that she's using in correct data. So I've created a user/group
specifically for this (open exclusive) & when this user needs to do her
reconciling she logs on using this group.

Ok, understood. That seems like a fair use of an exclusive open.

I've tried the switch & it does work .. thanks!!

That's a reluef: because I've never actually used that switch myself!

(snip) When another user
logs in, while she has it opened exclusively, is there a way to prevent
Access's standard msg stating someone has it open exclusively (which also
shows the name & path of the db) & display my own msg .. (snip) When this
is the case, instead of opening in share mode, I'd like it to display a
msg (my own) & then close Access.

You can probably do this by using a script file (eg. VBScript) to try
to open the db, & trap the error if it does not open ok (due to it
already being open exclusive). I'll try that out tonight, & get back to
you (hopefully with some code) tomorrow.

Thanks so much for all your help TC!!

No probs, glad I could help

TC

 

[Connie]

I'm not familiar with creating a script file or VBScript ... is it part of
VB? Were you able to try it out ... & if it's possible how would you
connect everything together ... the shortcut, that opens the database with
the mdw, & this script? Can you tell I'm clueless?

Thanks for help TC

 

[TC]

Hi Connie

I've been a busy & unable to write the code that I promised :-(

Here is something "off the top of my head". But you'll have to try it
yourself, to see if it works.

First, use a simple text editor (eg. Notepad) to put the following
statement into a file:

msgbox "hello"

Save that file as test.vbs (not text.txt). Double-click the file & see
if you get the message. If so, you have just successfully written & run
your first vbscript program!

Then, try this:

(UNTESTED)

dim o, db
set o = createobject("dao.dbengine.35")
o.systemdb="full path to your wgf file"
o.defaultuser="the name of a user"
o.defaultpassword="that user's password"
on error resume next
set db=o.opendatabase("full path to your mdb file")
if err.number = 0 then
msgbox "db is not locked exclusive"
elseif err.number = ???? then
msgbox "db is locked exclusive"
else
msgbox "unexpected error " & err.number & ": " & err.description
end if
set db=nothing
set o=nothing

I'm not sure what error you'll get when the db is currently open
exclusive. Maybe 3045?, I can't quite remember. Just change the code
(temporarily) to display whatever error number is returned.

The above is very rough code, but it might get you started. The
intention of that code is to tell whether the database is currently
open exclusive, or not. If it is not, you could make the code open the
db, instead of just displaying a message (as it currently does).

One disadvantage of that code is that you must supply the username &
password of a user who has permission to open the database. Ideally,
that would be a special user who has no permissions /except/ to open
the database!

Sorry I can't do more than this for you at present.

Cheers,
TC

 

[Connie]

TC sorry to have bugged you, but thanks so much for all the info. I was so
excited to see that the msgbox worked ... my very 1st vbscript ... so cool!
So I was excited to copy all your code into notepad (btw down in the
line for o.systemdb="full path to your wgf file" .. wgf means workgroup file
right?) & I thought it was working as I opened the database using the
exclusive switch & got the msg below .. so I just changed 3045 to 3049 ...
thinking the msg was what I'd get if it was already open exclusively.
However, when I went in with a normal logon (shared) it's still raising this
same err number. What am I doing wrong? If you don't have time to go over
this with me, I totally understand, but could you suggest a website where I
could pick up some (beginner's) info & more vbscript commands?

******
err msg:
unexpected error 3049: Can't open database. It may not be a database that
your application recognizes or the file may be corrupt.
******
And once again thanks so much for all your help & patience!!
Connie

 

[TC]

TC sorry to have bugged you, but thanks so much for all the info. I was so
excited to see that the msgbox worked ... my very 1st vbscript ... so

cool!

Well done

So I was excited to copy all your code into notepad (btw down in the
line for o.systemdb="full path to your wgf file" .. wgf means workgroup file
right?)
Yes.


I thought it was working as I opened the database using the
exclusive switch & got the msg below .. so I just changed 3045 to 3049 ...
thinking the msg was what I'd get if it was already open exclusively.
However, when I went in with a normal logon (shared) it's still raising this
same err number. What am I doing wrong?

I'm not sure I understand exactly what you're doing. (Sorry, I'm having
a bit of a "dense attack", today!) Can you clarify what error (if any)
you get when:

- you run the scipt when the database is not already open by
anyone/anything else;

- you run the script when the database is already open "normally" (not
exclusively) through MSAccess, and

- you run the script when the database is already open exclusively
(with the /excl switch) through MSAccess.

In each case, maybe just edit the script to display whatever error is
returned:

....
on error resume next
set db = o.opendatabase( ... )
msgbox err.number & " " & err.description
(end of edited script)

If you don't have time to go over
this with me, I totally understand, but could you suggest a website where I
could pick up some (beginner's) info & more vbscript commands?

Not sure I can suggest a good website. It really depends on what you're
trying to learn. There are losts of good sites on the "Access Web". I
think you get there via www.mvps.org.

As for vbscript, there are thousands of tutorials etc. on the web. Just
google on "vbscript tutorial", or somesuch, & I'm sure you will get
lots of hits!

******
err msg:
unexpected error 3049: Can't open database. It may not be a database that
your application recognizes or the file may be corrupt.
******

Can you clarify exactly how & when you get that error?

And once again thanks so much for all your help & patience!!
Connie

No probs, glad I could help. I'm just a bit limited with the time that
I can spend, at present.

 

[Connie]

Hi TC

I did the test you suggested, after revising the script, & I received the
same err for each of the 3 scenarios you listed:

3049 Can't open database. It may not be a database that your
application recognizes or the file may be corrupt.

I know you're busy, so whenever you get a chance to respond is fine
Thanks again!
Connie

 

[TC]

Hi Connie

That should not occur, if you are supplying the database path/file name
correctly. Can you post the exact code you are using, ie. copy & paste
from the vbs file?

Cheers,
TC

 

[Connie]

here you go. That was the 1st thing I checked was if I typed the path
correctly for both the wgf & mdw, but maybe you can find something I'm doing
wrong. Because I'm not using a password for now (not until I'm ready to
copy secured files to their server), I tried entering a space between the
quotes & that didn't work either.

Thanks for your time.


this is the 1st one:

dim o, db
set o = createobject("dao.dbengine.35")
o.systemdb="d:\data\client\security\developer.mdw"
o.defaultuser="developer"
o.defaultpassword=""
on error resume next
set db=o.opendatabase("d:\data\client\May10Secured\RCVobj.mdb")
if err.number = 0 then
msgbox "db is not locked exclusive"
elseif err.number = 3045 then
msgbox "db is locked exclusive"
else
msgbox "unexpected error " & err.number & ": " & err.description
end if
set db=nothing
set o=nothing

this is the revised one for just the error:
dim o, db
set o = createobject("dao.dbengine.35")
o.systemdb="d:\data\client\security\developer.mdw"
o.defaultuser="developer"
o.defaultpassword=""
on error resume next
set db=o.opendatabase("d:\data\client\May10Secured\RCVobj.mdb")

msgbox err.number & " " & err.description

set db=nothing
set o=nothing

 

[TC]

Connie wrote:

(snip)

this is the revised one for just the error:

dim o, db
set o = createobject("dao.dbengine.35")
o.systemdb="d:\data\client\security\developer.mdw"
o.defaultuser="developer"
o.defaultpassword=""
on error resume next
set db=o.opendatabase("d:\data\client\May10Secured\RCVobj.mdb")
msgbox err.number & " " & err.description
set db=nothing
set o=nothing

And that gets error 3049 ("Can't open database. It may not be a
database that your application recognizes or the file may be
corrupt."), right?

Mmmm. The code looks ok to me. Try the following:

(1) /Omit/ the following three lines & change the opendatabase call to
reference an /unsecured/ database.

o.systemdb="d:\data\client\security\developer.mdw"
o.defaultuser="developer"
o.defaultpassword=""

(2) If that works, re-add the following one line & try the unsecured
database again:

o.systemdb="full path to your default system.mdw"

(3) If that works, re-add the other two lines & try the unsecured
database again:

o.systemdb="full path to your default system.mdw"
o.defaultuser="Admin"
o.defaultpassword=""

Tell me what happens in each case.

Naturally you would also want to start the secured database from a
shortcut, using the /wrkgrp switch to identify the proper workgroup
file, just to ensure that it does start ok, & does not get error 3049.

Sorry this isn't working as easily as I expected!

Cheers,
TC

 

[Connie]

TC wrote

(snip)

And that gets error 3049 ("Can't open database. It may not be a
database that your application recognizes or the file may be
corrupt."), right?
Yes

Mmmm. The code looks ok to me. Try the following:

(1) /Omit/ the following three lines & change the opendatabase call to
reference an /unsecured/ database.

now the err # is:
3343 Unrecognized database format 'd:\data\client\TestVBscript\RCVobj.mdb'

here's the code I'm using. FYI, I have the unsecured db open (shared)
before running this script

dim o,db
set o = createobject("dao.dbengine.35")
on error resume next
set db=o.opendatabase("d:\data\client\TestVBscript\RCVobj.mdb")
if err.number = 0 then
msgbox "db is not locked exclusive"
elseif err.number = 3045 then
msgbox "db is locked exclusive"
else
msgbox err.number & ": " & err.description
end if
set db=nothing
set o=nothing

TC I want to make sure you are aware I'm using Access 2002 on Win XP,
however, by default it uses Access 2000 format. Does that have anything to
do with this err. Does this version use dbengine.35 ??

Sorry this isn't working as easily as I expected!

no problem .. I know these things happen ... with so many variables .. it's
hard to troubleshoot if not at actual computer.

I really appreciate all your help with this TC, but if you want to bag it, I
totally understand ... besides I believe this is getting out of the realm of
this newsgroup ... we probably should be in a vbscript newsgroup.

One question tho, I'm having a hard time wrapping my brain around exclusive
in a multi-user environment. If I have a shortcut setup for a specific
group that will open the db in exclusive mode, so no other user can log on
.... how does that work seeing each user will be running their own copy of
the FE ... when a user opens their own copy of the FE in exclusive mode, how
will another user's FE know that?

Thanks TC!

 

[Connie]

I had to change the dbengine from 35 to 36 & it works beautifully!!!

I've learned so much from you ... I'm now putting msgboxes in my code so I
know what part of code is not running plus I'm googling more. I just went &
googled on opendatabase & found an article that refers to Access 2000, even
tho I'm using Access 2002 - it triggered the fact that it's DAO 3.6 ... & it
hit me ... we were using 3.5.

Thanks for all your help!!!! .... but before we end this thread .. if u
wouldn't mind .. could u ans 1 more question which I put in my last post ...
I've pasted it here for you:

One question tho, I'm having a hard time wrapping my brain around exclusive
in a multi-user environment. If I have a shortcut setup for a specific
group that will open the db in exclusive mode, so no other user can log on,
... how does that work seeing each user will be running their own copy of
the FE ... when a user opens their own copy of the FE in exclusive mode, how
will another user's FE know that?

 

[TC]

Connie wrote:

(snip)

TC I want to make sure you are aware I'm using Access 2002 on Win XP,
however, by default it uses Access 2000 format. Does that have anything to
do with this err. Does this version use dbengine.35 ??

GAK! Well found. "My bad" :-(( Access versions after 97 use MS Jet
version 4, so the Createobject call should say DAO.DBEngine.40, not
DAO.DBEngine.35. You are absolutely correct, & spotted that one very
well.

If 40 doesn't work (but it should), use registry editor to find all
occurrences of DBEngine, & post the various numbers here.

When you try again, make sure you test initially with the db /not/ open
in Access. Run the script first, to ensure that it can open the db
without getting any more errors. Then try various combinations of the
db being open, or open exclusive, before you run the script.

One question tho, I'm having a hard time wrapping my brain around exclusive
in a multi-user environment. If I have a shortcut setup for a specific
group that will open the db in exclusive mode, so no other user can log on
... how does that work seeing each user will be running their own copy of
the FE ... when a user opens their own copy of the FE in exclusive mode, how
will another user's FE know that?

Good question! I've never used the /excl switch myself, so I can't say
for sure. Clearly, when Access sees that switch, it opens "something"
exclusively. If that "something" is, the BE (not the FE), then that
would answer your question.

Cheers!
TC

 

[TC]

Oops, just read this one!

Connie, well done for getting it working! It's fun to work with
someone who perserveres, & doesn't give-up half way through.

Perhaps you could post back, giving your final version of the script,
and a brief description of what it does. Then, that post will become
the authoritative answer for people who have the same problem in
future.

Again, well done

TC

 

[Joan Wild]

Good question! I've never used the /excl switch myself, so I can't say
for sure. Clearly, when Access sees that switch, it opens "something"
exclusively. If that "something" is, the BE (not the FE), then that
would answer your question.

The /excl switch will apply to the mdb being opening, in this case the
frontend. There is no effect on the BE.

 

[Connie]

Thanks for your reply Joan.

Then what do I need to do to make sure no one else gets into the database
when a certain user is logged on exclusively, as each user has their own FE
in their profile (they use remote access - Terminal Services). If I have a
shortcut on this user's desktop that includes the /excl it will open her
copy of the FE exclusively, but that won't effect the other FEs .. correct?
So how do I prevent others from getting into the database when she's in?

Connie