The following code is slightly modified from some boilerplate I use for almost every project I work on (in Access). All the other versions of this code work perfectly in this db. The tables are all actually in SQL Server but they are linked in this Access db.
Here's the weird part...When I run the code and get down into the "With rs" and do the ".Open", the next step "Do While Not .EOF" skips to the bottom of the "Do"...I am at .EOF! But if I get a copy of the ".Source" SQL Statement and paste it into a blank SQL query on the non-code Access main window, I get 835 records from that query. How could I be getting back an empty recordset when this query should return 835 records?
I've tried changing my .CursorType and .LockType, to no avail.
Anyone have any thoughts about what I could try in troubleshooting this?
Public Sub test()
Dim db As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sFromDate As String
Dim sToDate As String
Dim sSubUnit As String
sFromDate = "1/1/2013"
sToDate = "1/31/2013"
sSubUnit = "2???"
' Make a connection to the db
Set db = CurrentProject.Connection
With rs
.ActiveConnection = db
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = "SELECT SCEVENT.CLIENT_ID, SCATTEND.EMP_ID, SCATTEND.STARTDATE, SCATTEND.SVC_ID " & _
"FROM SCATTEND LEFT JOIN SCEVENT ON SCATTEND.SCEVENT_ID = SCEVENT.ID " & _
"WHERE (SCATTEND.STARTDATE Between #" & sFromDate & "# And #" & sToDate & "#) " & _
"AND (SCATTEND.SUBUNIT_ID Like '" & sSubUnit & "') " & _
"AND ((APPTYP_ID IS NULL) OR (APPTYP_ID IN (1,2))) " & _
"ORDER BY SCEVENT.CLIENT_ID, SCATTEND.EMP_ID, SCATTEND.STARTDATE, SCATTEND.SVC_ID;"
.Open
Do While Not .EOF
MsgBox "there are records!"
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub
