Class Module to wrap up classic ADO call to SQL-server I do a lot of copying excel vba code related to classic ADO from workbook to workbook, so I've decided to add the code to a Class Module in a single utility workbook and then in all other books just reference the utility book and use instances of the ADO wrapper class.

The class module is called clDataAccess

I would like to know any obvious flaws in the coding:

Option Explicit

'::::::::::::::::::::
'::
'::  Class Variable Declarations
'::
'::::::::::::::::::::

Private mcnConnectString As String
Private mcnConnect As ADODB.Connection


'::::::::::::::::::::
'::
'::  on initialization open an ADO connection through to the server
'::
'::::::::::::::::::::
Private Sub Class_Initialize()

    mcnConnectString = _
        "PROVIDER=SQLOLEDB.1;" &amp; _
        "P******D=*********;" &amp; _
        "PERSIST SE**RITY INFO=****;" &amp; _
        "USER ID=**********;" &amp; _
        "INITIAL CATALOG=*******;" &amp; _
        "DATA SOURCE=*****;" &amp; _
        "USE PROCEDURE FOR PREPARE=*;" &amp; _
        "AUTO TRANSLATE=****;" &amp; _
        "CONNECT TIMEOUT=*;" &amp; _
        "COMMAND TIMEMOUT=*" &amp; _
        "PACKET SIZE=****;" &amp; _
        "TAG WITH COLUMN COLLATION WHEN POSSIBLE=****"

    Call ensureClosedConn(mcnConnectString)

End Sub


'::::::::::::::::::::
'::
'::  connection string property
'::  user could need to change the connection
'::
'::::::::::::::::::::
Property Let connectionString(ByVal stConn As String)
    mcnConnectString = connectionString
End Property


':::::::::::::::::
'::
':: This procedure attempts to create a connection to
':: the database using the specified connection string.
'::
'::::::::::::::::::::
Private Function ensureClosedConn(ByVal sConnection As String) As Boolean

On Error GoTo ErrorHandler

    If (mcnConnect Is Nothing) Then
        Set mcnConnect = New ADODB.Connection
        With mcnConnect
            .connectionString = sConnection
            .CommandTimeout = 0
            .Open
            .Close
        End With
    Else
        If (mcnConnect.State And 1) = 1 Then mcnConnect.Close
    End If

Exit Function
ErrorHandler:
    Err.Raise 99999, , "Error occured inside routine ensureClosedConn of clDataAccess"
End Function

':::::::::::::::::
'::
':: Client may wish to receive the recordset
'::
'::::::::::::::::::::   
Public Function cGetRecordset(ByVal sqlString As String) As ADODB.Recordset

    Call ensureClosed_PubConn
    mcnConnect.Open
    Set fRecordset = New ADODB.Recordset

    With fRecordset
        .Open _
            Source:=sqlString, _
            ActiveConnection:=mcnConnect, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockReadOnly, _
            Options:=adCmdText
    End With

    Set cGetRecordset = fRecordset
End Function


':::::::::::::::::
'::
':: This procedure attempts to move some data
'::
'::::::::::::::::::::
Public Sub updateWorkSheet( _
    ByVal sqlString As String, _
    ByRef rngDest As Excel.Range _
    )

On Error GoTo ErrorHandler

    Call ensureClosed_PubConn
    mcnConnect.Open
    Dim r As ADODB.Recordset
    Set r = New ADODB.Recordset

    With r
        ':: Extract and copy the required records.
        .Open _
            Source:=sqlString, _
            ActiveConnection:=mcnConnect, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockReadOnly, _
            Options:=adCmdText
        rngDest.CopyFromRecordset r
        .Close
    End With

    ':: drop the recordset and its connection
    If Not (r Is Nothing) Then
        If (r.State And 1) = 1 Then r.Close
        Set r.ActiveConnection = Nothing
        Set r = Nothing
    End If

ErrorHandler:
    Err.Raise 99999, , "Error occured inside routine updateWorkSheet of clDataAccess"
End Sub    'updateWorkSheet
Suggestions:


Don't assume the cursor type of the recordset, the user might want a different type.
Consider making a ConnectionString class that lets the user use properties to get and let the individual parameters, but allows assigning or returning a complete string
Don't force the connection to close, as the user might intend to make subsequent calls.