Joining Worksheet Data to Oracle Table

[hatman]

I have successfully joined local Worksheet data with sqlServer tables using the following syntax:

    Set objConnection = New ADODB.Connection
    Set objRecordset = New ADODB.Recordset
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & ThisWorkbook.FullName & _
            ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

    SQL = "SELECT * FROM [Sheet1$] l INNER JOIN [sqlServer_Conn_String].TableName r ON l.wsField = r.serverField"

    objRecordset.Open sql, objConnection, adOpenDynamic, adLockReadOnly, adCmdText

To simplify parsing out syntax, I have also run this "version" to confirm that the connection works before attempting the join:

    Set objConnection = New ADODB.Connection
     Set objRecordset = New ADODB.Recordset
     
     objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & ThisWorkbook.FullName & _
             ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

     SQL = "SELECT * [sqlServer_Conn_String].TableName"

     objRecordset.Open sql, objConnection, adOpenDynamic, adLockReadOnly, adCmdText

This simplified version werx fine with sqlServer. However, when I instead use an Oracle connection string, I get one of (2) error messages depending upon whether I use a TNS string or a so-called TNS-less string. One tells me the table cannot be modified because it is readonly, and the other tells me that JET cannot find the table.

I confirmed that the (2) Oracle connection strings "work" by simply connecting to Oracle only with the following syntax:

    Set objConnection = New ADODB.Connection
     Set objRecordset = New ADODB.Recordset
     
     objConnection.Open "Oracle_Connection_String"

     SQL = "SELECT * FROM TableName"

     objRecordset.Open sql, objConnection, adOpenDynamic, adLockReadOnly, adCmdText

Through trial and error, and a lot of help from ConnectionStrings.com, I finally narrowed down to the following connections strings that work:

Driver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;

(where I substitute OraHome10g instead of OraHome92 to reflect the version we use here.)

and

Provider=OraOLEDB.Oracle;
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));
User Id=myUsername;Password=myPassword;

For kix, I tried putting in gibberish for SID and UserID and PWD to see if I could get any other error messages to be returned. Interestingly, no matter what I change in the connection string, each Driver/Provider seems to simply output a useless error message that doesn;t really describe what the true failure is.

My Plan B is to simply iterate through the records of my worksheet and perform an SQL query of Oracle for each record and append the results into the worksheet. It's messy but do-able. However, I would LOVE IT if anyone can look at what I am doing and tell me how to make Plan A work as it is by far the most elegant solution.

 

[ranman256]

instead of iterate, can you just paste them all at once?

Set rst = objRecordset.Open sql....

sheet("data").activate
range("A1").select
ActiveCell.CopyFromRecordset rst

 

[hatman]

instead of iterate, can you just paste them all at once?

Set rst = objRecordset.Open sql....

sheet("data").activate
range("A1").select
ActiveCell.CopyFromRecordset rst

Yes, that will be part of the Plan B solution. But I still need to iterate through each record on the destination sheet in order to get the results from the database that correspond to each record, then insert enough lines in the sheet to accept the resulting records, and finally use the CopyFromRecordset method you mention. In other words, my database has millions of records in it, with one record corresponding to each serial number of each part number. My worksheet has several hundred records in it, one record for each part number of interest. If I cannot perform an inner join of the data directly, I need to query for all records corresponding to my first part number, duplicate that part number to equal the number of records that came from the query (at one serial number per record) and append that retrieved data into the spreadsheet in one shot using the CopyFromRecordset method. Then query on the second part number and so on down the list. Actually, for this kind of thing it works better to move from the bottom up, but you get the picture. Make sense???

 

[ranman256]

it dont make sense.
you paste the rst at the end of the sheet.
then sort.

2 steps.

 

[hatman]

it dont make sense.
you paste the rst at the end of the sheet.
then sort.

2 steps.

I'm appending Columns/Fields, not rows. It's an Inner Join operation between (2) tables.