Connect to Oracle Database using Excel VBA

[agarwaldvk]

Hi Everybody


I am trying to connect to an Oracle database using Excel VBA (using the ADO objects).

Does anyone know the connection string syntax to connect to Oracle database, preferably prompting the user to enter the user id and password.

I could do the same to connect to SQL Server database.


Best regards


Deepak

[mohanvijay]

check this

http://www.connectionstrings.com/oracle

[agarwaldvk]

Hi Everybody


I found something along these lines on the net somewhere.

I am putting this here so that if anyone else is looking for a solution for this kind of connection issues, they may be able to use this solution.

Thanks for your link as well - although I didn't need to use it but I do like your knowledge base article of how to get the pdf file information in Excel or a text file - that is great! I have been looking for this for sometime!


Sub GetData()
Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim recordCount As Long
dbConnectStr = "Provider=msdaora;Data Source=" & "Oracle_Database_Name;"
User Id=userId" & "; Password=" & "password"
'The statement above has been commented out. I use the statement below to prompt the user for the userId and password - which is what I prefer!
con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString
'This is an example SQL code that you might want to run
'Select * From MyTable
'SQL_String = "Select count(*) from adm_user"
recset.Open SQL_String, con
recset.MoveLast
recordCount = recset.recordCount
recset.MoveFirst
Do While Not recset.EOF = True
'Have a loop here to go through all the fields
recset.MoveNext
Loop
recset.Close
End Sub

Best regards
Deepak

[User17061968]

Hello all, hi Deepak,

I tried to built documented solution.
My realized following steps:

(1) Successfully installation of 18 XE Express
(2) Successfully connect with "User DSN" (ODBC-Datasource-Adminstration/Oracle ODBC Driver Connect): Input of "Service Name" / "User Name" / "Password"

My questions:

(1) Is the discribed ODBC-Connection needed, like discribed under (2)
(2) Concerning your information >>> Data Source=" & "Oracle_Database_Name;"
What is the substitution of "Oracle_Database_Name". Is this the first string in tnsnames.ora: XE?


XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RENEDI-HP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Thanks for an answer.

Regards JuMathias

[User17061968]

Hello all, hi Deepak,

my further question:

Using EXCEL 2016) and the link "Microsoft Active Data Objects 6.1 Libary" (msado 15.dll), I get an error in the fouth line:



Sub Option4()
Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
< Information of EXCEL: Variable is not defined. What's to do?

Thanks for infromation

Regards

JuMathias

[macropod]

JuMathias: Kindly don't resurrect ancient threads just to hijack them with a different question. Start a new thread instead. Thread closed.