Originally Posted by
User17061968
Hello all,
I tried to use solution of "agarwaldvk" of the following solved thread (number 40986)
to get data in MS-EXCEL with using ORACLE as database.
The given code:
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
I realized the 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 question:
No. 1:
Concerning the information of agarwaldvk >>> Data Source=" & "Oracle_Database_Name;"
What is the substitution of "Oracle_Database_Name". Is this the first string in my tnsnames.ora: XE?
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RENEDI-HP)(PORT = 1521))
.
.
.
No. 2:
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 GetData()
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 your infromation
Regards
JuMathias