Access 2010 VBA 查询表并遍历结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4794727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access 2010 VBA query a table and iterate through results
提问by Sally
I have a query that I want to execute against a table. With the results I want to do something. In my head the pseudo code is:
我有一个要对表执行的查询。有了结果，我想做点什么。在我的脑海中，伪代码是：
var q = "select * from table where some condition";
var results = db.getResults(q);
foreach (row r in results )
do something with result
How would I so something similar with vba?
我如何与 vba 如此相似？
回答by Fionnuala
DAO is native to Access and by far the best for general use. ADO has its place, but it is unlikely that this is it.
DAO 是 Access 的原生版本，目前最适合一般用途。ADO 有它的位置，但不太可能就是这样。
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL as String
Set db=CurrentDB
strSQL = "select * from table where some condition"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
rs.Edit
rs!SomeField = "Abc"
rs!OtherField = 2
rs!ADate = Date()
rs.Update
rs.MoveNext
Loop
回答by XIVSolutions
I know some things have changed in AC 2010. However, the old-fashioned ADODB is, as far as I know, the best way to go in VBA. An Example:
我知道在 AC 2010 中有些事情发生了变化。但是，据我所知，老式的 ADODB 是使用 VBA 的最佳方式。一个例子：
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim colReturn As New Collection
Dim SQL As String
SQL = _
"SELECT c.ClientID, c.LastName, c.FirstName, c.MI, c.DOB, c.SSN, " & _
"c.RaceID, c.EthnicityID, c.GenderID, c.Deleted, c.RecordDate " & _
"FROM tblClient AS c " & _
"WHERE c.ClientID = @ClientID"
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
With cn
.Provider = DataConnection.MyADOProvider
.ConnectionString = DataConnection.MyADOConnectionString
.Open
End With
With cmd
.CommandText = SQL
.ActiveConnection = cn
Set prm = .CreateParameter("@ClientID", adInteger, adParamInput, , mlngClientID)
.Parameters.Append prm
End With
Set rs = cmd.Execute
With rs
If Not .EOF Then
Do Until .EOF
mstrLastName = Nz(!LastName, "")
mstrFirstName = Nz(!FirstName, "")
mstrMI = Nz(!MI, "")
mdDOB = !DOB
mstrSSN = Nz(!SSN, "")
mlngRaceID = Nz(!RaceID, -1)
mlngEthnicityID = Nz(!EthnicityID, -1)
mlngGenderID = Nz(!GenderID, -1)
mbooDeleted = Deleted
mdRecordDate = Nz(!RecordDate, "")
.MoveNext
Loop
End If
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
回答by XIVSolutions
Ahh. Because I missed the point of you initial post, here is an example which also ITERATES. The first example did not. In this case, I retreive an ADODB recordset, then load the data into a collection, which is returned by the function to client code:
啊。因为我错过了你最初的帖子，这里有一个例子，它也是迭代的。第一个例子没有。在这种情况下，我检索 ADODB 记录集，然后将数据加载到集合中，该集合由函数返回给客户端代码：
EDIT: Not sure what I screwed up in pasting the code, but the formatting is a little screwball. Sorry!
编辑：不确定我在粘贴代码时搞砸了什么，但格式有点乱。对不起！
Public Function StatesCollection() As Collection
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim colReturn As New Collection
Set colReturn = New Collection
Dim SQL As String
SQL = _
"SELECT tblState.State, tblState.StateName " & _
"FROM tblState"
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
With cn
.Provider = DataConnection.MyADOProvider
.ConnectionString = DataConnection.MyADOConnectionString
.Open
End With
With cmd
.CommandText = SQL
.ActiveConnection = cn
End With
Set rs = cmd.Execute
With rs
If Not .EOF Then
Do Until .EOF
colReturn.Add Nz(!State, "")
.MoveNext
Loop
End If
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
Set StatesCollection = colReturn
End Function
结束函数
