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?

Since VBA is not just Access (and becuase the solution will possibly not be portable to other uses of VBA), it should be reflected in the question. This is an important limitation, not just a category extension. — Michael Paulukonis
– Michael Paulukonis, Commented Oct 13, 2009 at 16:18

Johnno Nolan · Accepted Answer · 2009-10-13 14:24:10Z

1

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

answered Oct 13, 2009 at 14:24

Johnno Nolan

29.7k19 gold badges114 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David-W-Fenton Over a year ago

In Access/VBA, it's not necessary to cleanup after ADO (though it's certainly good housekeeping to at least close your recordset). It's only DAO that had the reference counting problems, and it seems to me from what I've read that they are substantially lessened these days in comparison to the A97 time frame.

onedaywhen Over a year ago

@David W. Fenton: Could be a good idea to call the Connection's Close method at the earliest opportunity when done but hard to generalize really. Also note that you sometimes need to set an ADO Catalog to Nothing in order to close the connection and release from connection pooling etc.

onedaywhen · Accepted Answer · 2009-10-15 13:23:29Z

1

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

There are two solutions:

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

edited Oct 15, 2009 at 13:23

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Oct 13, 2009 at 14:09

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

6 Comments

Michael Paulukonis Over a year ago

I don't see where the poster wants to use Access; VBA is also used as a scripting language for 3rd-party applications, like FormWare or InputAccel.

Johnno Nolan Over a year ago

@OtherMicheal there is an access tag.

Larry Lustig Over a year ago

Uhh. . . Seeing as I found the question by clicking on the Access tag-of-interest, and seeing as how the user tagged his or her question "Access", and seeing as how the question references the RunSQL method of the DoCmd object, I thought (and still think) it is reasonable to assume that he or she is using Access.

Michael Paulukonis Over a year ago

Hrm. so there is -- completely missed it, since I was focusing on the title and question.

onedaywhen Over a year ago

@Larry Lustig: Doesn't DoCmd.RunSQL use ANSI-89 Query Mode? I though "CREATE VIEW" DDL was only supported in ANSI-92 Query Mode.

|

Nick · Accepted Answer · 2009-10-13 14:14:03Z

0

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

answered Oct 13, 2009 at 14:14

Nick

3,67312 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

sql Query in VBA code

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related