effeciency of a filter

[smook]

I have a bit of a prob ... I have an access database with a sql server backend. i have a form that was returning about 120,000 records... which was a bad idea.

So i created the record source to be a query that only pulled a subset of the data - about 4000 records. The problem is, now that it is a limited query, I can't seem to insert. I assume this is because its treating it like a View, and i can't insert into it.

Would it help if I used a filter on the form instead? So i return all values but filter to only show the 4000 records... but doesn't that still return all records from sql server and then filter them in the front end of the application? or will access be "smart" enough to know that it should only retrieve 4000 records from sqlserver?

thanks again
sm

[puppydogbuddy]

I have a bit of a prob ... I have an access database with a sql server backend. i have a form that was returning about 120,000 records... which was a bad idea.

So i created the record source to be a query that only pulled a subset of the data - about 4000 records. The problem is, now that it is a limited query, I can't seem to insert. I assume this is because its treating it like a View, and i can't insert into it.

Would it help if I used a filter on the form instead? So i return all values but filter to only show the 4000 records... but doesn't that still return all records from sql server and then filter them in the front end of the application? or will access be "smart" enough to know that it should only retrieve 4000 records from sqlserver?

thanks again
sm

Filtering is not mentioned on the troubleshooter provided by microsoft, so it is hard to tell. I would try the steps included in the troubleshooter provided by Microsoft first.

http://support.microsoft.com/kb/328828

Hope this helps.

[puppydogbuddy]

I did some checking. The consensus is that filtering via the Access form is not going to help because all of the records would have been loaded by the form's record source SQL before the filter is applied.

Instead, you need to scope down the SQL select statement used as the form's record source in a manner that will not affect your users edit/update capability. Using a where clause instead of joins is one technique used to maintain updateability.

If you need more assistance, please post the SQL statement you used to load the subset of 4,000 records that made the query unupdateable.