In which database is my query?

[rquintal]

Situation: a directory full of .mdb files. Need to find a routine
that generates a list of all the queries in each .mdb.

Anybody know of such a beast?

[Phil Stanton]

This actually creates a table of queries and descriptions (I omit Action
queries (Delete, Append & Update, but just remove the appropriate lines)

The TblQueries is
QueryID Auto
QueryName Text
QueryVersion Long ' You dont need this
QueryDescription Text
ParameterUsed Yes.No ' You dont need this either
Function PopulateTblQueries(Version As Long) ' Populate TblQueries
with all queries

Dim Mydb As Database
Dim QuerySet As Recordset, ParamSet As Recordset
Dim i As Integer
Dim QueryName As String, SQLStg As String, Criteria As String
Dim ParamUsed As Boolean

Set Mydb = CurrentDb

SQLStg = "SELECT TblQueries.* FROM TBLQueries"
Set QuerySet = Mydb.OpenRecordset(SQLStg)

On Error GoTo PopulateTblQueries_Err

For i = 0 To Mydb.QueryDefs.Count - 1
If Left(Mydb.QueryDefs(i).Name, 4) <"~sq_" _
And Left(Mydb.QueryDefs(i).Name, 6) <"Append" _
And Left(Mydb.QueryDefs(i).Name, 6) <"Delete" _
And Left(Mydb.QueryDefs(i).Name, 6) <"Update" Then
QueryName = Mydb.QueryDefs(i).Name
Criteria = "QueryName = '" & QueryName & "'"

SQLStg = "SELECT DISTINCT TblQueries.* "
SQLStg = SQLStg & "FROM TblQueries INNER JOIN TblParameters "
SQLStg = SQLStg & "ON TblQueries.QueryID = TblParameters.QueryID "
SQLStg = SQLStg & "WHERE TblQueries.QueryName = '" & QueryName &
"';"
Set ParamSet = Mydb.OpenRecordset(SQLStg)
If ParamSet.RecordCount 0 Then
ParamUsed = True
Else
ParamUsed = False
End If
ParamSet.Close
Set ParamSet = Nothing

With QuerySet
.FindFirst Criteria
If .NoMatch Then
.AddNew
Else
.Edit
End If
!QueryName = Mydb.QueryDefs(i).Name
!QueryVersion = Version
!QueryDescription =
Mydb.QueryDefs(i).Properties("Description")
If ParamUsed = True Then
!ParametersUsed = True
Else
!ParametersUsed = False
End If
.Update
End With
End If
NextQuery:
Next i

Me.Requery

Exit Function

PopulateTblQueries_Err:
If Err = 3270 Then
Resume Next ' Description property not found
Else
MsgBox Err.Description
End If

End Function

HTH

Phil
"rq******@sympatico.ca" <bo*********@gmail.comwrote in message
news:11*********************@w3g2000hsg.googlegrou ps.com...

Situation: a directory full of .mdb files. Need to find a routine
that generates a list of all the queries in each .mdb.

Anybody know of such a beast?

[Bob Quintal]

"Larry Linson" <bo*****@localhost.notwrote in
news:TVQwi.13216$jy5.11681@trnddc07:

It appears that you've used Phil's and Tom's suggestions and
solved the problem.

From your description of the situation, I suspect that was the
best approach.

Oh, unless it qualified you for an upgrade (and I'm not
familiar enough with FMS' pricing policies to remember, if I
ever did know) that Access 97 Total Access Analyzer wouldn't
be useful on later versions.

Larry Linson

Once I had a pointer to the opendatabase method, It wasn't hard.
MS-Access is so rich in tools that one can never know all of
them.

The benefit of this newsgroup is the ability to get information
by someone who does know what to do in a specific situation, so
collectively, we do know all of the tools.

Have a good day.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com