PDA

View Full Version : Connect to Oracle with Excel VBA and store results of query?



jsabo
08-12-2013, 10:17 AM
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
08-12-2013, 11:16 AM
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
08-12-2013, 12:35 PM
Brilliant, i was able to take bits and pieces of what you posted and combine them with mine. Works great! Thanks!