Adding multiple rows to ADO recordset using VBA in Access 2007

Posted on 2012-09-19
Last Modified: 2012-09-19

I am likely missing something pretty obvious but here goes.  I am attempting to create an ADO disconnected recordset that contains the File Name and Date Created Data from a folder on our server.  I am also attempting to create a column in the recordset for indexing called File count as well as adding two boolean columns for use later when the user would like to print and / or email / fax the document.

The code loops through the files properly and the recordset appears to have the correct file count but each row in the recordset appears to always be the File Name and Date Created data from the first file.  The data from the next file(s) are not added to the array.  Also, the FileNID on this first row in the recordset is 0 which doesn't make sense as the FileCount variable should be 1.

My thinking is that perhaps there is a mismatch on data type either on the FileNID index or on Date.  However, the first row in the rst appears correct, except for the FileNID being incorrect, so I just don't know where I have screwed up.  Maybe I don't need the FileNID column and should eliminate it.


    Dim fso As Object
    Dim FromPath As String
    Dim FileName As String
    Dim FullPath As String
    Dim FileDate As Date
    Dim FileInFromFolder As Object

    Dim FileCount As Integer
    Dim FileNID As Integer
    Dim Prt As Boolean
    Dim Fx As Boolean
    Dim strMsg As String
    Dim strPatientID As String
    Dim iResponse As Integer
    Dim rst2 As ADODB.Recordset
    FileCount = 0

    On Error GoTo ErrHandler
    FromPath = DLookup("[PatientFolderPath]", "tblPathNames", 1)
    strPatientID = Forms!frmPatientDemographicData!PatientID
    FromPath = FromPath & strPatientID

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    Set rst2 = New ADODB.Recordset
    With rst2
        Set .ActiveConnection = Nothing
        .CursorLocation = adUseClient
        With .Fields
            .Append "FileNID", adInteger
            .Append "FileName", adVarChar, 255
            .Append "FileDate", adDate
            .Append "Prt", adBoolean
            .Append "Fx", adBoolean
        End With

    Set fso = CreateObject("scripting.filesystemobject")
    For Each FileInFromFolder In fso.getfolder(FromPath).Files
        FileName = (FileInFromFolder.Name)
        FileDate = (FileInFromFolder.DateLastModified)

            FileCount = FileCount + 1
            FullPath = FromPath & FileName
        .AddNew Array("FileNID", "FileName", "FileDate", "Prt", "Fx"), _
             Array(FileCount, FullPath, FileDate, 0, 0)
    Next FileInFromFolder
        Do Until .EOF
            MsgBox "FileNID = " & FileNID & "  FileName =" & FullPath & "  FileDate =" & FileDate & "  Print =" & Prt & "  Fax = " & Fx
    End With
Question by:isurgyn
    LVL 19

    Accepted Solution

    You are doing everything correctly... except the loop for the message box... you are not displaying values from the record set in that loop...

    try this loop instead:

            Do Until .EOF
                MsgBox "FileNID = " & .Fields(0) & "  FileName =" & .Fields(1) & "  FileDate =" & .Fields(2) & "  Print =" & .Fields(3) & "  Fax = " & .Fields(4)

    Open in new window


    Author Closing Comment

    Might be the easiest points you ever got but THANK YOU!  This is my first attempt a creating a rst from scratch and just didn't think about Msgbox syntax.
    LVL 19

    Expert Comment

    by:Ken Butters
    Lol... well the final code change was easy... but the process was not as quick... copying all the code into Access... accounting for missing routines / tables etc... getting set up to trace...finding the right libraries to include,  tracing through each step... and arriving at the bottom and scratching my head for a few minutes....saying... what the what?  


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now