Arrays vs Recordsets

[LPurvis]

>> This one line of code has pretty significant implications to me, like overnight it'll change how I do data access for class modules.

I was just thinking that I might as well mention, even though it's not likely to play a part in your decision or implemenation, that the connection used by a disconnected recordset can't always be destroyed. (Note that it is the connection itself - not the connection object, that's a gonner if you so decide).

Obviously, under normal circumstances, its a convenient tool at your disposal - open a connection, fetch data, disconnect, close the connection and continue to read the data for local access and manipulation without affecting the source.

While your recordset data is disconnected by the above code, regardless of other considerations (so long as it obeys the requirements for disconnecting a recordset of course which is, primarily, it being client side), the connection of a Shaped Jet recordset will persist.

i.e. (assuming a shaped recordset was opened...)
Set rst.ActiveConnection = Nothing
cnn.Close

ends the connection of those objects - but the physical connection is persisted until the recordset is closed too.
(This is trivial to determine in Jet, no Trace required, you can just watch the LDB file creation/destruction.)
As mentioned, the connection object can be closed/destroyed. The recordset is indeed disconnected. It can only be the shaping provider that causes ADO to internally hold open a connection until the recordset closes.

If you never employ shaped recordsets to external Jet data sources then it won't be an issue - and even where it does apply, it's not likely to impact what you're doing. You would generally just assume that the connection has terminated when it hasn't.

I mention it only since, AFAIK, that is the single except to successful disconnection.

Cheers.

 

[ChrisO]

I'm not sure of the requirement here but timing between arrays and record sets may not be fair to either.

If an array even approached a recordset for functionality then the array becomes somewhat more complex.
I think it should be the complex array which is timed against a recordset.

Below is some code which tries to emulate, to some degree, a recordset with an array: -

Option Explicit
Option Compare Text

[color=green]' Define Record.[/color]
Public Type MyRecord
    Index   As Long     [color=green]' Default is 0 (Marked as deleted)[/color]
    FName   As String   [color=green]' Default is ZLS[/color]
    LName   As String   [color=green]' Default is ZLS[/color]
    Address As String   [color=green]' Default is ZLS[/color]
    DOB     As Date     [color=green]' Default is Midnight 30/Dec/1899[/color]
End Type

[color=green]' Define array of Records (Recordset)[/color]
Public Type MyRecordset
    ID()    As MyRecord
End Type


Sub TestIt()
    Dim MyNewRecordset As MyRecordset
    
    [color=green]' Initialize[/color]
    ReDim MyNewRecordset.ID(0)
    
    [color=green]' AddNew Records[/color]
    AddNewRecord MyNewRecordset, "Fred", "Nurks", "Somewhere", Date - 1000
    AddNewRecord MyNewRecordset, "Sam", "Spade", "Somewhere Else", Date - 2000
    
    [color=green]' Display results[/color]
    DebugPrint MyNewRecordset
    
    [color=green]' Delete Record[/color]
    DeleteRecord MyNewRecordset, 1
    
    [color=green]' Display results[/color]
    DebugPrint MyNewRecordset
    
    [color=green]' AddNew Record[/color]
    AddNewRecord MyNewRecordset, "Mary", "HadALittleLamb", "Over There", Date - 3000
    
    [color=green]' Display results[/color]
    DebugPrint MyNewRecordset
    
    [color=green]' Compact.[/color]
    CompactArray MyNewRecordset
    
    [color=green]' Display resulDatets[/color]
    DebugPrint MyNewRecordset

End Sub


Public Sub AddNewRecord(ByRef ThisRecordset As MyRecordset, _
                        ByVal strFName As String, _
                        ByVal strLName As String, _
                        ByVal strAddress As String, _
                        ByVal datDOB As Date)
    
    Dim lngIndex As Long
    
    With ThisRecordset
        [color=green]' Auto increment Array index and Record Index.[/color]
        lngIndex = UBound(.ID) + 1
        
        [color=green]' Bump the Array (Recordset) and Preserve data in the Records.[/color]
        ReDim Preserve .ID(lngIndex)
        
        With .ID(UBound(.ID))
            .Index = lngIndex
            .FName = strFName
            .LName = strLName
            .Address = strAddress
            .DOB = datDOB
        End With
    End With

End Sub


Public Sub DeleteRecord(ByRef ThisRecordset As MyRecordset, _
                        ByVal lngIndex As Long)
                    
    [color=green]' Mark Record Index as being deleted.
    ' Note that this is the Index in the Record, not the ID of the array.
    ' This should be the equivalent of deleting a Record in a Recordset without compacting.[/color]
    With ThisRecordset.ID(lngIndex)
        .Index = 0
    End With

End Sub


Public Sub CompactArray(ByRef ThisRecordset As MyRecordset)
    Dim lngIndex As Long
    Dim Temp     As MyRecordset
    
    [color=green]' Initialize Temp.[/color]
    ReDim Temp.ID(0)

    With ThisRecordset
        For lngIndex = 1 To UBound(.ID)
            [color=green]' If Record not marked as deleted?[/color]
            If .ID(lngIndex).Index <> 0 Then
                [color=green]' Bump Temp upper bound.[/color]
                ReDim Preserve Temp.ID(UBound(Temp.ID) + 1)
                
                [color=green]' Transfer data to Temp.[/color]
                Temp.ID(UBound(Temp.ID)).Index = UBound(Temp.ID)
                Temp.ID(UBound(Temp.ID)).FName = .ID(lngIndex).FName
                Temp.ID(UBound(Temp.ID)).LName = .ID(lngIndex).LName
                Temp.ID(UBound(Temp.ID)).Address = .ID(lngIndex).Address
                Temp.ID(UBound(Temp.ID)).DOB = .ID(lngIndex).DOB
            End If
        Next lngIndex
    End With
    
    ThisRecordset = Temp
    
End Sub


Public Sub DebugPrint(ByRef ThisRecordset As MyRecordset)
    Dim lngIndex As Long

    Debug.Print "-----------"

    With ThisRecordset
        For lngIndex = 1 To UBound(.ID)
            With .ID(lngIndex)
                [color=green]' Check if Index is marked as deleted.[/color]
                If .Index = 0 Then
                    Debug.Print .Index & ":  Marked as Deleted."
                Else
                    Debug.Print .Index & ":  " & .FName & " " & .LName & ", "; .Address & ", " & .DOB
                End If
            End With
        Next lngIndex
    End With
    
    Debug.Print "-----------"

End Sub

Maybe it does and maybe it doesn't, it would simply need testing.

 

[Galaxiom]

Have you considered using recordsets to do everything, including the loading of text file data?

It won't necessarily be fastest - but ADO (using the Jet provider ;-) loads text file delimited data quite nicely.

I will add this variant to the tests. Maybe to make the scope complete I should also try holding the 10 KLine text files as arrays too.

I am working with fixed width data. I imagine the text file conversion to ADO would be read as a single field representing the whole line. The fixed column width information is the core of the array versus recordset question.

Currently I am just reading the text file line by line. Is there another way ADO can grab the data? Otherwise what would be the point of converting the text to an intermediate recordset. If the text file must be read like this anyway then it would make sense to impliment the full process through to the finished recordset in the same step.

 

[Banana]

FWIW, you can link to text files in Access as if it were just any other tables. To be sure, you have to be careful in specifying the specs and making that you can trust the text file to be consistent in its formatting because a mangled text files would look funky in Access.

With ADO, you could load the text file as a Stream object, then open a recordset against that stream object. I've never done that and imagine one has to play with it a bit to see how it handles the formatting and how it is interpreted (what defines a fields? What defines a row?)

Hope that gives you a starting point to look at...