Simple Access Query using VBA

[joave]

Hi:
This might seem quite simplistic, but I need to know how to set up a
simple query so it will work with VBA. I do not want to use a previously
stored query. For example:

SELECT * FROM BlahTable
WHERE name = "Dave Smith"
ORDER BY number;

I have researched this and have not come up with anything except that DAO
probably needs to be used and that SELECT queries don't work with
DoCMD.RunQuery. I am completely new to combining VBA and SQL.

Ultimately, I need to take the name field and reverse the last name and
first name so that it matches another table's properties for an INNER JOIN
query. I know how to do the string manipulation in VB but need to do this in
VBA for the SQL query.

Any help would be appreciated.

Thank you,

Dave

 

[Piet Linden]

Hi:
  This might seem quite simplistic, but I need to know how to set up a
simple query so it will work with VBA. I do not want to use a previously
stored query. For example:

SELECT * FROM BlahTable
WHERE name = "Dave Smith"
ORDER BY number;

I have researched this and have not come up with anything except that DAO
probably needs to be used and that SELECT queries don't work with
DoCMD.RunQuery. I am completely new to combining VBA and SQL.

Ultimately, I need to take the name field and reverse the last name and
first name so that it matches another table's properties for an INNER JOIN
query. I know how to do the string manipulation in VB but need to do thisin
VBA for the SQL query.

Any help would be appreciated.

Thank you,

Dave

One question: WHY?
You can open saved queries in VBA...

dim rs as dao.recordset
dim qdf as dao.querydef
set qdf=dbengine(0)(0).Querydefs("SomeSelectQuery")
set rs=qdf.openrecordset

or
dim strSQL as string
dim rs as dao.recordset

strsql="SELECT...."
set rs=DbEngine(0)(0).OpenRecordset(strsql, dbopensnapshot)
....