If I do the same using a linked table (in an other access file)
You should also look for "Filter" property of the recordset object to filter only the desired records and then interact with them in the same way (see VB6 Help in MS-Access code window), or create a "QueryDef" object to run a query and use it as a recordset too (a little bit more tricky). (A) Use the Filter property of a recordset to limit it's record and then open another new recordset on it.
This works fine as long as the table is in the current access file. The ADODB Recordset also contains a built-in filter method. The Recordset object is an essential component in Access development, but it often trips up even experienced developers. Since the ADODB recordset has a separate VBA library, you can use this method in all Office applications (Word, Access, Outlook, Excel, Powerpoint, etc.). I use the lines of code below to set the index for a recordset. #1 Set your references versions of Access that are earlier than Access 2000. Tell me if you want another aproach.
Susan Harkins explains 12 common Recordset … Syntax expression .OpenRecordset(Name, Type(Optional), Options(Optional), LockEdit(Optional)) expression A variable that represents a Database object. Author(s) Dev Ashish (Q) How can I filter a recordset based on a criteria? There are several ways to create or get a recordset: Create a new Recordset from a table or query in your database; Use the Recordset property of an Access object, such as a bound Form; Clone an existing recordset; Create a new Recordset by applying a Filter on an existing recordset
These can be arguments to use an ADODB Recordset.
Open a recordset.
The Recordset property returns the recordset object that provides the data being browsed in a form, report, list box control, or combo box control. Modules: Filter a recordset.
How to Use Recordset A recordset is a data structure that consists of a group of database records, and can either come from a table, query or SQL. If a form is based on a query, for example, referring to the Recordset property is the equivalent of cloning a Recordset object by using the same query.
With myRS.Filter = "ReturnDate BETWEEN #01/01/1997# AND #01/01/2003# AND FundID=24835" Set myRS2 = .OpenRecordset End With--Terry Kreft MVP Microsoft Access For example, in the sub sFilterRS, I'm limiting rs2 to a single record by setting a filter on rs1.
I hope I've helped. In this page I will not cover all aspects of the ADODB Recordset.
Copyright 2020 Recordset filter vba access