Connect to Oracle through Excel

cmontana
01-12-2004, 01:06 PM
Hi, I have been able to access Oracle tables using VBA within Access but I now need to do the same thing from within Excel. Can this be done? How?

What I need is to run an SQL query and dump the result to a worksheet. I already have code (used within Access) that creates the data I need - just need to know how to execute from within Excel.

Thanks in advance for your help.

Timbo
01-13-2004, 05:41 AM
This is more related to databases, but I think the mention of Excel might scare off the db experts there so:
http://www.visualbasicforum.com/t39722.html

You will need to add the "MS ActiveX Data Objects" library reference to your Excel project, and use a 'Connection' or 'Command' to execute SQL on the Oracle db. Look up those keywords in the VB help after you've added the reference...

Post back if you get stuck.

cmontana
01-13-2004, 10:17 AM
Thanks to the information. I understand how to add the reference, but I have five (5) different references available for Microsoft ActiveX Data Objects 2.X Library, one for Multi-dimensional 2.7 Library, and one for Recordset 2.7 Library. Which one should I choose?

Timbo
01-13-2004, 10:24 AM
"Microsoft ActiveX Data Objects 2.7 Library" is the one I was referring to :)

cmontana
01-13-2004, 01:05 PM
I thought so - after I added that reference, and read through the ADO tutorial, I recieved the following error - "table or view does not exist" right after the "Set myRecSet..." line executed.

What did I miss? Is there any way to determine if the connection was opened successfully?

-- Code to read Oracle table and write values to worksheet
Public Sub DataDump(mySurveyFormat As String)
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim mySQL As String

Set MyConn = New ADODB.Connection

'ODBC DSN method
MyConn.ConnectionString = "DSN=TSPM;" & "Uid=CMONTANA;" & "Pwd=*"

MyConn.Open

Select Case mySurveyFormat
Case "CBS"
mySQL = "SELECT SATADMIN_V_SURVEY_QUESTIONS.QUESTION_ID_LIST, SATADMIN_V_SURVEY_QUESTIONS.SURVEY_FORMAT, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_NUMBER, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_DESC_SHORT, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_DESC_LONG, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_SORT, SATADMIN_V_SURVEY_QUESTIONS.LOYALTY_IND, SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID FROM SATADMIN_V_SURVEY_QUESTIONS WHERE (((SATADMIN_V_SURVEY_QUESTIONS.SURVEY_FORMAT) = " & mySurveyFormat & ") And ((SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID) = 25)) ORDER BY SATADMIN_V_SURVEY_QUESTIONS.QUESTION_SORT"
Case "IES"
mySQL = "SELECT SATADMIN_V_SURVEY_QUESTIONS.QUESTION_ID_LIST, SATADMIN_V_SURVEY_QUESTIONS.SURVEY_FORMAT, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_NUMBER, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_DESC_SHORT, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_DESC_LONG, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_SORT, SATADMIN_V_SURVEY_QUESTIONS.LOYALTY_IND, SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID FROM SATADMIN_V_SURVEY_QUESTIONS WHERE (((SATADMIN_V_SURVEY_QUESTIONS.SURVEY_FORMAT) = " & mySurveyFormat & ") And ((SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID) = 2)) Or (((SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID) = 15)) Or (((SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID) = 16)) ORDER BY SATADMIN_V_SURVEY_QUESTIONS.QUESTION_SORT"
Case "PSC"
mySQL = "SELECT SATADMIN_V_SURVEY_QUESTIONS.QUESTION_ID_LIST, SATADMIN_V_SURVEY_QUESTIONS.SURVEY_FORMAT, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_NUMBER, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_DESC_SHORT, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_DESC_LONG, SATADMIN_V_SURVEY_QUESTIONS.QUESTION_SORT, SATADMIN_V_SURVEY_QUESTIONS.LOYALTY_IND, SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID FROM SATADMIN_V_SURVEY_QUESTIONS WHERE (((SATADMIN_V_SURVEY_QUESTIONS.SURVEY_FORMAT) = " & mySurveyFormat & ") And ((SATADMIN_V_SURVEY_QUESTIONS.RESULT_TYPE_ID) = 2)) ORDER BY SATADMIN_V_SURVEY_QUESTIONS.QUESTION_SORT"
Case "CSMS"
'need special code here to find the correct MDB file before extracting data.
mySQL = ""
End Select

Set MyRecSet = MyConn.Execute(mySQL)
MyConn.Close

'activate the data dumping worksheet
Worksheets("Questions").Activate

'print out the data collected
MyRecSet.MoveFirst
myRow = 2
myCol = 1
While Not MyRecSet.EOF
For x = 1 To MyRecSet.Fields.Count
ActiveSheet.Cells(myRow, myCol) = MyRecSet.Fields(1)
Next x
myRow = myRow + 1
myCol = 1
Wend

End Sub

Timbo
01-13-2004, 07:00 PM
Did you look at the connection string link in the database FAQ?
http://www.able-consulting.com./MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForOracleFromMicrosoft

cmontana
01-16-2004, 06:49 AM
Yep, I did read through that. When I used that syntax I received a run-time error. I think there may be a problem with the database name I am using. I'm checking on this with some dba support people. It's funny though, I didn't recieve the error until I tried to execute the SQL that initialized my recordset. Am I correct in thinking that the connection had actually been successfully established? Is there some way I can verify this?

Timbo
01-16-2004, 08:44 PM
Execute the code in break mode, and use the 'View/Locals' VBE menu option to view your object variables. You will be able to view all the properties of the recordset object.
But I tend to agree with you about the error, it seems more likely that your SQL has a misspelt table name in there?..

cmontana
01-19-2004, 01:27 PM
Good idea about the Locals window.

When I ran this code with the Locals window opened I noticed that the connection object (MyConn) had two errors noted Here's the text for each error.

Description: "Multiple-step OLE DB operation generated errors."
Number: -2147217887
Source: "Microsoft OLDE DB Provider for ODBC Drivers"

Description: "Provider does not support the property."
Number: -2147217887
Source: "ADODB.Connection"

BTW, these are the references I have set inside the VB editor:
Visual Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft Office 9.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft ActiveX Data Objects 2.7 Library

As for the SQL statement itself, I think it's alright because I lifted it (no typing) directly from a (working) query design window (SQL mode).

Any other ideas?

Timbo
01-20-2004, 05:48 AM
I'm out of ideas, so I'll move this to the DB board. It think it's obvious that the problem is not related to VBA etc...

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum