Storing recordset as instance of a class?

Question

Have the following scenario. I have a few form, which essentially have a few dropboxes, lists etc. I populate them with records from a ms sql db. However, is there a way to query the database only once and store the records as an instance of a class throughout the life of the application rather than querying each time the user opens the form?

Connection is as this:

Sub connection_test()

    Dim Cn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim stSQL As String

    stSQL = "SELECT * FROM dbo.Client"

    Set Cn = New ADODB.Connection

    With Cn
        .CursorLocation = adUseClient
        .Open CONNECTION_STRING
        .CommandTimeout = 0
        Set Rs = .Execute(stSQL)
    End With

    Rs.Close
    Cn.Close
    Set Rs = Nothing
    Set Cn = Nothing
End Sub

Can someone suggest a solution for me? I can see all the problems from heavy traffic and mostly unnecessary.

Answer 1

If you just want the recordset available, dim the variable as public in a standard module

Public Rs As ADODB.Recordset

Sub connection_test()

    Dim Cn As ADODB.Connection
    Dim sSQL As String

    If Rs.State = adStateClosed Then
        sSQL = "SELECT * FROM dbo.Client"

        Set Cn = New ADODB.Connection

        With Cn
            .CursorLocation = adUseClient
            .Open CONNECTION_STRING
            .CommandTimeout = 0
            Set Rs = .Execute(sSQL)
        End With
    End If

End Sub

Now Rs will be available anywhere in the project. You can run connection_test whenever you need to and, if the recordset it closed, it will create it. If not, you're good to go.

Generally my approach to this is to create custom classes. I would create a CClient class, fill it from a recordset (or somewhere else), manipulate the objects with the business logic, then write the new values back to the database. That way, none of my business logic relies on the fact that I'm using ado. I could switch to a text file or an Excel worksheet as a data store and wouldn't have to worry about dependencies everywhere in the code.

For instance, suppose I have an Access table:

 ClientID, Autonumber
 ContactFirst, String
 ContactLast, String
 Company, String
 CityState, String
 Volume, Double

I create a CClient class with a property for each field in my table. I also create a CClients class to hold all of the CClient instances. In a standard module, you might have something like this

Public gclsClients As CClients

Sub Main()

    Set gclsClients = New CClients

    'Fill the class
    gclsClients.FillFromRS

    'change some value
    gclsClients.Client(1).Volume = 100

    'write back to the database
    gclsClients.WriteToDB

End Sub

Where I change the volume of one client, you would have a lot more code calling your userform, etc. The basics are, load up the class, do whatever you need, then write the class data back to the db. I won't show you all the class code, but in CClients

Public Sub FillFromRS()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim clsClient As CClient

    Const sSQL As String = "SELECT * FROM tblClient"

    Set cn = New ADODB.Connection

    cn.Open msCON
    Set rs = cn.Execute(sSQL)

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            Set clsClient = New CClient
            With clsClient
                .ClientID = rs.Fields("ClientID").Value
                .ContactFirst = rs.Fields("ContactFirst").Value
                .ContactLast = rs.Fields("ContactLast").Value
                .Company = rs.Fields("Company").Value
                .CityState = rs.Fields("CityState").Value
                .Volume = rs.Fields("Volume").Value
            End With
            Me.Add clsClient
            rs.MoveNext
        Loop
    End If

End Sub

This method gets the data from the database and fills a bunch of CClient instances. Also in CClients

Public Sub WriteToDB()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim clsClient As CClient
    Dim sSQL As String

    Set cn = New ADODB.Connection

    cn.Open msCON

    For Each clsClient In Me
        sSQL = BuildUpdateSQL(clsClient)
        cn.Execute sSQL
    Next clsClient

End Sub

Private Function BuildUpdateSQL(clsClient As CClient)

    Dim sReturn As String

    With clsClient
        sReturn = "UPDATE tblClient SET ContactFirst = '" & .ContactFirst & "',"
        sReturn = sReturn & " ContactLast = '" & .ContactLast & "',"
        sReturn = sReturn & " Company = '" & .Company & "',"
        sReturn = sReturn & " CityState = '" & .CityState & "',"
        sReturn = sReturn & " Volume = " & .Volume
        sReturn = sReturn & " WHERE ClientID = " & .ClientID & ";"
    End With

    BuildUpdateSQL = sReturn

End Function

This method loops through all of the CClient instances, creates an UPDATE sql statement and executes it. You'll want to implement some sort of IsDirty property in CClient so that you only update those client where something is changed. The rest of CClients and CClient are basic class module stuff.

You can call WriteToDB a lot or a little. In some apps, I write it whenever something changes. In others, I only write back to the database when the workbook is closed. It kind of depends on the flow of your application. The real beauty is that if you change from, say, an Access database to a text file for data storage, you only have to change to methods in CClients. All of the rest of your code consumes CClients and doesn't care where the data lives.

You can see the workbook and Access database here http://www.dailydoseofexcel.com/excel/ClientClassExample.zip