Exclusive access to the database

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.

On Wed, 22 Dec 2004 07:03:08 -0800, "Steve Huff"
wrote:

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

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.



"Steve Huff" wrote:

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.

On Wed, 22 Dec 2004 07:39:09 -0800, "Steve Huff"
wrote:

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

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.

"Steve Huff" wrote:

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.

On Wed, 22 Dec 2004 07:55:04 -0800, "Steve Huff"
wrote:

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

"Steve Huff" wrote in message

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?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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" wrote:

"Steve Huff" wrote in message

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?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Steve Huff" wrote in message

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")

was reached I got an error 3045, "Could not use my test database; file
already in use." *However*, when I changed the line

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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


"Dirk Goldgar" wrote:

"Steve Huff" wrote in message

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")

was reached I got an error 3045, "Could not use my test database; file
already in use." *However*, when I changed the line

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)