CODEWITHSUNDEEP
ms-accessvba
tksy
tksy

Reputation: 3529

sql Query in VBA code

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?

Upvotes: 0

Views: 1678

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 1

Johnno Nolan
Johnno Nolan

Reputation: 29659

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..

Upvotes: 1

Nick
Nick

Reputation: 3643

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

Upvotes: 0

Related Questions