Vba to query SQL Plus

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top