Query Oracle DB with large schema

[Ian Stock]

Hi,

I have created an excel macro to automatically run
multiple queries against an oracle database using ms query
and an odbc connection. When I look at the vb code it
creates a connection for each query. This takes a very
long time to run as the schema on the oracle db is ~1500
tables and I think it is loading the schema definition at
the beginning of each connection. The code looks like this:

ActiveSheet.QueryTables.Add(Connection:=........

a) Is there a way to run multiple queries in the one
connection?
b) Does the above method capture the schema definition, if
so is there a way of turning it off?

Any help would be greatly appreciated.
Ian

[Ian Brown FCA]

Ian,

Not familiar with running queries against Oracle using ODBC because I
couldn't get the ODBC connection to work no matter what. However, my work
around may be of interest to you. I control a SQL*Plus query from Excel and
spool the output to a .csv or .txt file on disk. I then use a TXT ODBC
driver to bring the data into the query table. (I've used Data Browser /
Discoverer 2000 and Discoverer 3 to do the same thing - all under the
control of Excel).

Just a thought.

Ian

"Ian Stock" <ian....@cimsglobal.com> wrote in message
news:112f01c3409c$d9f0ab10$a101...@phx.gbl...

[Ian Stock]

From one Ian to another...

thanks for the suggestion, I think that would work.
I actually found a way around it, my problem was that you
can't have multiple destinations for multiple queries
using the QueryTable object, and the destination property
is read-only once the connection is created.
For multiple queries I decided to copy each result set
(one cell in this case) to the proper destination I wanted.
See cutdown version of code below:

Sub GetTableCounts()

'create db cxn and loop for all tables

Dim sSQLStr As String

sSQLStr = "select count(*) from " & ActiveSheet.Range
("C3").Value 'initial sql

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=***;UID=***;PWD=***;DBQ=***;"), _
Array("=0;")), Destination:=Range("E1"))
.CommandText = Array(sSQLStr)
.Name = "Qry1"
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False

Range(sWkEndCol & "3") = Range("E1") 'copy
value to correct cell

For i = 4 To 24 'corresponds to row holding
tablenames

sSQLStr = sConSQL & ActiveSheet.Range("C" &
i).Value 'change sql
.CommandText = Array(sSQLStr)
.Refresh BackgroundQuery:=False
Range(sWkEndCol & i) = Range("E1") 'copy
value to correct cell

Next i
End With
End Sub

>.
>