Filter for ADO Recordset bound to subform (1 Viewer)

WojtekKowalski

New member
Local time
Today, 18:54
Joined
Jan 17, 2021
Messages
5
Hi:)

I have one main form(place where I filter content in subform by comboboxes) and one subform(datasheet where data are being filtered).
I have to work on data what come from sql server. I don't wont to use standard query based on linked tables as subform's Record Source, because it doesn't work that fast as in case where I have linked tables from MS Access database stored on same PC.

I found this code and placed in on "Open event" in subform.

Code:
Dim cn As ADODB.Connection
 Dim rs As ADODB.Recordset
 
 'Use the ADO connection that Access uses
 Set cn = CurrentProject.AccessConnection
 'Create an instance of the ADO Recordset class,
 'and set its properties
 Set rs = New ADODB.Recordset
 With rs
 Set .ActiveConnection = cn
 .Source = "SELECT * FROM dbo_Stan"
 .LockType = adLockOptimistic
 .CursorType = adOpenKeyset
 .Open
 End With
 'Set the form's Recordset property to the ADO recordset
 Set Me.Recordset = rs
 Set rs = Nothing
 Set cn = Nothing

It works perfectly fine. But I'm wondering how am I suppose to filter subform based on something like this from my main form.
I thought about running same code with small modification on event "After Update" in combobox
Code:
...
rs.Filter = "TValue=" & Me.MyComboBox
Set Me.MySubform.Recordset = rs
...
But unfortunately it seems I can not set subform recordset from main form and secondly doing this I would need to get data from sql server each time I set some filter.
Setting filter on subform also doesn't work - it seems to be not possible to use it once there is Recordset in Subform Record Source.
What's the best way to filter subform from comboboxes on main form, taking into consideration there is Recordset set in Record Source Subform always on "Open form" event.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:54
Joined
May 21, 2018
Messages
5,690
I don't wont to use standard query based on linked tables as subform's Record Source, because it doesn't work that fast as in case where I have linked tables from MS Access database stored on same PC.
Any reason for not using a pass through query, or are you saying the pass through is slow?
 

WojtekKowalski

New member
Local time
Today, 18:54
Joined
Jan 17, 2021
Messages
5
Any reason for not using a pass through query, or are you saying the pass through is slow?
Yes it's slower. Whenever I scroll data then I have some lags. Probably it's because Ms Access constantly communicate itself with linked table on SQL Server. Once subform bases on Recordset then it is fast, because data are stored on memory not on external source I think
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:54
Joined
May 21, 2018
Messages
5,690
OK, I just want to check because what you stated is different
don't wont to use standard query based on linked tables as subform's
So you are not querying the linked table, but in fact using a pass through and the pass through is slow?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:54
Joined
May 21, 2018
Messages
5,690
Have you tried to clone the mainform recordset, Apply the filter to the clone, then bind the subform to the cloned recordset?
 

WojtekKowalski

New member
Local time
Today, 18:54
Joined
Jan 17, 2021
Messages
5
OK, I just want to check because what you stated is different

So you are not querying the linked table, but in fact using a pass through and the pass through is slow?
New data srouce=>From Database=>From Azure Database.
That's the way I have linked sql view to my database. I used pass through queries in the past to use sql server stored procedures as far as I remember. So you mean I should create pass through query to this sql view?
Have you tried to clone the mainform recordset, Apply the filter to the clone, then bind the subform to the cloned recordset?
That would mean I need to change subform's record source each time I would like to filter data. I'm not sure if it will be stable solution.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 17:54
Joined
Feb 19, 2013
Messages
13,580
according to your code, rs is a local variable to the subform

have you tried

Me.MySubform.form.Recordset.Filter = "TValue=" & Me.MyComboBox
Set Me.MySubform.form.Recordset = Me.MySubform.form.Recordset

Setting filter on subform also doesn't work
the form filter (and sort) actions only work with DAO. If you are using an ADO recordset, you will need to write your own shortcut menus.

edit: - rookie mistake, the subform recordset is part of the form collection of the subform - have modified the code to include in red
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:54
Joined
Jan 20, 2009
Messages
12,391
Hi:)

I have one main form(place where I filter content in subform by comboboxes) and one subform(datasheet where data are being filtered).
I have to work on data what come from sql server. I don't wont to use standard query based on linked tables as subform's Record Source, because it doesn't work that fast as in case where I have linked tables from MS Access database stored on same PC.
You are still using the linked table in the code you posted.

If you want to get it straight from the SQL database you need to use an SQL server connection string in the connection object and SQL Server syntax in the query.

Get the recordset, disconnect it by setting the ActiveConnection to Nothing and Set it as the form's recordset. Now it is just in memory and won't hit the database again. Use the Filter property of the recordset, not the Form's recordset filter. (The form's recordset filter is expecting DAO.)

Refresh the form's recordset after applying the filter with:
Code:
Set Me.Recordset = Me.Recordset
(Don't ask me why exactly this code but it is what I learnt long ago.)
 

Users who are viewing this thread

Top Bottom