- Joined
- Jan 26, 2010
- Messages
- 2
- Reaction score
- 0
I am trying to query a sql plus server through vba in excel. My code seems to be working but suffers a long delay when trying to open the recordset. Anywhere from two to five minutes. The database is large and I was contributing the delay to this but am honestly not sure.
Is it Possible to speed this up or is the delay just because of the large database it has traverse. For this example the recordcount is over 6000,
for a two day period. Thanks
Code:
Option Explicit
Public cnt As ADODB.Connection
Public Function getData()
Dim str As String
Dim sqlQuery As String
Dim rs As New ADODB.Recordset
Dim sOutput As String
Dim inc As Integer
inc = 0
Set cnt = New ADODB.Connection
str = ""
cnt.ConnectionString = str
cnt.Open
sqlQuery = "SELECT Flag FROM Downtime where StartTime >= '20-JAN-10 07:00:00'"
Set rs = GetResult(sqlQuery, ADODB.adOpenDynamic, ADODB.adUseClient, ADODB.adLockOptimistic)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
inc = inc + 1
Range("A" & inc).Value = rs.Fields(0).Value
Range("B" & inc).Value = rs.Fields(1).Value
rs.MoveNext
Loop
Else
MsgBox "no record found"
End If
rs.Close
Set rs = Nothing
cnt.Close
Set cnt = Nothing
End Function
Public Function GetResult(Cstr1 As String, typ As Integer, loc As Integer, clock As Integer) As ADODB.Recordset
Dim adrs As ADODB.Recordset
Set adrs = New ADODB.Recordset
On Error GoTo AdoErr
adrs.CursorLocation = loc
adrs.CursorType = typ
adrs.LockType = clock
[b][color=red]'Delay is on following line[/color][/b]
[b][color=red]adrs.Open Cstr1, cnt[/color][/b]
Set GetResult = adrs
Set adrs = Nothing
Exit Function
AdoErr:
MsgBox "Error : " & Err.Description, vbExclamation
Err.Clear
Set GetResult = Nothing
Resume Next
End Function
Is it Possible to speed this up or is the delay just because of the large database it has traverse. For this example the recordcount is over 6000,
for a two day period. Thanks