Simple Access Query using VBA

  • Thread starter Thread starter joave
  • Start date Start date
J

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
 
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
Click to expand...

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)
....
 
Back
Top