Queries in a table????

[pdbowling]

Hi all.
Is there a way to store queries in a table?.... the
query_name.dqy files???? blobs or anything?

I can't store just text because it only allows 60 characters and most of my queries are quite complex. Is there another alternative to text not in the drop down list?

Read on if you want more background on why.

(... there's a report that I wrote that mines out and graphs lots of data, I use a table whose Primary Key is DeptCode. I wrote a query for each deptCode and have it refreshed in a spreadsheet when appropriate. Now the boss wants to be able to create new entries in DeptCode and have the program create a query that will add it to the report..... I guess he wants to not need me eh??? LOL )
PB

 

[raskew]

This will probably give you more than you need to know but it'll be a starting point.

Function GetQueries()
'*******************************************
'Name:      GetQueries (Function)
'Purpose:   Creates tblQueries and populates with query info.
'*******************************************

Dim db As DATABASE
Dim qd As QueryDefs
Dim found As Boolean, test As String
Dim rs As Recordset, tdesc As String
Dim tName As String, tfield As String, tsource As String
Dim n As Integer, i As Integer, fcount As Integer
Dim x As Integer
Dim qSQL

Set db = CurrentDb
Set qd = db.QueryDefs

On Error Resume Next
tName = "tblQueries"
'Does table "tblQueries" exist?  If true, delete it;
found = False
test = db.TableDefs(tName).Name
If Err <> 3265 Then
  found = True
  docmd.DeleteObject acTable, "tblQueries"
End If
'Create new tblTable
'db.Execute "CREATE TABLE tblQueries(Object TEXT (55), FieldName TEXT (55)," _
' & " RecordSource TEXT (55));"
db.Execute "CREATE TABLE tblQueries(ObjectID LONG, " _
 & " Type TEXT (55), Object TEXT (55), Description TEXT (55), RecordSource TEXT (55), FieldName TEXT (55), QuerySQL MEMO);"
 
Set rs = db.OpenRecordset("tblQueries")

'find out how many queries there are in the database
n = qd.Count

'loop thru each of the querydefs to capture the query
'name, field names and the source table (or query)
'for the particular field
For i = 0 To n - 1
   tdesc = ""
   tName = qd(i).Name
   tdesc = qd(i).Properties("Description")
   fcount = qd(i).Fields.Count
   For x = 0 To fcount - 1
      If Left(tName, 1) <> "~" And Len(tName) > 0 Then
      rs.AddNew
      
      tfield = qd(i).Fields(x).Name
      tsource = qd(i).Fields(x).SourceTable
      qSQL = qd(i).SQL
      'tsource = qd(i).Fields(x).ForeignName
      rs!ObjectID = 2
      rs!Type = "Query"
      rs!Object = tName
      rs!fieldName = tfield
      rs!RecordSource = tsource
      rs!Description = tdesc
      rs!QuerySQL = qSQL
      rs.Update
      End If
      'debug.Print tname & ", " & tfield & ", " & tsource
   Next x
Next i

rs.Close
db.Close
Set qd = Nothing
Set db = Nothing
     
End Function

 

[KevinM]

Surely you don't need to create a new query for every new dept code

Why not simply use a parameter query for the dept code or better still have the query based on a form where the user selects a dept code from a combo or list box.