Authenticating Access to an External Database using VBA

[jochryem]

I have a user form that I've built in excel that captures a UserId and Password which I use to update the DNS entry sting of an Oracle database I connect to through ODBC. I want to build VBA code that authenticates that the UserId and Password I entered in the user form is valid for the database before running a bunch of VBA code and I want it to stop if I entered an invalid UserId or Password (or both).

Here is my connection string:

Dim conn As String
conn = "ODBC;DSN=livedr;UID=" & UserId & ";PWD=" & Password & ";DBQ=LIVEDR;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"

How would one go about authenticating the UserId and Password before allowing VBA code to continue running?

[SamT]

Moderator bump

[JKwan]

give this a try

Sub Check()
 Dim bOK as Boolean
 
    On Error Resume Next
    Do
        dbConnectStr = "Provider=msdaora.1;" & _
                       "Data Source=YourDataSource;User Id=YourUserID; Password="
         
        Connection.ConnectionString = dbConnectStr
        Connection.Properties("Prompt") = adPromptAlways
        Connection.Open dbConnectStr
        Select Case Err.Number
            Case Is = -2147217843
                bOK = False
                MsgBox "Incorrect credential", vbCritical, "Incorrect Credential"
            
            Case Is = -2147217842
                bOK = False
                MsgBox "Operation Cancelled", vbInformation, "Operation Cancelled"
                Exit Sub
                
            Case Else
                SQL = "SELECT blah from blah"
                bOK = True
                RecordSet.Open SQL, Connection
            End Select
       Err.Clear
    Loop Until bOK
    On Error GoTo 0
End Sub