PDA

View Full Version : VBA code to pick id and password for oracle from excel



rockybalboa
11-27-2013, 09:39 PM
I am trying to connect to oracle database via VBA. I want a way in which the macro will take the id and pass of the database from a given range. i am doing this because every body will be using their personal login and i cannot hardcode it.


conn = "UID=****;PWD=****;DRIVER={Microsoft ODBC for Oracle};SERVER=****;"
With cnn
.ConnectionString = conn
.CursorLocation = adUseClient
.Open
End With


Suppose the id is in Range M3 and Password in Range M4. I want the macro to pick UID from M3 and PWD from M4.

Thanks

p45cal
11-28-2013, 12:50 AM
try:
conn = "UID=" & Sheets("mySheetName").Range("M3").Value & ";PWD=" & Sheets("mySheetName").Range("M4").Value & ";DRIVER={Microsoft ODBC for Oracle};SERVER=****;"
or
With Sheets("mySheetName")
conn = "UID=" & .Range("M3").Value & ";PWD=" & .Range("M4").Value & ";DRIVER={Microsoft ODBC for Oracle};SERVER=****;"
End With
If the sheet with M3 and M4 is the active sheet you could use (less robust):
conn = "UID=" & Range("M3").Value & ";PWD=" & Range("M4").Value & ";DRIVER={Microsoft ODBC for Oracle};SERVER=****;"
or
conn = "UID=" & [M3] & ";PWD=" & [M4] & ";DRIVER={Microsoft ODBC for Oracle};SERVER=****;"
(After adjusting SERVER=**** and mySheetName of course.)

rockybalboa
11-28-2013, 01:24 AM
Thank you very much, Appreciated.