Dynamic Filter on Recordset

[tsosiel]

Hello. I have the following test subroutine where table tblTEST has field MsgID (numbers). This test sub emulates a problem within my code. I'm trying to dynamically filter an open recordset. My filter relies on the "MsgID] IN ( ... )" syntax used in queries; however, when this same tactic is used in a recordset, I get a run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Here's the TEST subroutine.
sub Test()
Dim rst As Recordset
Dim strFilter As String

Set rst = New ADODB.Recordset
rst.Open ("tblTEST&quot, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

strFilter = "MsgID IN (1,2)"
rst.Filter = strFilter
rst.Close
End Sub

Does anyone know of a solution??? I'm using Access2000 and ADO. Any assistance is appreciated.

- Lorentz

 

[lameid]

I still use DAO in 97. You could always try opening an SQL statement instead of a table with a filter...

sub Test()
Dim rst As Recordset

Set rst = New ADODB.Recordset
rst.Open ("Select tblTEST.* From tblTEST where MsgID IN (1,2)&quot, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

rst.Close
End Sub

Just a thought. You might be better off with a string variable for the SQL statment.

 

[cmmrfrds]

I don't believe ADO supports filtering on a list. Can you come up with another way such as using a range.

MsgID >= 1 and MsgID <=2

 

[tsosiel]

About using DAO.... I've pretty much converted all my systems to ADO. Would it be possible to use DAO's recordset filter in cases like this?

OR

Figure another schema like ranges? In fact, this method was my first replacement in doing this dynamic filtering. However, there is a limit as to how many characters can be contained in a filter - just can't remember the limit. Does anyone know? This puts the pressure on me becuase this dynamic list could contain at least 1 and at most 1000 items!!

I appreicate your input in this dilemma and thank you in advance.

- Lorentz

 

[lameid]

The only time I ever used a filter on a recordset in DAO was when I modified someone else's code. I've always just concatenated SQL strings togehter to open. I don't see why that would be a problem for ADO.

 

[tsosiel]

True. This particular function was inherited from a Access97 database and the methods/procedures followed as is. We're not finding out the differences between DOA/ADO... for now, they're 'bugs'.

I will see how much time it'll take to do the quick fix (using the range) and compare that to changing everything to SQL.

Thanks for the suggestions!! Again, more is better!

- Lorentz

 

[cmmrfrds]

Maybe if you explain why you are using a dynamic filter on the recordset instead of requerying the database then there could be other solutions. Is the ADO recordset coming from Access or another source. How often in a single session would you filter the recordset. If the recordset is small and the number of times that it is filtered in most sessions is near 0 or 1, then why not just do the requery of the database.

 

[tsosiel]

Here's a scenario: I have Parent A with 2 Children. Child 1 has 3 GrandChildren. Child 2 has 2 GrandChildren. Each GrandChild is allowed to send 1 or more messages to another Parent (besides Parent A) OR Either Children.

So, I have one recordset that keeps trak of which Parent-Children-Grandchildren and another for each set of message/s the GrandChild is sending. And since this can be more than one set, the dynamic filtering is used.

Once each recordset is iterated through, the filter of the second recordset is updated. And since the data is contained in tables, I have to gather these message (IDs) into a list and I use the 'Message IN (mes1,mes2,mes3,...)' OR the opposite could occur - 'Message NOT IN (mes1,mes2,mes3,...)'. The method worked great using DAO - but it's not so with ADO.

So, the approach of using ranges should replace this:
Message IN (mes1,mes2,mes3,...)
WOULD BECOME
Message=mes1 OR Message=mes2 OR Message=mes3 OR ...

This would work, but I'm afraid of some larger systems where the Parent-Child-GrandChild is great! Not to mention the messages!!

I hope that explains what I'm doing. If you have any suggestions/comments/questions.... let me know.

- Lorentz

 

[lameid]

How are you arriving at the elements in your IN statement? Could it be substituted for a select statment? I'm just thinking that if you solve this with SQL it will execute faster unless requerying will be a huge performance hit as cmmrfrds suggests.

I'm not sure I understand how your elements are related. Are they all in the same table and joined using a self join? Do you have a table for each parent, child and grandchild?

At this point, I think we may need the code in its entirety and a description of how many records are involved. Personally, I have not encoutered severe performance problems by requerying, but I see how that could be a problem for some systems.