Creating a DBHelper class to work with ADO in Excel

[JasonEnsor]

Hi Guys,

I have just started using ADO with excel and have created a module with a few procedures to do some basic testing, but I am wanting to turn this in to reusable code so that I can reduce repetition as I have a feeling I might be using this a lot in the future. The code I have at the moment is:

Option Explicit

Dim oConnection As ADODB.Connection
Dim oRecordSet As ADODB.Recordset
Dim sSQL As String
Const sFilePath = "U:\Ages.xlsx"
Const sXLVersion As String = "2007"

Sub OpenConnection(sFilePath As String, sXLVersion As String)

    Set oConnection = New ADODB.Connection

    With oConnection
        Select Case sXLVersion
            Case "2007"
                .Provider = "Microsoft.ACE.OLEDB.12.0"
                .Properties("Extended Properties").Value = "Excel 8.0; HDR=YES"
            Case "2003"
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Properties("Extended Properties").Value = "Excel 8.0; HDR=YES"
            End Select

            .Open "Data Source = """ & sFilePath & """"
    End With

End Sub

Sub CreateRecordSet(oConnection As ADODB.Connection)

    Set oRecordSet = New ADODB.Recordset

    With oRecordSet
        .Source = sSQL
        .ActiveConnection = oConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open
    End With

End Sub

Sub BuildSQL()
    Dim TableName As String
    TableName = "Ages"
    sSQL = "SELECT [Name], [AGE] FROM [" & TableName & "$] WHERE [Age] > 7;"
End Sub

Sub DisplayResults(oRecordSet As ADODB.Recordset)
    Dim sResults As String

    Do Until oRecordSet.EOF

        sResults = sResults & oRecordSet.Fields(0).Value & " " & oRecordSet.Fields(1).Value & Chr(13)
        oRecordSet.MoveNext
    Loop

    MsgBox sResults
End Sub



Sub CloseConnection(oConnection As ADODB.Connection)
    oConnection.Close
End Sub

Sub Test()

    Call OpenConnection(sFilePath, sXLVersion)
    
    
    Call BuildSQL
    Call CreateRecordSet(oConnection)
    Call DisplayResults(oRecordSet)
    
    
    Call CloseConnection(oConnection)
End Sub

Does anyone have any thoughts on what I could do better with the code to make it more reusable? I have not really used classes much in vba but I think that might be the way forward

Many Thanks in advance


Regards

J.