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!
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!