How do I run a query from a VBA editor window in Access?

Question

I would like to do something like this:

DoCmd.OpenQuery "select * from some_table;"

How can I do it?

Please keep in mind, I do not want to create a new query, save it and run it. I just want to inject a select statement into VBA code and run it.

+2

2 answers

You can run a query to retrieve a single value with the following:

Set rst = CurrentDB.OpenRecordset("Select Max(myCol) FROM myTable")
myValue = rst(0).Value

Which you can shorten to one line like this:

lngValue = CurrentDB.OpenRecordset("Select Max(myCol) FROM myTable")(0)

+1

voon May 14 '10 at 22:08

Heinzi · Accepted Answer · 2010-05-14T22:07:43+0000

If you just want to get the maximum value, DMax-Function should do the trick:

myVariable = DMax("fee", "courses", "region = 'UK'")

(matches SELECT MAX(fee) FROM courses WHERE region = 'UK'

).