You will need to set the DAO 3.6 reference for this code to work. Replace my
"Customer" table with your table.
Hope this helps...
Public Sub CreateQueries()
'*******************************
'Lonnie Johnson, 7/3/08
'ProDev, www.prodev.us
'*******************************
On Error GoTo ErrHandle 'error handling in case you run it more than once to
delete prior queries
Dim db As DAO.Database 'virtual database object
Dim tbl As DAO.TableDef 'variable to hold the table object
Dim fld As DAO.Field 'variable to represent each field in your table
Dim qry As DAO.QueryDef 'query object to create new queries
Dim strSQL As String 'storage for query SQL
Set db = CurrentDb 'set database object to the current database
Set tbl = db.TableDefs("Customer") 'set the table object to your table name
For Each fld In tbl.Fields 'iterate through each field in the fields
collection of the table
'the next step sets the SQL query syntact to select the field name and
order by the field name _
by concantenating a SELECT string with the name property of the field
object as we iterate
strSQL = "SELECT " & fld.Name & " FROM Customer ORDER BY " & fld.Name
'we now set our query object's name property to the field name and also
set the SQL property to our SQL string variable
Set qry = db.CreateQueryDef("qry" & fld.Name, strSQL)
Set qry = Nothing 'after the query is created we clear the query
variable for the next field name
Next fld 'iterate
Exit Sub 'exit before getting to our err handle
ErrHandle:
If Err.Number = 3012 Then 'if we raise the specific error number indicating
that the query already exist, then we delete it
DoCmd.DeleteObject acQuery, "qry" & fld.Name
Resume 'resume what we were doing
Else 'if this is another error, then we show the message to the user
MsgBox Err.Number & vbCrLf & Err.Description
End If
End Sub
May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us
--- On Thu, 7/3/08, Clauss Gilbert <[EMAIL PROTECTED]> wrote:
From: Clauss Gilbert <[EMAIL PROTECTED]>
Subject: [ms_access] Automatising building of 200 queries
To: [email protected]
Date: Thursday, July 3, 2008, 5:04 AM
Hello,
I would like to automatise the building (and saving) of 200 queries.
The queries are based on one table only.
They differ from each other only by the name of the field that is
selected.
I must make one query on each field, such as "select field1 from MyTable
order by field1" , field1 being a sort of parameter.
How could i do that :
1) put the name of each field in an array
2) browse the array from beginning to end so that the program builds the
SQL query corresponding to the name of the field (i have a SQL template
of the query, one character string is missing : the name of the field)
3) save the query as [name of the field]
I have the algorithm, but i am not able to transform it into VBA...
Thank you for help, suggestions, or links to help pages on the web
Gilbert Clauss
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
