sql Query in VBA code

Question

If I want to code the following in VBA how do I do it

QUERY1:
SELECT field1, Min(field4) AS MinField4, Max(field5) AS MaxField5
FROM Table1
GROUP BY field1;


SELECT Query1.field1, Table1.field2, Table1.field3, Query1.MinField4,
       Query1.MaxField5
FROM   Query1 INNER JOIN Table1 ON (Query1.field1 = Table1.field1) AND
       (Query1.MinField4 = Table1.field4) AND
       (Query1.MaxField5 = Table1.field5);

I know for executing the SQL I store it as as string and write run SQL. but how do I code storing query1 as a persistent object that can be referenced in other SQL statements?

Answer 1

I take it your question is "How do I create a new query in Microsoft Access using code?"

There are two solutions:

1. Microsoft Access will accept the DDL statement CREATE VIEW. So you can construct that statement in code and then execute it against the database via OLE DB e.g. ADO in VBA code.
2. The database object in MS Access contains a collections property called QueryDefs and you can access that to manipulate (and create) queries stored in the database.

Answer 2

you could just create a query and paste that SQL into the SQL View (available from the query design window).

Answer 3

Here some code that will fill up a dataset with your results

Dim cnn As ADODB.Connection
Dim recordset As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "SELECT blah ..."
recordset.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
'do what you want now.

oh yeah its been a while but you probably want to clean up too

Set recordset = Nothing
'etc..