Doing queries in VBA

[Guest]

Pretty simple question. Does anyone have a simple example for retrieving
data from a table in Access using VBA? I realize there are functions which
let you do different things with table data, metadata, etc. What I am
looking for is a simple example for how you would execute a SQL statement in
a VBA module. I'll give an example:

select count(*) from table1 where buyDate > '06/01/2005'

So, how would I execute the above statement so that I could set a variable
in my module equal to the result? I have read about DAO and ADO, but they
both seem like overkill for a query in the Access project containing the
table I am interested in. Thanks in advance.

 

[Douglas J. Steele]

Actually, you don't run Select queries in VBA. You either need to open a
recordset (hence DAO or ADO), or you can use a Domain function DCount("*",
"Table1", "buyDate > #06/01/2005#")

Note that dates are always delimited with # characters in Access (and will
always be interpretted as being in mm/dd/yyyy format, regardless of what
your Regional Settings are, unless you use an unambiguous format such as
yyyy-mm-dd or dd mmm yyyy)

 

[Guest]

Thanks Doug, that answered my question.

Actually, you don't run Select queries in VBA. You either need to open a
recordset (hence DAO or ADO), or you can use a Domain function DCount("*",
"Table1", "buyDate > #06/01/2005#")

Note that dates are always delimited with # characters in Access (and will
always be interpretted as being in mm/dd/yyyy format, regardless of what
your Regional Settings are, unless you use an unambiguous format such as
yyyy-mm-dd or dd mmm yyyy)