Connect to Oracle with Excel VBA and store results of query?

[jsabo]

Hello,

I am trying to connect to Oracle using VBA through Excel, run a SQL query, and store the results in the active workbook. Here is the code I have so far (kindly borrowed from this forum):

Sub ActivityLogger()
'ActivityLogger Macro
'Keyboard Shortcut: Ctrl+Shift+A
Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim recordCount As Long
dbConnectStr = "Provider=msdaora;User ID=;Password=;Data Source=" & ";"
'User Id=userId" & "; Password=" & "password"
'The statement above has been commented out. I use the statement below to prompt the user for the userId and password - which is what I prefer!
con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString
'This is an example SQL code that you might want to run
'Select * From MyTable
SQL_String = "Select * from AC_OVERDUEPOMILESTONE_V"
recset.Open SQL_String, con
recset.MoveLast
recordCount = recset.recordCount
recset.MoveFirst
Do While Not recset.EOF = True
'Have a loop here to go through all the fields
recset.MoveNext
Loop
recset.Close
End Sub

As of now, I am connecting to the database but I am getting an error reading, "Rowset does not support fetching backward." and the following line is highlighted in debug:

recset.MoveLast

any ideas? Thanks!

[Kenneth Hobs]

I know some say that you need to move to the last record to get the record count but I am not sure about that. What happens if you do not execute that command?

You left some connection details out but that is fine as we don't have your oracle database.

I use ADO. Using DAO is not the same.

Here is one way to iterate fields and rows.

'Connection Strings, http://www.connectionstrings.com/

'http://msdn.microsoft.com/en-us/library/ms808325.aspx

Sub ADO()
'   Set Reference in Tools to: Microsoft ActiveX Data Objects 2.x Library
    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    Dim Col As Integer, Row As Long, s As String
    
'   Database information
    DBFullName = ActiveWorkbook.Path & "\NWind2003.mdb"
'Exit?
    If Dir(DBFullName) = "" Then Exit Sub

'Clear any existing data from activesheet
   Cells.Clear

'   Open the connection
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Cnct
    
'   Create RecordSet
    Set RecordSet = New ADODB.RecordSet
'   Record locking
  RecordSet.CursorType = adOpenKeyset
  RecordSet.LockType = adLockOptimistic
    
    With RecordSet
'       Filter
        'Src = "SELECT * FROM Products WHERE ProductName = 'Watch' "
        'Src = Src & "and CategoryID = 30"
        Src = "SELECT Orders.CustomerID, Orders.OrderDate " & _
          "FROM Orders " & _
          "WHERE (((Orders.OrderDate) " & _
          "Between #8/1/1994# and #8/30/1994#))"
        RecordSet.Open Source:=Src, ActiveConnection:=Connection

'       Write the field names
        For Col = 0 To .Fields.Count - 1
           Range("A1").Offset(0, Col).Value = RecordSet.Fields(Col).Name
        Next Col
        
'       Write the recordset
        Range("A1").Offset(1, 0).CopyFromRecordset RecordSet
        Dim a As Variant
        .MoveFirst
        a = RecordSet.GetRows
        MsgBox LBound(a), , UBound(a)
        MsgBox a(0), , a(1)

        If .RecordCount < 1 Then GoTo endnow
        .MoveFirst
        For Row = 0 To (.RecordCount - 1)
          'Debug.Print CStr(.Fields(Row).Value)
          .MoveNext
        Next Row
  End With
endnow:
    Set RecordSet = Nothing
    Connection.Close
    Set Connection = Nothing
End Sub

[jsabo]

Brilliant, i was able to take bits and pieces of what you posted and combine them with mine. Works great! Thanks!