Adding multiple rows to ADO recordset using VBA in Access 2007

Posted on 2012-09-19
Medium Priority
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
  • 2
LVL 19

Accepted Solution

Ken Butters earned 2000 total points
ID: 38414096
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

ID: 38414358
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
ID: 38414406
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

621 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