Running a long query

[Maury Markowitz]

Throughout my code, in hundreds of places, I have code that looks like
this...

Set rstRunner = New ADODB.Recordset
rstRunner.Open theSql, cnn, adOpenStatic, adLockReadOnly

cnn is a globally defined connection string to our sql server. This
works great for the vast majority of our queries, but now I'm making a
report that takes about 45 seconds to generate and it's timing out. So
I did this...

Set cRunner = New ADODB.Connection
cRunner.CommandTimeout = 120 ' 2 minutes, just to be safe
cRunner.Open cnn

Set rstRunner = New ADODB.Recordset
Set rstRunner.ActiveConnection = cRunner
rstRunner.Open theSql, cRunner, adOpenStatic, adLockReadOnly

This fails to return any rows. I tried it both with and without the
ActiveConnection line. Can anyone offer any advice?

Maury

 

[Jim Thomlinson]

Here is how I implimented my ADODB query. It uses early binding. Basically
you pass in the select statement and the function returns the record set to
you. It uses a globally defined user name and password.

Private Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
ByVal strWhere As String, ByVal strOrderBy) As ADODB.Recordset
'Dim strConnection As String 'String used to connect to the database
Dim cn As ADODB.Connection

On Error GoTo Errorhandler

Set cn = New ADODB.Connection
With cn
.ConnectionTimeout = 240
.ConnectionString = "DSN=" & m_cDSN & ";Uid=" & LOGIN_NAME & ";Pwd=" &
LOGIN_PASSWORD & ";"
.Open
End With
'Set application parameters
Application.StatusBar = "Refreshing Data. Please Wait..."

'Create the recordset object
Set RunQuery = New ADODB.Recordset
'Define recordset settings
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

On Error GoTo QueryError
'Execute the query
RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, cn, , , adCmdText
On Error GoTo Errorhandler

'Housekeeping
Set RunQuery.ActiveConnection = Nothing
Application.StatusBar = False
Exit Function

Errorhandler:
modErrors.HandleError m_cModule, "RunQuery"
Set RunQuery.ActiveConnection = Nothing
Application.StatusBar = False
Exit Function