What VBA's equivalent to this VB.NET code?? (1 Viewer)

[blacksaibot]

Here is my VB.NET code:

        Dim listOfRecs As List(Of Integer) = New List(Of Integer)

        Dim queryStr As String = "SELECT DISTINCT recnum FROM t_User_Ldr"

        con.Open()

        cmd = New OleDbCommand(queryStr, con)
        Using reader As OleDbDataReader = cmd.ExecuteReader()
            While reader.Read()
                listOfRecs.add(reader.GetValue(0))
            End While
        End Using

        con.Close()

This is as far as my limited VBA knowledge gets me:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim nullDate As Boolean
    Dim queryStr As String
    Dim listOfRecords As Collection
    
    queryStr = "SELECT DISTINCT rec_num FROM tbl_UserLeader_Interviewv2_0"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(queryStr)
    
    
    With rs
        If Not rs.BOF Then
            .MoveFirst
            Do While .EOF = False
                listOfRecords.Add
                .MoveNext
            Loop
        End If
    End With

First of all, why is there no "List" in VBA??? Is a collection like a hash table or something? If so, it's not what I want. I just need a simple list to house my records.
Second, is using a recordset the correct way of going about i t?
Thank you in advance!

 

[mdlueck]

First of all, why is there no "List" in VBA??? Is a collection like a hash table or something? If so, it's not what I want. I just need a simple list to house my records.

For in-memory tables, I make use of Collection objects, via this useful link:
"Using Custom Collections in Microsoft Access"
http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

I end up creating two VBA classes, one for the *Item itself, and the second one I name *Items to be the collection.

Second, is using a recordset the correct way of going about it?

I prefer to use ADO objects when ever possible. The only things I have found I must do with DAO objects are working with QueryDef objects which are DAO only, attaching to access tables directly, and a techniqe of nesting two DAO.QueryDef objects in order to execute a pass-through query to a SQL BE DB and download the records directly to an Access FE table.

In short, ADO objects are great when you need the data to end up in VBA variables or to save VBA variables to a database.

Use DAO objects when you are interacting with Access tables and do not want the data to end up in VBA variables.

 

[blacksaibot]

and what ADO object can I use that will be like a VB.NET data reader?

 

[mdlueck]

and what ADO object can I use that will be like a VB.NET data reader?

I have not / do not use anything .Net related in my work.

I do as much as I can with ADODB.Command objects which work with ADODB.Parameters objects. Second best are ADODB.Recordset objects.

Examples:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Using VBA ADO objects to execute a Stored Procedure
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

 

[VilaRestal]

Would an array not do for your list?

 

[blacksaibot]

No. The amount of records being returned will always be unknown.

 

[VilaRestal]

VBA has dynamic arrays:

    Dim rs As DAO.Recordset
    Dim listOfRecords() As Integer
    Dim i As Integer
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT rec_num FROM tbl_UserLeader_Interviewv2_0")
    With rs
        If Not .BOF Then
            .MoveFirst
            Do While Not .EOF
                ReDim Preserve listOfRecords(i)
                listOfRecords(i) = !rec_num
                .MoveNext
                i = i + 1
            Loop
        End If
        .Close
    End With

 

[mdlueck]

VBA has dynamic arrays:

As much as I know, when you need to expand a dynamic array, doing so resets all of the prior stored records. I recall testing that with the Watches window.

For that reason I arrived at using a customized Collection, as mentioned above.

 

[VilaRestal]

You use ReDim Preserve to keep the existing values in a dynamic array when changing its dimensions. (See the example I posted.)

 

[mdlueck]

You use ReDim Preserve to keep the existing values in a dynamic array when changing its dimensions. (See the example I posted.)

I had never come across the Preserve keyword back then. Perhaps that would have worked as well.

OTOH, with Collections I can allow each attribute to be its own correct datatype, and not have to deal with translation to/from strings.

 

[VilaRestal]

Yeah collections are more powerful but a bit trickier. It's horses for courses.

 

[ChrisO]

Something else to try though there is the possibility of simply using the Recordset by itself and not needing an array at all.

Option Explicit
Option Compare Text


Public Const dbOpenDynaset As Long = 2


Sub TestIt()
    Dim lngIndex As Long
    Dim vntArray As Variant
    
    [color=green]' Get the rows.[/color]
    With CurrentDb.OpenRecordset("SELECT DISTINCT recnum FROM t_User_Ldr", dbOpenDynaset)
        If (.RecordCount) Then
            vntArray = .GetRows(.RecordCount)
        End If
    End With
    
    [color=green]' Display the rows.[/color]
    If IsEmpty(vntArray) Then
        MsgBox "No records returned."
    Else
        For lngIndex = LBound(vntArray, 2) To UBound(vntArray, 2)
            Debug.Print vntArray(0, lngIndex)
        Next lngIndex
    End If

End Sub

Chris.