Running Queries to Oracle from Excel 2000

[Kevin]

Greetings! I have a number of ODBC queries in Access that I run against an
Oracle database. I later use the result set in Excel. I would like to run
these queries directly from Excel and save the whole Access step so that the
process is more seamless for my enduser.

Would someone please point me to some primer on this kind of data access
programming in Excel VBA. I'm very familiar with VBA, but this is new
territory for me and I need a good place to start. I have several books that
I have consulted, but I need something more specific to what I am trying to
do.

Thanks in advance!

 

[VBA Dabbler]

Try this, it worked the last time I used it:

Sub GetFlowCalGQSourceList()
Dim strUserName As String
Dim strPassword As String
Dim strDatabaseName As String
Dim strProviderName As String
Dim strConnString As String
Dim Cnn As New ADODB.Connection
Dim SQL_Text As String
Dim CurrentRS As ADODB.Recordset
Dim i As Integer
Dim j As Integer

strProviderName = "OraOLEDB.Oracle"
strDatabaseName = "your server name"
strUserName = "your user ID"
strPassword = "your user password"

strConnString = "Provider=" & strProviderName & ";" & _
"Data Source=" & strDatabaseName & ";" & _
"User ID=" & strUserName & ";" & _
"Password=" & strPassword
Cnn = strConnString
Cnn.Open
Set CurrentRS = New ADODB.Recordset
SQL_Text = "This is your SQL for the query"
CurrentRS.Open SQL_Text, Cnn, adOpenStatic
'Do with your recordset what you will
CurrentRS.Close
Set CurrentRS = Nothing
Cnn.Close
Set Cnn = Nothing
End Sub

You'll probably want to turn on the latest version of "Microsoft ActiveX
Data Objects X.X Library" in the VBE Tools\References dialog - it won't work
without it.

Regards,
VBA Dabbler

 

[codecom]

Thanks VBA Dabbler,
Is it possible to pass a list of values to the sql string from a range of
cells in excel (to filter)

 

[VBA Dabbler]

Yes. The sql string is just that, a string. You can concatenate variables
into the string to make a dynamic string.

You could also drive the string value from a userform, if you like - I've
done it many times.