Consulting

Results 1 to 3 of 3

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

  1. #1

    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

  2. #2

    Next steps ...

    Quote Originally Posted by User17061968 View Post
    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
    Last edited by User17061968; 05-21-2019 at 11:09 PM.

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •