VBA connect to protected database, error handling

Question

I'm doing a form with two fields, one with I can browse the path for a database and the other for the database password.

Everything working fine when it the right password and a path to a database file.

My problem is an error stop the program if I dont have the right password or the file is not a database. I'm not able to test the connection without crashing.

How can I test the path and the password without making the whole thing crash. Here a look of what I have done so far(very simple) :

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim canConnect As Boolean

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=" & Me.txt2   & ";   Data Source=" & Me.txt1 & ";"
Set rs = New ADODB.Recordset

If cn.State = adStateOpen Then
  canConnect = True
  cn.Close
End If

MsgBox canConnect

As you can see txt1 et txt2 are my two textarea and like I said everything crash before my message box prompt.

HansUp · Accepted Answer · 2013-09-17 20:41:11Z

I think all you may need to do is add an error handler. I eliminated the ADODB.Recordset because you only need the .Connection.

Public Sub test17()
    Dim cn As ADODB.Connection
    Dim canConnect As Boolean
    Dim strConnect As String
    Dim strMsg As String

On Error GoTo ErrorHandler

    canConnect = False ' make it explicit, but not really needed
    Set cn = New ADODB.Connection
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Jet OLEDB:Database Password=" & Me.txt2 & _
        ";Data Source=" & Me.txt1 & ";"
    Debug.Print strConnect
    cn.Open strConnect

    ' if we got to this point, there was no error ...
    ' which means the connection succeeded
    canConnect = True
    cn.Close

ExitHere:
    MsgBox canConnect
    Set cn = Nothing
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure test17"
    'MsgBox strMsg or Debug.Print strMsg, if desired
    GoTo ExitHere
End Sub

Collectives™ on Stack Overflow

VBA connect to protected database, error handling

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related