I do a lot of VB/VBA development (and I'm a DBA, weird isn't it?).
A few thoughts:
1) Don't use ODBC!
Use ADO instead, it's faster (uses OLE DB, circumventing the Jet layer), has better error handling (Errors collection), allows for disconnected recordsets, and client side cursors. Did mention it's way faster? It will require you to download the latest MDAC from MS. I recommend MDAC 2.7 RTM.
2) Don't embed SQL in your VB code.
It's messy, hard to maintain, and a royal pain to debug. It's much "cleaner" if you seperate your SQL into a view on the database side. You can embed the query during development but I *highly* recommend you move it to a view once you get the recordset you require. Work with your DBA on this. If you *must* code a query into your VB code then move the complex query into a view and reference it as "select * from my_view where fld = ?".
3) Use bind variables wherever possible.
If you can't use bind variables easily (you probably shouldn't be doing what you are doing) then consider calling out through ADO and "alter session set cursor_sharing=force". See below for an simple example of using a bind variable with ADO. Note, Excel will only use a ODBC query for External Data Tables.
4) Use the ADODB.Errors collection for error handling.
Every VB routine that references the DB should use the ADODB.Errors collection. No funtion/sub is too small to avoid this.
5) Have Oracle calculate as much as possible.
It's far more efficient at handling large datasets and almost any function you can think of in Excel can probably be coded as a PL/SQL function or field in a view. Again, work with your DBA on this. You can flesh out your query locally but it will be much faster in the DB. This will be particularly true in the upcomming 10g with it's new MODEL clause. {nifty examples BTW Tom at OracleWorld}
6) Use BeginTrans and EndTrans to batch update.
Where possible use the BeginTrans and Commit/Rollback methods of the Connection object when updating large #'s of records. It's *much* faster than commiting on every change. You can also use the UpdateBatch method of the Recordset object. Again, this is ADO, not ODBC.
7) Let Excel to it for you.
Excel can handle your parameters natively without needing to code all of this up like you have done. None of the code you have shown is required if you simply wish to generate a filtered table in Excel.
Here's how I do it:
a) Move your query into a view in Oracle. Let it return all of the data. You will filter when you build the query or with AutoFiltering in Excel.
b) Insert the view in a worksheet (Tools/External Data) and in the Query Builder create your filter such that "fld = [my_param1]" (use the brackets to indicate a query parameter).
b) Return the data to Excel. When the query runs you will be asked to provide values for the parameters that have [] around them. This will allow Oracle to filter your view.
c) Once you get the data into Excel, if you right-click on the table you can use the Parameters... option to set the values a) for all time, b) for one run, or c) referencing a set of cells in your spreadsheet.
I use this technique to help track ticket assignments in our Helpdesk system in a spreadsheet. It allows them to type their name in as the ASSIGNED cell of the workbook, which Excel uses as a parameter for the table. I also set the table to auto-update every 15 minutes. To ease load I created this as a Materialized View also. This is the best of all worlds, speed, filtering, and views from Oracle, ease of use from Excel.
Hope this helps.
Mike
Sub ParameterExample()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
' Set the command's connection using a connection string.
cmd.ActiveConnection = Provider=OraOLEDB.Oracle;User ID=scott;Password=tiger;Data Source=oraDSN;
' Set the command's text, and specify that it
' is an SQL statement.
cmd.CommandText = "Select empno from emp where deptno = ?"
cmd.CommandType = adCmdText
' Set up a new parameter for the stored procedure.
Set prm = cmd.CreateParameter("deptno", adInteger, adParamInput, , 10)
cmd.Parameters.Append prm
' Create a recordset by executing the command.
Set rs = cmd.Execute
' Loop through the recordset and print the first field.
Do While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Loop
' Close the recordset.
rs.Close
End Sub
Oracle Overview of OleDB (with Code Samples):
</code>
http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95498/intro.htm
Good Overview of MS Data Technologies:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheEvolutionofData-AccessTechnologies.asp <code>