Need Help With ADO Recordset and Loop

Hello,

I have the following code, which works, but it drags.

What I need to do, is loop through each worksheet and if the name has
Detail in it, read in the policy number and then connect to another
datasource to lookup information based on this policy. Any suggestions
would be appreicated:

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
Dim strPolicyNumber As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

Set rng = Cells(Rows.Count, 1).End(xlUp)

' zzzzed out server information
strConn = "Driver={SQL Server};" & _
"Server=zzzzz;" & _
"Database=zzzzz;" & _
"Uid=izzzzz;" & _
"Pwd="

Set cn = New ADODB.Connection

cn.Open strConn

Set rst = New ADODB.Recordset

rst.Open "Select polnum, F_EntryDate From tblPolicyInformation", cn,
adOpenForwardOnly, adLockReadOnly, adCmdText

For Each ws In wb.Worksheets
If InStr(1, ws.Name, "Detail") > 0 Then
ws.Select
ws.Range("I:I").Select
rst.MoveFirst
For i = rng.Row To 3 Step -1

rst.MoveFirst ' Make sure pojnter is at the top
strPolicyNumber = Cells(i, "B").Value
If strPolicyNumber <> "" Then
rst.Find "polnum = '" & strPolicyNumber & "'" -
This is where a delay happens
If Not rst.EOF Then
ws.Cells(i, "I").Value = rst("F_EntryDate")
End If
End If
Next i
End If
Next ws

Thanks,
Brian

First, is the field indexed? How many records are there in the database?
That will have a big effect on the speed of a .Find. If it is not there
already, and if you can get it done, have an index put on the policy number
in the database.

Next: you are using a forward only cursor and (since not otherwise
specified) it is a server-side cursor. Try adOpenStatic: it is better for
scrolling bidirectionally through the data and should speed up a .Find. And
try setting your Connection object's CursorLocation to adUseClient (this must
be done before you open the connection). This lets your recordset operate
independently of other traffic on the server.
--
- K Dales


"BerkshireGuy" wrote:

> Hello,
>
> I have the following code, which works, but it drags.
>
> What I need to do, is loop through each worksheet and if the name has
> Detail in it, read in the policy number and then connect to another
> datasource to lookup information based on this policy. Any suggestions
> would be appreicated:
>
> Dim cn As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim strConn As String
> Dim strPolicyNumber As String
> Dim wb As Workbook
> Dim ws As Worksheet
> Dim rng As Range
> Dim i As Integer
>
> Set wb = ActiveWorkbook
> Set ws = wb.ActiveSheet
>
> Set rng = Cells(Rows.Count, 1).End(xlUp)
>
> ' zzzzed out server information
> strConn = "Driver={SQL Server};" & _
> "Server=zzzzz;" & _
> "Database=zzzzz;" & _
> "Uid=izzzzz;" & _
> "Pwd="
>
> Set cn = New ADODB.Connection
>
> cn.Open strConn
>
> Set rst = New ADODB.Recordset
>
> rst.Open "Select polnum, F_EntryDate From tblPolicyInformation", cn,
> adOpenForwardOnly, adLockReadOnly, adCmdText
>
> For Each ws In wb.Worksheets
> If InStr(1, ws.Name, "Detail") > 0 Then
> ws.Select
> ws.Range("I:I").Select
> rst.MoveFirst
> For i = rng.Row To 3 Step -1
>
> rst.MoveFirst ' Make sure pojnter is at the top
> strPolicyNumber = Cells(i, "B").Value
> If strPolicyNumber <> "" Then
> rst.Find "polnum = '" & strPolicyNumber & "'" -
> This is where a delay happens
> If Not rst.EOF Then
> ws.Cells(i, "I").Value = rst("F_EntryDate")
> End If
> End If
> Next i
> End If
> Next ws
>
> Thanks,
> Brian
>
>