ADO Connection String Class

[Mile-O]

I have the following code in a Class module called clsConnection.

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog

' Module Level Variables
Private mDatabasePath As String

' Properties
Public Property Get DatabasePath() As String
    DatabasePath = mDatabasePath
End Property

Public Property Let DatabasePath(FilePath As String)
    mDatabasePath = FilePath
End Property

Public Function Connect() As Boolean
    On Error GoTo Err_Connect
    Dim strConnect As String
    Set cn = New ADODB.Connection
    [b]Set cat = New ADOX.Catalog[/b]
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & Me.DatabasePath & ";" & _
                "User Id=admin;" & _
                "Password="
    cn.Open strConnect
    Set cat.ActiveConnection = cn
    Connect = True
Exit_Connect:
    strConnect = vbNullString
    Exit Function
Err_Connect:
    Connect = False
    Resume Exit_Connect
End Function

Private Sub Class_Terminate()
    Set cat = Nothing
    Set cn = Nothing
End Sub

On a form I have the following code in the Click event of a command button:

Private Sub cmdReview_Click()
    Dim c As clsConnection
    Set c = New clsConnection
        With c
            .DatabasePath = Me.txtFileName
            If .Connect Then
                MsgBox "Connected to database"
            Else
                MsgBox "Unable to connect"
            End If
        End With
    Set c = Nothing
End Sub

Everytime that I try to connect to a database I get the Unable to connect message. Can anyone spot the mistake I'm making as I certainly can't.

 

[Mile-O]

Typical! As soon as I post this I spot the error. I've added the missing line in bold above should anyone ever need to use this to connect via ADO to another database.

 

[modest]

Hmm a class connection. That's interesting.

Here's a little bit easier way to get the string without typing it all out though. That is if you're going to keep everything the same as you have it.

cn.ConnectionString = Replace(CurrentProject.Connection, CurrentDb.Name, "C:\New Database Name.mdb")