B
BerkshireGuy
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
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