PDA

View Full Version : Authenticating Access to an External Database using VBA



jochryem
11-02-2017, 09:44 AM
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;B NF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TL O=O;MLD=0;ODA=F;"

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

SamT
11-04-2017, 07:34 AM
Moderator bump

JKwan
11-06-2017, 07:46 AM
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