Recordset keeps returning EOF and shouldn't be.
 

Hello everyone,

The following code should grab policy number from cell B from the
activeworksheet, then go through a recordset (that is a SQL table) to
find that recordset.

However, the following code is always returning EOF even though I am
hardcoding a policy number that I know exists in the table.


Here it is:

Dim oConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set rng = Cells(Rows.Count, 1).End(xlUp)

(connection properties changed for privacy)

oConn = "Driver={SQL Server};" & _
"Server=OurSQLServer;" & _
"Database=OverDatabase;" & _
"Uid=joesmoe;" & _
"Pwd="

oConn.Open

'rst.Open "Select polnum, EntryDate From tblOurTable", oConn,
adOpenStatic

For i = rng.Row To 3 Step -1
'strPolicyNumber = Cells(i, "B").Value


strPolicyNumber = "Z9999858" <--- This policy number is there, but
recordset returns EOF


rst.Open "Select polnum, F_EntryDate From ipdSysUser.tblQueueLoc_NBPROD
WHERE polnum = ' " & strPolicyNumber & "'", oConn, adOpenStatic


If rst.EOF Then GoTo NextPolicy:
Debug.Print rst("polnum")

NextPolicy:
rst.Close
Next i

Thanks,
Brian

Recordset keeps returning EOF and shouldn't be.
 

After your "rst.Open..." command, try putting in the following:


if rst.RecordCount = 0 Then
GoTo NextPolicy
Else
rst.MoveLast
rst.MoveLast
End If


The MoveLast causes the DB handler to populate the recordset, and th
MoveFirst sets the first record as the current one

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=54470