Programatically Create/Save a Query...

  • Thread starter Thread starter James Hunter Ross
  • Start date Start date
J

James Hunter Ross

This might not be the right group for this, but...

Is there any way to programatically create a query. My client has MSWord
documents that his (very unsophisticated) staff use for mail merge. The
document refers to an MSAccess query that must be hand-edited prior to the
merge. He would prefer that a simple dialog interface create/save the query
so the document can then use it. I can create the SQL, that's easy; I just
need to save the SQL so some external program can refer to it. (Forms and
Reports can be manipulated programatically, but the queries don't seem to be
in a collection that I can modify.

An alternative would certainly be welcome too; possibly "pushing" the
current reocrdset (or ReocrdsetClone) of a form into an MSWord mail merge,
or something.

Any words or advice will be very much appreciated. Thanks in advance for
your time!

Sincerely,

James Hunter Ross
(e-mail address removed)
 
Check Access VB Help on the QueryDef object and CreateQueryDef Method. You
will need to add the DAO 3.6 Library into the References Collection of you
database.
 
Yes, Van, that did help. Thanks very much for your reply.

I'm off and running. My ultimate goal would be to capture the query text
(even if filter by forms was used) for the current form, and place that SQL
into the QueryDef. I would have a more general solution if I could do that.
Then users could prepare their recordset any way they felt comfortable,
(typically using our forms and built-in filter stuff), then save the SQL so
it could be referenced by an external program such as MSWord.

Thanks again for the help. If you ever need SQL Server or C++ help,
hopefully I can deliver.

James
 
You may want to check out the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
solution allows users to create queries/reports by saving their SQL
definitions in a couple tables. Users select the fields, set criteria, maybe
add grouping,... and press a command button. The results are displayed in a
datasheet subform. The main form has option buttons to send the results to
Print, Excel, Word table, CSV, Word Merge, HTML, or Graph.

The developer only has to import the two tables and about 6 forms into their
mdb. Then create several saved queries that join the related tables and
possibly alias the field names.
 
Here is a sample of how to re-create a query object in code:

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
'strCriteria = (some condition that you choose)

strSQL = "SELECT table1.field1, table1.field2 "
strSQL = strSQL & "FROM table1 "
strSQL = strSQL & "WHERE (" & strCriteria & ");"

If QueryExists("MyQuery") = True Then
dbs.QueryDefs.Delete "MyQuery"
End If
Set qdf = dbs.CreateQueryDef("MyQuery")
qdf.SQL = strSQL

DoCmd.SetWarnings False
DoCmd.OpenQuery "MyQuery"
DoCmd.SetWarnings True
MsgBox ("This is a different query than the original one!!")

Set dbs = Nothing

The function QueryExists should be placed in a standard module.
It is used to test if a given query exists in the database.

Function QueryExists(QueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(QueryName))
End Function
 
Back
Top