Option Explicit
Const conProvider As String =  "Provider=MSDataShape.1 ;"
Const conSecurPer As String =  "Persist Security Info=False;" 
Const conModeRead As String =  "Mode=ReadWrite;" 
Const conDataSour As String =  "Data Source=" 
Const conDataProv As String =  "Data Provider=MICROSOFT.JET.OLEDB.4 . 0 "

Dim count As Integer
Private ADOconn() As ADODB.Connection
'local variable(s) to hold property value(s)



'Public Property Set cnnADO(ByVal vData As Variant)
''used when assigning an Object to the property, on the left side of a Set statement.
''Syntax: Set x.cnnADO = Form1
'    Set ADOconn = vData
'End Property


Public Property Get cnnADO(Optional ByVal idx As Integer) As Variant
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.cnnADO
Dim intIndex As Integer
intIndex = IIf(IsMissing(idx), idx, count)
    If IsObject(ADOconn(intIndex)) Then
        Set cnnADO = ADOconn(intIndex)
    Else
        cnnADO = ADOconn
    End If
End Property

Public Function cnnAdo_mdbAdd(strPath As String, _
                           strMDB As String, _
                  Optional ByVal idx As Integer)
                           
Dim strConnect As String
strConnect = conProvider & conSecurPer & conModeRead & conDataSour & _
             strPath &  "\"  & strMDB &  ";"  & _
             conDataProv
If idx =  0  Then count = count +  1 
ReDim Preserve ADOconn(count)
Set ADOconn(count) = New ADODB.Connection
ADOconn(count).ConnectionString = strConnect
ADOconn(count).Open
End Function

Private Sub Class_Initialize()
count =  0 
End Sub

Private Sub Class_Terminate()
On Error Resume Next
Dim intI As Integer
For intI =  1  To count
ADOconn(count).Close
If Int(err.Number) =  3704  Then err.Clear
If Not (err.Number =  0 ) Then MsgBox err.Description
Set ADOconn(count) = Nothing
Next
End Sub