Access Still Sees QueryDef Deleted During a Session?

[Tim Marshall]

A2003, but this behaviour also occurred during the same DAO process I'm
about to describe in A97.

I have a sub procedure which takes two arguments: a querydef name; and
an SQL statement constructed from the calling procedure and constructs
or modifies a querydef. The sub runs through the querydefs collection -
if it finds the querydef name argument, it takes the existing query and
modifies the SQL. If not found, a new querydef is created.

The problem is this. It works fine when the mdb is first opened and the
querydef in question has been previously deleted - it finds that there
is no querydef by the argument name and creates a new one. However, if
during a session I delete a querydef through the database window, the
function still finds the querydef exists! I've tried using
querydefs.refresh at the beginning of the function to no avail.

The error is (in this case, the query name being passed is
"qryCriteriaUserList":

Error 3011 The Microsoft Jet database engine could not find the object
'qryCriteriaUserList'. Make sure the object exists and that you spell
its name and the path name correctly.

When I close the app and reopen it, things go as you would expect, with
no error (the query gets created).

Here's the code I use (this is for pass through queries, but I don't
think this matters).

Thanks very much in advance for any suggestions.

Sub sCreatePT(strSql As String, strQryName As String)

'Takes an SQL string (strSql) and a query name (strQryName)
'and either creates a new PT query or, if
'it exists already, changes the SQL string

Dim qdf1 As DAO.QueryDef

Dim booFound As Boolean

On Error GoTo Err_Proc

'I just added the following, it still coughs up

Access.CurrentDb.QueryDefs.Refresh

booFound = False

For Each qdf1 In dbLocal.QueryDefs

If qdf1.Name = strQryName Then

booFound = True

Exit For

End If

Next

If booFound = False Then 'querydef does not exist, so create it

Set qdf1 = dbLocal.CreateQueryDef(strQryName)

Else 'already exists

Set qdf1 = dbLocal.QueryDefs(strQryName)

End If

'Now assign pass through characteristics

With qdf1

.Connect = cTmarConnect

.SQL = strSql

.ReturnsRecords = True

End With

Exit_Proc:

On Error Resume Next 'use brute force to close

qdf1.Close

Set qdf1 = Nothing

On Error GoTo 0

Exit Sub

Err_Proc:

Select Case Err.Number

Case Else

MsgBox "Error " & Err.Number & " " & Err.Description,
vbCritical, "sCreatePT", Err.HelpFile, Err.HelpContext

Resume Exit_Proc

End Select

End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[lylefair]

There must be some joy in looping through and exploring db objects that
I haven't learned about. How about something simple like:

Private Sub CreateQuery(ByVal Name As String, ByVal SQL As String)
With CurrentProject.Connection
On Error Resume Next
.Execute "DROP PROCEDURE " & Name
On Error GoTo 0
.Execute "CREATE PROCEDURE " & Name & " AS " & SQL
End With
End Sub

Sub test()
CreateQuery "Query1", "SELECT * FROM Table1"
End Sub

Me thinks you are going to say,
"But, if the query already exists I want to modify it, not delete it
and replace it."

And I am going to say.
Do you think this is what Access/Jet does when you modify a query
string?

Well maybe it does. Of course it would have to reserve space for the
maximum allowable query string, else, in order to so when the query
string is longer, it would have to rewrite the entire part of the file
that occurs after the original definition.

Or would it write new data at the end of the file, and simple have a
pointer to that?

Who can say? I learned a long time ago that's it's easier to just jump
over the error if you want to delete something that may or may not
exist, and that it's often easier and quicker to replace something than
to modify it; I think this is what Access does 99% of the time.

Of course if we were in MS-SQL we could just use ALTER PROCEDURE and
forget about the deletion and creation.

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:di**********@coranto.ucs.mun.ca:

'I just added the following, it still coughs up

Access.CurrentDb.QueryDefs.Refresh

booFound = False

For Each qdf1 In dbLocal.QueryDefs

You refreshed the wrong instance of the QueryDefs collection.

I'm assuming that you're using my dbLocal code for your persistent
database variable. That variable was initialized the first time it
was used with a call to CurrentDB. That pointer has its own
QueryDefs collection, and it is independent of the new one returned
by Access.CurrentDb.QueryDefs.

You need to change Access.CurrentDb.QueryDefs to
dbLocal.QueryDefs.Refresh, and then the problem should go away.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:di**********@coranto.ucs.mun.ca:

Tim Marshall wrote:

Here's the code I use (this is for pass through queries, but I
don't think this matters).

Set qdf1 = dbLocal.CreateQueryDef(strQryName)

I should add that I don't declare a database variable - I use a
dblocal function which is itself a DAO.DATAbse. This is a
function David Fenton posted here the past Summer or late Spring
and works a treat.

You're on the right track, as I replied to your original post.

The collection that matters is dbLocal.QueryDefs, as
Access.CurrentDb.QueryDefs is a completely different memory
structure. And, of course, Access.CurrentDb.QueryDefs.Refresh is a
useless and redundant thing to do, as CurrentDB refreshes all
collections each time it's called (it's why it's many times slower
than DBEngine(0)(0)), and because since you haven't cached a
reference to the memory structure that your Refresh operated on
(which is a copy of the real one), you just wasted your time.

Now, you'd think that perhaps DBEngine(0)(0).QueryDefs.Refresh would
do the trick, but it won't, because you're using a cached db
variable that has a *copy* of the collections.

If you're using dbLocal(), then USE IT FOR EVERYTHING, as long as
you want to use the front end database.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tim Marshall]

lylefair wrote:

Me thinks you are going to say,
"But, if the query already exists I want to modify it, not delete it
and replace it."

And I am going to say.
Do you think this is what Access/Jet does when you modify a query
string?

HI Lyle, thanks for the reply.

Actually, originally, I did something similar in DAO and/or through the
use of docmd something or other, ie, delete the existing querydef and
create a new one. Asking about it here in the late 90s or, I think
early 2000/1, I came away with the impression that my original method
exacerbated database bloat and that the modification was the better way
to go. Again, that was a long time ago, and your hypothetical reply
makes a lot of sense... Hmmmmm.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Tim Marshall]

David W. Fenton wrote:

You need to change Access.CurrentDb.QueryDefs to
dbLocal.QueryDefs.Refresh, and then the problem should go away.

Yes, you're right about the code source. 8)

Thanks, I'll give this a try tomorrow at work where my Oracle server is.
Your explanation makes a great deal of sense, though.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Tim Marshall]

David W. Fenton wrote:

If you're using dbLocal(), then USE IT FOR EVERYTHING, as long as
you want to use the front end database.

Yup, that solved it. Thanks for the pointer - you're right, I had
messed things up by not being consistent in the use of dblocal.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me