ADODB Recordset (1 Viewer)

[LaurenceFrost]

Hi everyone,

I am using an ADODB Recordset to run a query in the following way:

rs.Filter = "ShiftId=" & ShiftId & " AND BookingTime <=" & timeSlot & " AND BookingId <> " & BookingId

rs.Open "qryFindBookingsForTimeSlot", CurrentProject.Connection,
adOpenKeyset,
adLockOptimistic

The problem is that it takes a long time to read every table row and format it into a collection (recordset). Although it appears that only the specified rows are in the recordset, but as soon as you remove the filter, the whole table is then contained without having to call 'requery'! How can I make the recordset ONLY contain the rows that meet all the criteria specified in the filter? I have tried

rs.open ("<TEXT FROM QUERY">) but the query text is about 1000 characters long. Any ideas?

Thanks in advance for any response.

 

[GJT]

I do recall when I first started using ADO - it was apparent that there were delays inherrent in its use when compared to DAO.

Unfortunately, I cannot recall what the work-round was (this was more than 2 years ago !) but it may have something to do with the recordset type and locking you have specifed. Could you try using pessimistic locking and dynasets instead??????

Failing this - maybe you could try using DAO for comparison purposes?

Good luck!