How to execute a dynamic SQL query against MS Access 2003 through VBA?

Question

This is a super basic question, but I'm trying to execute a query that I create using some form values ​​in the MS Access database in which the form is located. I don’t think I need to go through ADO formally, but maybe I do.

In any case, some help would be appreciated. Sorry for being n00b .;)

Answer 1

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

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 text field in the form.

Answer 2

This is what I came 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 3

, , , , DAO. , , . , :

  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

, :

  UPDATE MyTable SET Field = "New Data"

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

, , SQL ( / ).

Answer 4

ADO:

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 ADO . DAO ADO. , , . , . ADO .