Code to list queries in a db? Possible? 2

[CptCrusty1]

I just had a wild idea to build a form (table) that would allow me to keep a log of the changes I've made to the various queries, etc., in a particular db. My problem arrises in the fact that I have over 300 quereis in this db, plus I periodicaly add/delete queries. I want a list box or combo box to show me all the lovely Pain-In-My-Backside queries so that I can select which one I modify. Is this something to involve THOUSANDS of lines of code or something short and spiffy?

Thanks
Crusty.

I live to work and I work to live.

 

[Golom]

Try

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5));

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[CptCrusty1]

oooooh.... thats slick! Where do I find the properties for that spiffy little tidbit? I'd like to explore it a bit....

I live to work and I work to live.

 

[Golom]

Select "Tools / Options" and check "System Objects" in the "Show" frame. Several system tables beginning "MSys..." will then appear in the tables collection. Enjoy.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[CptCrusty1]

Oh I enjoy very much... . Its like a big window has been opened..... LOL

Will one of these magic tables tell me the type of the query? The users that built this quagmire were not very good about Naming Conventions.

Crusty.

I live to work and I work to live.

 

[Golom]

Not really but you can use a bit of DAO code

Dim qdf As QueryDef
For Each qdf in CurrentDB.QueryDefs
   Select Case UCase(Left(qdf.SQL,3))
      Case "SEL":       QueryType = "Select"
      Case "INS":       QueryType = "Insert"
      Case "UPD":       QueryType = "Update"
      Case "CRE":       QueryType = "Create"
      Case "ALT":       QueryType = "Alter"
      Case "DEL":       QueryType = "Delete"
   End Select
   If QueryType = "Select" Then
      If Instr(1, qdf.SQL, "INTO") Then QueryType = "Make Table"
   End If
Next

You will probably need to be a bit fancier if you have PARAMETER queries that specify a PARAMETERS clause at the beginning of the SQL.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

Golom, instead of trying to parse the QueryDef.SQL, why not simply test the QueryDef.Type property ?
Select Case qdf.Type
Case dbQAction: QueryType = "Action"
Case dbQAppend: QueryType = "Append"
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

PHV, can you elaborate on this in SQL form?

I live to work and I work to live.

 

[PHV]

In a standard code module create the following function:

Public Function basQueryType(ObjType As Long) As String
Dim tmp As String
Select Case ObjType
Case 0
  tmp = "Select"
Case 16
  tmp = "Crosstab"
Case 32
  tmp = "Delete"
Case 48
  tmp = "Update"
Case 64
  tmp = "Append"
Case 80
  tmp = "MakeTable"
Case 96
  tmp = "DDL"
Case 112, 144
  tmp = "PassThrough"
Case 128
  tmp = "Union"
Case 160
  tmp = "Compound"
Case 224
  tmp = "Procedure"
Case 240
  tmp = "Action"
Case Else
  tmp = "Unknown"
End Select
basQueryType = tmp
End Function

And now your RowSource:
SELECT Name, basQueryType([Flags]) AS QueryType
FROM MsysObjects
WHERE Type=5 AND Left([Name],1)<>'~'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CptCrusty1]

Is there way to tell when the last time a query was run? Not Modified or created, but actually run?

I live to work and I work to live.