How to Create a Recordset from an Array

Posted on 2005-04-19
Last Modified: 2012-05-05
Hello all!

I need to create a Recordset (NO DATABASE) - Stand Alone to move though records and delete records from an Array of values.

For Example:

arrTest(0) = "A"
arrTest(1) = "B"
arrTest(2) = "C"

I want to move these values from the Array to a Recordset.

I need to loop though the Recordset and where ever I find a matching letter I need to delete it when I find a matching letter.  Once I've looped through all the records I need to write it out to a file.  The problem I was having with the Array was that if I delete an item in the middle of the Array I would have to re-number the array to loop though it later to write out the file.

Question by:smithmrk
    LVL 13

    Expert Comment

    If your problem is "re-number" arrays then use a collection:

        Dim c As New Collection
        Dim i As Long
        c.Add "A"
        c.Add "B"
        c.Add "C"
        For i = 1 To c.Count
            MsgBox "Index" + Str(i) + ", Value " + c(i)
        c.Remove 2
        MsgBox "2nd item deleted"
        For i = 1 To c.Count
            MsgBox "Index" + Str(i) + ", Value " + c(i)
    LVL 28

    Accepted Solution

    If you want to use a recordset add a reference to Microsoft ActiveX data Objects Library and try something like this:

    Private Sub Command1_Click()
    Dim rstADO As ADODB.Recordset
    Set rstADO = New ADODB.Recordset
    Dim x As Long

    With rstADO.Fields
      .Append "MyField", adVarChar, 10
    End With


    For x = 0 To UBound(arrTest)
        rstADO.Fields("MyField") = arrTest(x)
    Next x

    ' work with records

    ' clean up
    Set rstADO = Nothing

    End Sub

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 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

    13 Experts available now in Live!

    Get 1:1 Help Now