0

I am working on a macro to pull data from an Oracle DB and return the results back to an excel worksheet. I'm able to connect to the DB when hard coding the credentials. However, I am in need of a solution which will prompt for the user to enter their credentials and validate if the connection is successful, if the credentials are invalid or if the password is, or soon will be, expired. If the connection is anything other than successful, a popup should be displayed indicating the error message. Any help achieving this would be greatly appreciated.

Here is how I'm currently connecting to the DB:

Sub ConnectToOracle()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mtxData As Variant
Dim oraQuery As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open ( _
"User ID=XXXXX" & _
";Password=XXXXX" & _
";Data Source=MBRSTGPTNS" & _
";Provider=OraOLEDB.Oracle")

rs.CursorType = adOpenForwardOnly
'QUERY'
mtxData = rs.GetRows

Worksheets("Sheet1").Activate
ActiveSheet.Range("b1:c1000") = mtxData

Set cn = Nothing
Set rs = Nothing

End Sub
Brian
  • 45
  • 1
  • 2
  • 9
  • Exactly which part of this is giving you problems? You can use a Userform to collect the user's credentials (or get them from a worksheet for example). Use an error handler to catch any error when opening the connection, and display the details using a messagebox. https://stackoverflow.com/questions/5389634/vba-error-handling-on-adodb-connection-open https://stackoverflow.com/questions/41837242/error-trapping-code-using-ado-connections – Tim Williams Dec 09 '19 at 18:48
  • None of the existing code was giving me problems, I was looking to implement error handling when logging in. The links you provided gave me the guidance I needed to implement the error handling changes. Thank you! – Brian Dec 10 '19 at 19:16

0 Answers0