How do you execute a dynamic SQL Query against MS Access 2003 via VBA?

Question

Hello Everyone,

This is a super basic question but I'm trying to execute a Query that I'm building via some form values against the MS Access database the form resides in. I don't think I need to go through ADO formally, but maybe I do.

Anyway, some help would be appreciated. Sorry for being a n00b. ;)

Answer 1

Are you building a new query (i.e. raw SQL) from scratch based on the values collected in the form, or are you trying to execute an existing parameterized query, and trying to pass the form values as parameters to the query?

Answer 2

You can use the following DAO code to query an Access DB:

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Attendance WHERE ClassID = " & ClassID)

do while not rs.EOF
  'do stuff
  rs.movenext
loop

rs.Close
Set rs = Nothing

In my case, ClassID is a textbox on the form.

Answer 3

This is what I ended up coming up with that actually works.

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDB
Set rs = db.OpenRecordset(SQL Statement)

While Not rs.EOF
    'do stuff
Wend

rs.Close

Answer 4

The answers you've been given and that you seem to be accepting loop through a DAO recordset. That is generally a very inefficient method of accomplishing a text. For instance, this:

  Set db = CurrentDB()
  Set rs = db.OpenRecordset("[sql]")
  If rs.RecordCount > 0
     rs.MoveFirst
     Do While Not rs.EOF
       rs.Edit
       rs!Field = "New Data"
       rs.Update
       rs.MoveNext
     Loop 
  End If
  rs.Close
  Set rs = Nothing
  Set db = Nothing

will be much less efficient than:

  UPDATE MyTable SET Field = "New Data"

which can be run with:

  CurrentDb.Execute "UPDATE MyTable SET Field = 'New Data'"

It is very seldom the case that one needs to loop through a recordset, and in most cases a SQL update is going to be orders of magnitude faster (as well as causing much shorter read/write locks to be held on the data pages).

--
David W. Fenton
David Fenton Associates

Answer 5

Here just in case you wanted an ADO version:

Dim cn as new ADODB.Connection, rs as new ADODB.RecordSet
Dim sql as String

set cn = CurrentProject.Connection
sql = "my dynamic sql string"

rs.Open sql, cn ', Other options for the type of recordset to open, adoOpenStatic, etc.

While Not rs.EOF
  'do things with recordset
  rs.MoveNext   ' Can't tell you how many times I have forgotten the MoveNext. silly.
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

DAO and ADO are very close in usage. You get more control with DAO and slightly better performance with ADO. In most access database applications I have come across it really doesn't make a difference. When I have seen a big difference is with linked tables. ADO often performs better.