Exclusive access to the database

[Guest]

After the following code executes:

Public Sub populate_qrylist()
Dim db As Database
Dim qdf As QueryDef
Dim strList As String
Dim intCT As Integer
Dim x As Integer
Dim rs As Recordset

Set db = OpenDatabase(txtDBLocation)
intCT = db.QueryDefs.Count
Set rs = CurrentDb.OpenRecordset("tblQueries")
For x = 0 To intCT - 1
rs.AddNew
rs!qryName = db.QueryDefs(x).Name
rs.Update
Next x
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub


I end up getting this message if I try to go into design mode of the form:

"You do not have exclusive access to the database at this time. If you
proceed to make changes, you may not be able to save them later."

This is in Access 2000 SP-3. Any pointers or ideas? Thx.

 

[Andi Mayer]

After the following code executes:

Public Sub populate_qrylist()
Dim db As Database
Dim qdf As QueryDef
Dim strList As String
Dim intCT As Integer
Dim x As Integer
Dim rs As Recordset

Set db = OpenDatabase(txtDBLocation)
intCT = db.QueryDefs.Count
Set rs = CurrentDb.OpenRecordset("tblQueries")
For x = 0 To intCT - 1
rs.AddNew
rs!qryName = db.QueryDefs(x).Name
rs.Update
Next x
rs.Close
Set rs = Nothing

I miss:
db.close
you destroy the object, but you leave it open

Set db = Nothing
End Sub


I end up getting this message if I try to go into design mode of the form:

"You do not have exclusive access to the database at this time. If you
proceed to make changes, you may not be able to save them later."

This is in Access 2000 SP-3. Any pointers or ideas? Thx.

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Guest]

Thanks for the reply.

I actually caught that and tried it but it made no difference at all - I
still get the same message. And just for the record - I'm 100% sure no one
else has this open. I can reporduce it everytime just by running this code.

Again - thanks for any help or ideas.

 

[Andi Mayer]

Thanks for the reply.

I actually caught that and tried it but it made no difference at all - I
still get the same message. And just for the record - I'm 100% sure no one
else has this open. I can reporduce it everytime just by running this code.

Again - thanks for any help or ideas.

have you closed access?

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Guest]

Yes - I close Access and when I reopen it I can make changes to everything
but as soon as I execute that code again - boom - from that point on it tells
me I do not have exclusive rights to the database.

 

[Andi Mayer]

Yes - I close Access and when I reopen it I can make changes to everything
but as soon as I execute that code again - boom - from that point on it tells
me I do not have exclusive rights to the database.

check if you have a ldb file left from "txtDBLocation"



If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Dirk Goldgar]

After the following code executes:

Public Sub populate_qrylist()
Dim db As Database
Dim qdf As QueryDef
Dim strList As String
Dim intCT As Integer
Dim x As Integer
Dim rs As Recordset

Set db = OpenDatabase(txtDBLocation)
intCT = db.QueryDefs.Count
Set rs = CurrentDb.OpenRecordset("tblQueries")
For x = 0 To intCT - 1
rs.AddNew
rs!qryName = db.QueryDefs(x).Name
rs.Update
Next x
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub


I end up getting this message if I try to go into design mode of the
form:

"You do not have exclusive access to the database at this time. If
you proceed to make changes, you may not be able to save them later."

This is in Access 2000 SP-3. Any pointers or ideas? Thx.

Am I right in thinking that txtDBLocation holds the path to a separate
database from the one that the code is running in? So that you are
making, in the current database, a table of the stored queries from a
different database? I'm assuming it's a different database, because
otherwise you wouldn't need to open a separate database object to
reference it.

Which of these databases is it that, after the code runs, you don't have
exclusive access to -- the current database, or the one whose path is
stored in txtDBLocation?

 

[Guest]

Correct, I am opening an external database and getting the names of all the
queries in it and I'm then putting them into a table located in the Current
Database. Then the local database where I just executed the code is the one
that now says I don't have execlusive rights.

To be more exact, I open the form in the "current db" push a button that
executes that code, then I switch do design mode and it says I do not have
exlusive rights to the database. If I close out and come back in I'm fine
till I run that code again.

 

[Dirk Goldgar]

Correct, I am opening an external database and getting the names of
all the queries in it and I'm then putting them into a table located
in the Current Database. Then the local database where I just
executed the code is the one that now says I don't have execlusive
rights.

To be more exact, I open the form in the "current db" push a button
that executes that code, then I switch do design mode and it says I
do not have exlusive rights to the database. If I close out and come
back in I'm fine till I run that code again.

I just tried this in my own installation of Access 2000 SP3 (9.0.6926)
and I got a different, but interesting, result. When the line

Set rs = CurrentDb.OpenRecordset("tblQueries")

Set db = OpenDatabase(txtDBLocation)

to

Set db = DBEngine.OpenDatabase(txtDBLocation)

then that error vanished. Even more oddly, when I then removed the
DBEngine qualifier, the problem didn't reappear. And I never had a
problem with not being able to go into design mode.

I've heard that odd things happen when you use the OpenDatabase method
without specifying its base object -- maybe it creates a separate
Workspace object or something. Try using

Set db = DBEngine.OpenDatabase(txtDBLocation)

in your code, and see if the problem goes away.

 

[Guest]

WOW - that worked. Thanks a million. I'd call that a bug.

 

['69 Camaro]

Hi, Steve.

I'd call that a bug.

Fortunately, it's not a bug. This behavior is by design in order to protect
the monolithic record that stores all Access-specific objects in the
database from becoming corrupted in a multiuser environment. As you know, a
user must have exclusive access to the database file in order to make design
changes to a form, report, module, et cetera, which will be saved in the
monolithic record. Even if the database was opened in shared mode, Access
will promote the user's shared lock to an exclusive lock as soon as a form
is opened in Design View, as long as no other user or process is sharing the
workspace.

Of course, that workspace is defined by the Workspace object, and a
Workspace object can only handle one Database object "exclusively" at a
time. As soon as your code referenced the second Database object in the
default Workspace object (which is the Workspace object used, unless
otherwise specified), the Workspace was being shared. This is why you got
the warning as soon as you opened the form in Design View, because your
shared lock _couldn't_ be promoted to the exclusive lock that would be
needed to make any changes to the form's design.

The work-around of course, is to either explicitly or implicitly use a
Workspace object other than the default Workspace object when referencing
the Database object for the other database, as Dirk suggested.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Steve Huff]

How does adding DBENGINE to the OpenDatabase method refer to a different
workspace? If anything it would be the same default workspace would it not?
I'm confused on how using DBENGINE versus DBENGINE.OpenDatabase makes a
difference. Thanks.

p.s. I"m going to repost this so it goes to the top of the list...

 

[Dirk Goldgar]

p.s. I"m going to repost this so it goes to the top of the list...

That's not a good reason to repost, Steve. You have no "right" to the
position at the top of the list, nor is it proper etiquette to leave an
ongoing discussion thread and start a new thread solely for the purpose
of putting your question at the top of the list.

Valid reasons for reposting are:
1. After several days, there has been no substantive reply to your
message.
2. The topic of the thread has drifted so far that your followup
question is really unrelated to the original thread topic.
3. The current participants in the thread have exhausted their ability
to help you, and you fear no new respondents will appear.
4. The person(s) you are in conversation with suggest you do so.

Be aware that most experienced newsgroup posters have their newsreader
programs set to automatically and highlight all threads they are
currently paricipating in, so new messages posted to those threads will
not go unnoticed by them.

 

['69 Camaro]

Hi, Steve.

How does adding DBENGINE to the OpenDatabase method refer to a different
workspace? If anything it would be the same default workspace would it

not?

That would be true if the default Workspace object were the default member
of the DBEngine object. It isn't.

I'm confused on how using DBENGINE versus DBENGINE.OpenDatabase makes a
difference.

I think you mean OpenDatabase( ) vs. DBEngine.OpenDatabase( ), so I'll
explain in that context.

The DBEngine object's default member is the Workspaces Collection Property.
And the Workspaces Collection Property has no default member. Therefore,
when the DBEngine object is being referred to without an explicit Workspace
object defined, some logic must be executed to determine which Workspace
object to use implicitly. There are three choices: the default Workspace
object, another previously-defined Workspace object, or a newly created
Workspace object.

The most efficient use of resources dictates use of existing resources first
and then allocating memory for creating new objects only when necessary.
The default Workspace object is a bad choice, because it already has a
Database object defined. (I'll explain the implications of this in a
moment.) If a previously-defined Workspace object is available (i.e., the
Database object isn't assigned to a database), then it will be used for the
new Database object. If no Workspace object is currently available, then a
new one will be created for the new Database object.

We don't want to add a second Database object to the default Workspace
object because a Workspace object can only handle one Database object
exclusively. Using the OpenDatabase( ) method without explicitly or
implicitly referencing a Workspace object will usually create a Database
object in the default Workspace object, but not always. Even with the
latest service packs installed, this behavior is inconsistent and depends
upon whether another Workspace object is already open (and that Database
object is available for assignment), whether the database file has been
decompiled recently with the undocumented /Decompile command-line switch,
and whether the database file has been compacted and repaired even more
recently. This is why executing identical code that uses two or more
Database objects seemingly in the same scope will sometimes work, but
sometimes give one of the following errors:

"Error # 3734: The database has been placed in a state by user 'Admin' on
machine 'MyComputer' that prevents it from being opened or locked" or

"Error # 3045, "Could not use <FullPath\MyDB.mdb>; File already in use."

Clear as mud? Well, just so you don't have to memorize and explain this
trivia about how the DAO library works with the the Jet Workspace the next
time someone sees your code and asks, "Why are you using
DBEngine.OpenDatabase( ) instead of the simpler OpenDatabase( )?" you can
explicitly define a Workspace object to keep the Database objects separate,
and make sure that the Database object assigned to it is released when it
goes out of scope. The following code will work just fine:

'******* Start Code *******

Public Sub populate_qrylist()

On Error GoTo ErrHandler

Dim wkSpc As Workspace
Dim db As Database
Dim intCT As Integer
Dim x As Integer
Dim rs As Recordset
Dim fOpenedRecSet As Boolean
Dim fOpenedDB As Boolean
Dim fOpenedWkSpc As Boolean

'-----------------------------------------------------
' Create a 2nd Wkspc to handle the
' other DB object separately.
'-----------------------------------------------------

Set wkSpc = DBEngine.CreateWorkspace("TempWkSpc", "Admin", "")
fOpenedWkSpc = True

Set db = wkSpc.OpenDatabase(Me!txtDBLocation.Value, False)
fOpenedDB = True
intCT = db.QueryDefs.Count
Set rs = CurrentDb.OpenRecordset("tblQueries")
fOpenedRecSet = True

For x = 0 To intCT - 1
rs.AddNew
rs!qryName = db.QueryDefs(x).Name
rs.Update
Next x

CleanUp:

If (fOpenedRecSet) Then
rs.Close
fOpenedRecSet = False
End If

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

If (fOpenedWkSpc) Then
wkSpc.Close
fOpenedWkSpc = False
End If

Set rs = Nothing
Set db = Nothing
Set wkSpc = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in populate_qrylist( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & "Error #: " & _
Err.Number & vbCrLf & Err.Description,
vbExclamation + vbOKOnly

Err.Clear
GoTo CleanUp

End Sub


'******* End Code *******

But to be honest, I'm wondering why you went to all this trouble to do this
in VBA code. Why doesn't your application just query the other database's
MSysObjects table for the current list of query names? The list of query
names in your tblQueries table is going to become outdated at some point,
not to mention one of the goals of relational databases is elimination of
redundancy.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Steve Huff]

Gunny,
Thanks a lot that helps a lot, I begining to see the light. You are
correct, I meant "DBENGINE.OpenDatabase versus OpenDatabase"

You say:

Using the OpenDatabase( ) method without explicitly or
implicitly referencing a Workspace object will usually create a Database
object in the default Workspace object, but not always.

I under stand how workspaces work --- and now thanks to your help I
understand how it makes a decision on what workspace to use when you don't
specifically tell it a workspace, but I'm still unsure why it picks a
different workspace with DBENGINE.OpenDatabase then it does with just
OpenDatabase. Isn't OpenDatabase a method of DBENGINE? So it seems it
should work the same wether I specificy DBENGINE or not. I totally
undertand why your code works better because I'm specifically telling it to
use a different workspace and then i'm closing that workspace but I assume
DBENGINE.OpenDatabase creates a new workspace for me where OpenDatabase does
not (depending on if there is one already available and so forth) but I
don't see why...

To answer your question, the reason I was not using MSysObjects was
everytime I tried to open MSysObjects in another database I got an error
telling me I didn't have rights to the table so I gave up when my boss
suggested using the QueryDef object and went that route.

Again thanks for your help.
--
_______________________
Steve Huff
http://www.huffs.us


Generic email: (e-mail address removed)

 

['69 Camaro]

Hi, Steve.

I'm still unsure why it picks a
different workspace with DBENGINE.OpenDatabase then it does with just
OpenDatabase. Isn't OpenDatabase a method of DBENGINE? So it seems it
should work the same wether I specificy DBENGINE or not.

The OpenDatabase( ) method is a method of both the DBEngine object and the
Workspace object. Each method of the same name _could_ be using different
logic to pick the implicit Workspace object completely independent of the
other's method. However, I suspect that there's inheritance going on, so if
these methods aren't using identical code, then the code is nearly
identical.

So when the code doesn't specifically identify which object to use the
OpenDatabase( ) method from, Access is usually picking the default
Workspace's OpenDatabase( ) method, which is why the new Database object is
generally assigned to the default Workspace object, not a separate Workspace
object.

but I assume
DBENGINE.OpenDatabase creates a new workspace for me where OpenDatabase does
not (depending on if there is one already available and so forth) but I
don't see why...

It's supposed to be dynamic: under the present circumstances, pick the best
Workspace object for the job. The Workspace object picked may not be the
one you expect, so if you're programming the code, identify the Workspace
object explicitly so that you don't have to guess what Access is doing
behind the scenes when something goes wrong and you need to fix it.

everytime I tried to open MSysObjects in another database I got an error
telling me I didn't have rights to the table

It's been awhile, but now that you mention it, I vaguely remember the "No
read permission" problem when I first coded a similar solution in a Jet 4.0
database. There are a couple of work-arounds for it, but yours is the most
intuitive. Keep it.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Steve Huff]

Now I fully understand. Thanks a lot for your help it is very
appricated!!!!

I've been programming with Access since version 2 but this problem really
had me stumped.

I hope you and everyone else reading this has a happy holidays!!

-Steve Huff

 

['69 Camaro]

I hope you and everyone else reading this has a happy holidays!!

Thank you. It's very sad holidays here, but others will surely be able to
enjoy them elsewhere. I wish you a merry Christmas.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)