Get record set from filtered table (1 Viewer)

kiwipeet · Apr 11, 2013

Hi All.

I have a table of data in SQL server attached via Linked table (200K+ rows).

I have created a form which has a a subform which displays the contets of my linked table in datasheet view. I can filter rows and reduce my visible record set. (very cool so far

)

The number of records in my filtered set may vary form 1 record up to a few thousand rows.

Now I want to be able to execute a query against the filtered rows visible. Not the whole table.

Question: Is is possible to retrieve/use the primary key of a filtered record set in a subform datasheet view?

For example: (update LnkdTbl, set abc = "xyz' where Primary key in (pkey of my filtered record set.)

Any help or suggestions would be greatly appreciated.

Regards

Pete

CJ_London · Apr 11, 2013

In vba, refer to the subformcontrol.form.recordset should give you the filtered records from which you can create an insert or update query

kiwipeet · Apr 11, 2013

CJ_London said:
In vba, refer to the subformcontrol.form.recordset should give you the filtered records from which you can create an insert or update query

Many thanks for your quick reply. I'll start investigating that.

<Deep breath> About to venture into the VBA wilderness

kiwipeet · Apr 11, 2013

Woohoo!!!!!!

Code:

Private Sub Command7_Click()

Dim rs As DAO.Recordset

Set rs = mapto_subform.Form.Recordset

MsgBox mapto_subform.Form.Recordset.RecordCount

 With rs
    .MoveFirst
    Do While Not .EOF
        .Edit
        !priority = Me.maptofield.Value
        
        .Update
        .MoveNext
    Loop
End With
Set rs = Nothing

End Sub

It works a treat! Thanks very much!.

Performance is pretty bad though, so now i'll rewrite it to use a SQL statement with parameters rather than iterate through the recordset.

LPurvis · Apr 11, 2013

Can I suggest that you use the form's RecordsetClone, rather than the recordset itself. (Or at least a clone of the recordset.)
i.e.
Set rs = mapto_subform.Form.RecordsetClone
or
Set rs = mapto_subform.Form.Recordset.Clone

It won't necessarily shave much off the execution time (though should some, noticably or not). How many rows are you talking about here once filtered?

Opening a 200K+ row recordset first then filtering down sounds like a struggle to begin with. Do you filter before opening the form?

Cheers

kiwipeet · Apr 11, 2013

LPurvis said:
Can I suggest that you use the form's RecordsetClone, rather than the recordset itself. (Or at least a clone of the recordset.)
i.e.
Set rs = mapto_subform.Form.RecordsetClone
or
Set rs = mapto_subform.Form.Recordset.Clone

It won't necessarily shave much off the execution time (though should some, noticably or not). Cheers

Cheers, I will try that.

LPurvis said:
How many rows are you talking about here once filtered?

Cheers

The numbers of records could vary worse case ~17,000, diminishing down to a single record.

LPurvis said:
Opening a 200K+ row recordset first then filtering down sounds like a struggle to begin with. Do you filter before opening the form?
Cheers

So far that part is performing really well. I have 266,000 records in a table, and the filtering works remarkable well. However I was thinking I could exclude records once they have been marked/updated. So the working recordset will get smaller as we go.

However it's the update and the fact that i'm looping through each records that's the issue for me. When testing on my full size table Currently it take about 4-5 seconds to update 20-30 rows. If we were updated thousands it would be utterly unacceptable.

However I just noticed that on a small table the update is almost instantaneous... Hmm... So not sure if the issue is the sheer size of the table causing the delay.

kiwipeet · Apr 11, 2013

Eureka!!!

kiwipeet said:
However I just noticed that on a small table the update is almost instantaneous... Hmm... So not sure if the issue is the sheer size of the table causing the delay.

I changed the code so that it's using a Clone, it was hard to tell if it made much difference..

However.. The fact that it was working faster on small test table than it was on my large suggested to me it wasn't the loop causing the delay. It suggested to me it was Server side rather than in the code.

In both cases they where linked tables, and I had used an Identity column as a unique id column. I had selected that as a Unique identifier when adding the linked tables. So as far as Access was concerned other than the volume of records they are/were the same.

However... On my large table I hadn't defined the identity column as a primary key in SQL Server. Which means it didn't have an index... :banghead:

So for every update, SQL Server was doing a table scan to find the individual rows.

As soon as I defined the identity column as a Primary key, (and SQL Server added the implicit index) performance increased massively.

Edit: Just did a sample test. Updated 6200 rows in 22 seconds.

Thanks to all for their help.

LPurvis · Apr 12, 2013

>> As soon as I defined the identity column as a Primary key, (and SQL Server added the implicit index) performance increased massively.

The sort of thing that we'd have assumed as already being in place. Yes - a massive, massive issue and inevitable difference.
It explains everything. (I feel like Greg House when saying that.)

Cheers

Galaxiom · Apr 12, 2013

Re: Eureka!!!

kiwipeet said:
I changed the code so that it's using a Clone, it was hard to tell if it made much difference..

The difference is that you can do things to the clone that are not reflected in the form.

Otherwise as you navigate around the recordset you will make the form change.

Consequently the clone is often used. However, sometimes the developer might want to use the actual recordset. Understanding the difference allows you to make that choice.

LPurvis · Apr 12, 2013

The UI aspect being part of why it should have made some difference. (Even if not tangible - and certainly not noticeable compared to adding a PK index

)

misscrf · Sep 24, 2015

Just found this post, looking for a similar solution and this post has everything I needed to do exactly what I want to do. Thank you for post and answers!!!

Get record set from filtered table (1 Viewer)

kiwipeet

Programmer wannabe

CJ_London

Super Moderator

kiwipeet

Programmer wannabe

kiwipeet

Programmer wannabe

LPurvis

AWF VIP

kiwipeet

Programmer wannabe

kiwipeet

Programmer wannabe

LPurvis

AWF VIP

Galaxiom

Super Moderator

LPurvis

AWF VIP

misscrf

Registered User.

Similar threads

Users who are viewing this thread