vba 循环遍历 MS Access 中所有记录的代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5864160/
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
Code to loop through all records in MS Access
提问by Ali
I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!
我需要一个代码来遍历表中的所有记录，以便我可以提取一些数据。除此之外，是否还可以循环过滤记录并再次提取数据？谢谢！
回答by HK1
You should be able to do this with a pretty standard DAO recordset loop. You can see some examples at the following links:
http://msdn.microsoft.com/en-us/library/bb243789%28v=office.12%29.aspx
http://www.granite.ab.ca/access/email/recordsetloop.htm
您应该能够使用非常标准的 DAO 记录集循环来完成此操作。您可以在以下链接中看到一些示例：
http://msdn.microsoft.com/en-us/library/bb243789%28v=office.12%29.aspx
http://www.granite.ab.ca/access /email/recordsetloop.htm
My own standard loop looks something like this:
我自己的标准循环看起来像这样：
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts")
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True
'Perform an edit
rs.Edit
rs!VendorYN = True
rs("VendorYN") = True 'The other way to refer to a field
rs.Update
'Save contact name into a variable
sContactName = rs!FirstName & " " & rs!LastName
'Move to the next record. Don't ever forget to do this.
rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
回答by Alex
In "References", import DAO 3.6 object reference.
在“参考”中，导入 DAO 3.6 对象参考。
private sub showTableData
dim db as dao.database
dim rs as dao.recordset
set db = currentDb
set rs = db.OpenRecordSet("myTable") 'myTable is a MS-Access table created previously
'populate the table
rs.movelast
rs.movefirst
do while not rs.EOF
debug.print(rs!myField) 'myField is a field name in table myTable
rs.movenext 'press Ctrl+G to see debuG window beneath
loop
msgbox("End of Table")
end sub
You can interate data objects like queries and filtered tables in different ways:
您可以通过不同方式交互查询和过滤表等数据对象：
Trhough query:
通过查询：
private sub showQueryData
dim db as dao.database
dim rs as dao.recordset
dim sqlStr as string
sqlStr = "SELECT * FROM customers as c WHERE c.country='Brazil'"
set db = currentDb
set rs = db.openRecordset(sqlStr)
rs.movefirst
do while not rs.EOF
debug.print("cust ID: " & rs!id & " cust name: " & rs!name)
rs.movenext
loop
msgbox("End of customers from Brazil")
end sub
You should also look for "Filter" property of the recordset object to filter only the desired records and then interact with them in the same way (see VB6 Help in MS-Access code window), or create a "QueryDef" object to run a query and use it as a recordset too (a little bit more tricky). Tell me if you want another aproach.
您还应该查找记录集对象的“过滤器”属性以仅过滤所需的记录，然后以相同的方式与它们交互（参见 MS-Access 代码窗口中的 VB6 帮助），或创建一个“QueryDef”对象来运行查询并将其用作记录集（有点棘手）。告诉我你是否想要另一种方法。
I hope I've helped.
我希望我有所帮助。
回答by Adarsh Madrecha
Found a good code with comments explaining each statement. Code found at - accessallinone
找到了一个很好的代码，其中包含解释每个语句的注释。代码位于 - accessallinone
Sub DAOLooping()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "tblTeachers"
'For the purposes of this post, we are simply going to make
'strSQL equal to tblTeachers.
'You could use a full SELECT statement such as:
'SELECT * FROM tblTeachers (this would produce the same result in fact).
'You could also add a Where clause to filter which records are returned:
'SELECT * FROM tblTeachers Where ZIPPostal = '98052'
' (this would return 5 records)
Set rs = CurrentDb.OpenRecordset(strSQL)
'This line of code instantiates the recordset object!!!
'In English, this means that we have opened up a recordset
'and can access its values using the rs variable.
With rs
If Not .BOF And Not .EOF Then
'We don't know if the recordset has any records,
'so we use this line of code to check. If there are no records
'we won't execute any code in the if..end if statement.
.MoveLast
.MoveFirst
'It is not necessary to move to the last record and then back
'to the first one but it is good practice to do so.
While (Not .EOF)
'With this code, we are using a while loop to loop
'through the records. If we reach the end of the recordset, .EOF
'will return true and we will exit the while loop.
Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName")
'prints info from fields to the immediate window
.MoveNext
'We need to ensure that we use .MoveNext,
'otherwise we will be stuck in a loop forever…
'(or at least until you press CTRL+Break)
Wend
End If
.close
'Make sure you close the recordset...
End With
ExitSub:
Set rs = Nothing
'..and set it to nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub
Recordsets have two important properties when looping through data, EOF (End-Of-File) and BOF (Beginning-Of-File). Recordsets are like tables and when you loop through one, you are literally moving from record to record in sequence. As you move through the records the EOF property is set to false but after you try and go past the last record, the EOF property becomes true. This works the same in reverse for the BOF property.
记录集在循环数据时有两个重要的属性，EOF（文件结尾）和 BOF（文件开头）。记录集就像表一样，当您循环遍历一个表时，您实际上是在按顺序从一个记录移动到另一个记录。当您在记录中移动时，EOF 属性设置为 false，但在您尝试通过最后一条记录后，EOF 属性变为 true。对于 BOF 属性，这反过来也是一样的。
These properties let us know when we have reached the limits of a recordset.
这些属性让我们知道何时达到了记录集的限制。
