为了账号安全,请及时绑定邮箱和手机立即绑定

代码遍历MS Access中的所有记录

代码遍历MS Access中的所有记录

FFIVE 2019-12-15 12:12:03

我需要一个代码来遍历表中的所有记录,以便提取一些数据。除此之外,是否还可以遍历过滤的记录并再次提取数据?谢谢!

查看完整描述

3 回答

?
幕布斯6054654

TA贡献1322条经验 获得超7个赞

找到了一个很好的代码,并带有解释每个语句的注释。在-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

记录集在遍历数据时具有两个重要的属性,EOF(文件末尾)和BOF(文件开始)。记录集就像表一样,当您遍历一个表时,实际上是按顺序从一个记录移到另一个记录。当您在记录中移动时,EOF属性设置为false,但在尝试通过最后一条记录后,EOF属性变为true。反之,BOF属性的工作原理与此相反。


这些属性使我们知道何时达到记录集的限制。



查看完整回答
反对 回复 2019-12-16
  • 3 回答
  • 0 关注
  • 365 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信