如何通过VBA对msaccess2003执行动态SQL查询?

可以使用以下DAO代码查询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

在我的例子中,ClassID是表单上的一个文本框。

这就是我最终得出的结论。

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

这里只是为了以防万一你想要一个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可以获得更好的性能。在我遇到的大多数access数据库应用程序中,它确实没有什么不同。当我看到一个很大的区别是链接表。ADO通常性能更好。

您已经给出了答案,并且您似乎正在接受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'"