Data import: MS-EXCEL (frontend) with using ORACLE as database (backend)

Hello all,

I tried to use solution of "agarwaldvk" of the following solved thread with the 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 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 your infromation

Regards

JuMathias

Quote:

---

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

---

*************** 22.05. *****************

Concerning the problem No. 2, I have to use further two lines to resolve the first errors:


Sub GetData()

Dim con As ADODB.Connection 'New, 22.05.
Dim recset As ADODB.Recordset 'New, 22.05.
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=" & "XE;"

'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[/COLOR]


Note/question: In the solution of thread 40986, the variables con and reset have not declaired.
How could the code run in the solution of agarwaldvk?

Regards

JuMathias

Now, I have the question to:

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))

.
.
.

Regards

JuMathias