The following Excel macro shows how to connect to an Oracle database using ADO, create a new worksheet in the current work book, and then fill in the returned data using a slow method (not recommended) and a fast method.
'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting '*** This section might be in the top portion of the user form, worksheet code, or module: 'Declare a connection object in the general section to hold the connection to the database Dim dbVMFG As ADODB.Connection 'Declare a set of variables to hold the username and password for the database Dim strUserName As String Dim strPassword As String Dim strDatabase As String '*** Dim i as Integer Dim intResult As Integer Dim strSQL as String Dim lngRow as Long On Error Resume Next strDatabase = "MyDB" strUserName = "MyUser" strPassword = "MyPassword" 'Connect to the database 'Oracle connection string Set dbVMFG = New ADODB.Connection dbVMFG.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;" dbVMFG.ConnectionTimeout = 40 dbVMFG.CursorLocation = adUseClient dbVMFG.Open If (dbVMFG.State <> 1) Or (Err <> 0) Then intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo") Else strSQL = "SELECT" & VBCrLf strSQL = strSQL & " ID AS RESOURCE_ID," & VBCrLf strSQL = strSQL & " DESCRIPTION" & VBCrLf strSQL = strSQL & "FROM" & VBCrLf strSQL = strSQL & " MY_TABLE" & VBCrLf strSQL = strSQL & "WHERE" & VBCrLf strSQL = strSQL & " DESCRIPTION LIKE '%10%'" & VBCrLf strSQL = strSQL & "ORDER BY" & VBCrLf strSQL = strSQL & " ID" 'Add a new worksheet to the new workbook, add after the last sheet ActiveWorkbook.Sheets.Add ActiveWorkbook.ActiveSheet.Name = "JustATest" snpData.Open strSQL, dbVMFG If snpData.State = 1 Then 'Slow Method------------------------------------ lngRow = 0 'Header Row For i = 0 To snpData.Fields.Count – 1 lngRow = lngRow + 1 ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name ActiveSheet.Cells(lngRow, i + 1).Font.Bold = True Next i 'Detail Rows Do While Not snpData.EOF lngRow = lngRow + 1 For i = 0 To snpData.Fields.Count - 1 ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i) Next i snpData.MoveNext Loop 'End Slow Method------------------------------------ 'Fast Method---------------------------------------- 'Do not use this and the slow method! For i = 0 To snpData.Fields.Count - 1 ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name Next i ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True ActiveSheet.Range("A2").CopyFromRecordset snpData 'Auto-fit up to 26 columns ActiveSheet.Columns("A:" & Chr(64 + snpData.Fields.Count)).AutoFit 'End Fast Method---------------------------------------- snpData.Close End If End If
Finally! Someone else who uses Excel to query an Oracle database 🙂
I have used Excel for this purpose since 1999, and constantly have colleagues trying to get me to change to use SQL Developer, TOAD, etc. While each has its merits (and I am sure functionality mine does not), I have found the ability to customize Excel to do exactly what I want always outweighs any loss in functionality (which normally isn’t that important to me anyway).
It’s name? SHAM. Steve Howard Application Manager. 🙂
It is good to know that there is interest in blog articles related to using Excel for database access beyond what the Microsoft Query tool is able to provide. I agree that Excel is extremely flexible. Six months ago I demonstrated to a couple of regional user’s groups for an ERP package how to build things in Excel to extend the ERP system.
I’ve just started with Excel + Oracle combo.
Please tell me how strDatabase = “MyDB” is resolved ?
I’m using OID resloving method and that seems to not working with ADO.
Probably tnsnames file is what ADO needs ? Right ?
Regards.
Greg
Yes, that particular example (and most of the examples involving either Excel or a VBS script) uses the identifier from tnsnames.ora.
If you want to manually set up an ODBC connection and use that instead, see this blog article for the connection string:
https://hoopercharles.wordpress.com/2010/01/12/select-from-or-update-a-database-table-based-on-the-contents-of-an-excel-spreadsheet/
Where is snpData being Dim’d? Also, is there a corresponding reference I should make?
Thanks,
Mark
Mark, nice catch. The code is missing the following two lines:
The above two lines could be combined into a single line like this:
However, it is my understanding that the above shortcut potentially leads to performance problems. If I recall correctly, ADO must repeatedly test whether or not snpData has been previously used, and therefore the object was previously created, every time it is accessed. The two line approach avoids this extra test.
Make certain that you add a reference to “Microsoft ActiveX Data Objects” in the Visual Basic References window, as stated in the above code.
Hi, this is interesting, the Excel collective I mean, and you may all be able to save me here. I’m a first time poster so any rules I have messed up please let me know where I should be post so I can.. I have been totally gazumped and really need support in some fashion.
I have an oracle database, and BIG (350 lines) query that has two WITH tables (one using the other) followed by 26 subsequent UNION ALL select statements that draw their data from the resultant WITH table. This runs fine in SQL developer tool. but then via the ADO (2.8) control I get nothing but zeros for all except the last union select statement
Hi Gary,
That is an interesting problem, a couple of thoughts:
* Are you using a “Provider=OraOLEDB.Oracle” in the ConnectionString? Please post the entire connection string (make certain that you replace the database username and password).
* When you establish the connection to the database, do you configure the connection to use client-side cursors? This is done with a command like this:
* ODBC connections can possibly re-write SQL statements before submitting those SQL statements to the database – the database may see an entirely different SQL statement. You can confirm that this is happening by enabling a 10046 trace for the session before submitting the SQL statement. A trace file will be created on the server that shows the exact SQL statement that is submitted. If I recall correctly, ODBC supports a “pass-through” option that does not allow the SQL statement to be rewritten before sending to the database – but that might be a DAO only concept, while the example on this page uses the ADO.
* What version of ADO did you reference in the project? You should be using at least ADO 2.8, which ships pre-installed on computers running Windows XP and later.
* What is the release version of the Oracle client? If you do not know, you can find out by opening a Windows command prompt and type:
* What is the release version of the Oracle Database? You can determine the Oracle Database release version by connecting to the database using SQL*Plus (or another tool) and issuing the following SQL statement:
* Is it possible that the data types differ slightly between the different UNION ALL sections?
Let’s try a simple test case to see if we are able to find the cause of the problem. We will start by creating a simple table to 1,000 rows:
A SQL statement with a large number of UNION ALL statements will be used in the Excel program:
In Excel’s programming code editor, create a reference to “Microsoft ActiveX Data Objects 2.8 Library” (see the directions in this article, if necessary: https://hoopercharles.wordpress.com/2011/02/28/oracle-database-time-model-viewer-in-excel-1/ ). Add the following code in the code editor:
In the above code, change these lines to specify the user that owns the T1 table created earlier, the password for that user, and the database name (from tnsnames.ora) where the table is located:
Show the Debug window (I think that the option is on the View menu) and run the above procedure by typing the following line in the Debug window, followed by an Enter key:
You should see something like this in the Debug window:
If the above works for you, try replacing the SQL statement in the TestUnion subroutine with the SQL statement that you are having trouble with – does the SQL statement return the expected results?
Hej,
by executing the code as a makro in excel i get the error
“Could not connect to the database. Check your user name and password.
The Provider could not be found. maybe it isn’t installed.”
I changed the the ConnectionString to:
“Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=” & strDatabase & “)(PORT=” & strPort & “)))(CONNECT_DATA=(SID=” & strSid & “)(SERVER=DEDICATED)));User Id=” & strUserName & “;Password=” & strPassword & “;ChunkSize=1000;FetchSize=100;”
Because the Server is not my pc. But i get the same error, if i use your ConnectionString.
Can you help me please?
I use Excel 2003 on a Vista Box with userrights.
Greatings,
Jan
Jan,
I am not sure how much help I will be able to provide to you. The error message that you received suggests one of three problems:
* If you are using the 32 bit version of Excel, you must have the 32 bit Oracle client installed (I believe that you will need the full client, not the Instant Client).
* When the Oracle client is installed, you will probably need to perform a custom installation and install all of the Windows interfaces (except the one with MTS in its name). In particular, you will need “Oracle Provider for OLE DB”.
* Finally, access rights on the computer need to be correct so that the user is able to access the Oracle client’s configuration files. If your user is not in the computer’s local administrator’s group, you may need to adjust the file permissions for the Oracle installation folder so that the user has at least read access to that folder and all of its contents. Additionally, User Access Control may need to be either set less restrictive or completely disabled.
In the script, where you see:
MyDB is the name of the database from the computer’s tnsnames.ora file.
If the OraOLEDB.Oracle provider was not installed, you may be able to create an ODBC type connection instead, assuming that the ODBC drivers were installed. See this article for an example of connecting using ODBC instead of the OraOLEDB.Oracle provider:
https://hoopercharles.wordpress.com/2010/01/12/select-from-or-update-a-database-table-based-on-the-contents-of-an-excel-spreadsheet/
I have had to modify your code to use ODBC/DSN connection because Oracle OleDB is not available – I am connecting to an Oracle 10g database using the Instant Client. I get 34 rows of data back from the query. However when I run the same query in SQL Developer I get 138. I trust SQL Developer more than Excel VBA – so where should I look for the problem?
Please disregard. I used the following code to copy/paste the exact SQL query as formulated in my VBA and pasted/ran in SQL Developer. There was a problem in how I parsed variables and passed to SQL string.
Dim dClip As DataObject
Set dClip = New DataObject
dClip.SetText strSQL
dClip.PutInClipboard
I found the incorrect lines and corrected them. I’m good now.
Great that you found the solution to the problem.
There are a couple of potential causes for the problem that you experienced, such as:
* VPD (Virtual Private Database) – assuming that you were connecting as diffrent database users
* Referencing objects in different schemas – again, easy to be affected if you were connecting as different database users
* Referencing objects in different databases – easy to be affected if one program is using an ODBC connection while another is using an OLEDB or similar type connection that directly references the tnsnames.ora
Hi hooper thanks for your codding i used your code but i face some issue 1)Could not connect to the database. Check your user name and password. if i mentioned this code conn.CursorLocation = adUseClient sample from my code:
but i am not getting if i avoid the this line conn.CursorLocation = adUseClient
2)always the recordset.State=0 only i dont how to solve this issue
note:-
please help me
this is my code corrcect me where i have to change thanks advance
I noticed that you commented out “As ADODB.Connection”. That indicates that you very likely need to add a reference to the Microsoft ActiveX Data Objects Library – the process for doing that is explained in this article:
https://hoopercharles.wordpress.com/2009/12/28/extract-1834-images-from-excel-2007-and-transfer-to-a-database-table/
In the above, the variables strHost, strDatabase, pwd, strUser are declared as variants, while only strPassword is declared as a string. I prefer not to declare multiple variables on a single line, but if you must do that, you should change the above as follows:
—
For me, the Microsoft ODBC driver for Oracle Database stopped working when my company switched to Oracle 8i (8.1.7.3) back in 2001 or 2002. There may be other issues with the contents of your connection string, but I did not spend much time examining the connection string.
This is the connection string that my code sample uses:
That “OraOLEDB.Oracle” provider is made available through a custom (or full) installation of the Oracle client when the “Oracle Provider for OLE DB” is selected. The Oracle Client installer version 11.2.0.3 misses a couple of key entries in the Windows registry – see this article for the missing entries:
https://hoopercharles.wordpress.com/2012/11/25/connecting-to-an-oracle-database-with-visual-basic-6-0-on-windows-8-64-bit/
The “conn.CursorLocation = adUseClient” line is returning an error because the adUseClient constant does not have a defined value. The adUseClient constant is given the correct defined value when you add a reference to the Microsoft ActiveX Data Objects Library as mentioned earlier in my comment.
You will want to change the above to the following because you named the recordset rs rather than snpData:
I might have missed a couple of issues in your code, but the above should be enough to help you get started.
i feel happy for your reply but still i got some issue i leave reply in this
https://hoopercharles.wordpress.com/2012/11/25/connecting-to-an-oracle-database-with-visual-basic-6-0-on-windows-8-64-bit/#comment-5640
Thanks from heart becoz u spent time for me (the unknown person)
I just used your code Charles and it worked like a charm. Thanks a bunch.
CopyFromRecordset is not fast, so the whole premise of the article is wrong.
Philip,
Thanks for the comment.
The premise of this article is simply “Retrieve Data to Excel with a Macro using ADO”. Two methods are provided for accomplishing that task, a slow method that sets the value one Excel cell at a time using a nested loop, and a fast method that accomplishes the same using a single command “ActiveSheet.Range(“A2”).CopyFromRecordset snpData“. Please feel free to explain why CopyFromRecordset is not fast, and also offer a different method that is faster.
Charles, would you happen to be able to suggest an connection string to utilize Kerberos authentication now that kerberos authentication is available in 11.2+ without needing an ASO license. I am struggling to come up with a working connection string. I am looking to change my spreadsheets using ADO to use kerberos to connect to oracle. Thanks for your blog in general, I have used tit many times over the years.
-Mark Adams