Thread: Filter equivalent for Access bound form

Access 2003
pg 7.4.3

What is the recommended approach to get the equivalent of a filter for an
Access bound form?

The filter property of a bound form seems to be the equivalent of an Access
client-side query of SELECT * FROM form.recordsource WHERE form.filter. That
would have a performance penalty.

I change the SQL of pass-through queries dynamically at runtime to use as
record sources for reports. That wouldn't work for forms, as not updatable.

Is the best approach to use an updatable view as the record source, then
change the view definition at runtime as with a passthrough query?

Would need a unique view name for multi-user reasons, perhaps including part
of a timestamp string or sequence. The original view's insert, update and
delete rules should still have valid syntax except for the new view name, as
only the WHERE clause and view name are changed. The rules could be updated
with VBA code at the same time as the view change.

Thanks,

David P. Lurie

Hello,

Using the filter property of a bound form is not a problem I think, at least with Access 2000. The filtering is done
server-side,and only the necessary data is fetched on the client. I don't think this is a performance penalty. 

But with Access 2003, I agree I have problems too. For an unknown reason, filtering is apprently done client-side,
don'task me why... 

I have to check that...

Philippe Lang

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de David P. Lurie
Envoyé : jeudi, 5. août 2004 17:47
À : pgsql-odbc@postgresql.org
Objet : [ODBC] Filter equivalent for Access bound form

Access 2003
pg 7.4.3

What is the recommended approach to get the equivalent of a filter for an Access bound form?

The filter property of a bound form seems to be the equivalent of an Access client-side query of SELECT * FROM
form.recordsourceWHERE form.filter. That would have a performance penalty. 

I change the SQL of pass-through queries dynamically at runtime to use as record sources for reports. That wouldn't
workfor forms, as not updatable. 

Is the best approach to use an updatable view as the record source, then change the view definition at runtime as with
apassthrough query? 

Would need a unique view name for multi-user reasons, perhaps including part of a timestamp string or sequence. The
originalview's insert, update and delete rules should still have valid syntax except for the new view name, as only the
WHEREclause and view name are changed. The rules could be updated with VBA code at the same time as the view change. 

Thanks,

David P. Lurie




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Hi,

After using ethereal and making changes for a while, I came to the conclusion that there is apprently a problem with
AccessDatabases in 2000 format used under Access 2003. After converting my database into the native 2003 format (not
compatiblewith Access 2000, though) I don't have any more problems, and the application is much more responsive, by the
way.

Filters in bound forms work fine, and don't suffer any performance penalty... And the recordset remains updatable,
whichis great for a RAD tool like Access. 

I hope this helps.

Philippe Lang

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de David P. Lurie
Envoyé : jeudi, 5. août 2004 17:47
À : pgsql-odbc@postgresql.org
Objet : [ODBC] Filter equivalent for Access bound form

Access 2003
pg 7.4.3

What is the recommended approach to get the equivalent of a filter for an Access bound form?

The filter property of a bound form seems to be the equivalent of an Access client-side query of SELECT * FROM
form.recordsourceWHERE form.filter. That would have a performance penalty. 

I change the SQL of pass-through queries dynamically at runtime to use as record sources for reports. That wouldn't
workfor forms, as not updatable. 

Is the best approach to use an updatable view as the record source, then change the view definition at runtime as with
apassthrough query? 

Would need a unique view name for multi-user reasons, perhaps including part of a timestamp string or sequence. The
originalview's insert, update and delete rules should still have valid syntax except for the new view name, as only the
WHEREclause and view name are changed. The rules could be updated with VBA code at the same time as the view change. 

Thanks,

David P. Lurie




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

--- "David P. Lurie" <dbase4@hotmail.com> wrote:

> Access 2003
> pg 7.4.3
>
> What is the recommended approach to get the
> equivalent of a filter for an
> Access bound form?
>
> The filter property of a bound form seems to be the
> equivalent of an Access
> client-side query of SELECT * FROM form.recordsource
> WHERE form.filter. That
> would have a performance penalty.

How much testing have you done?  I have never had a
problem with this, but then, I don't have to deal with
any huge datasets.

>
> I change the SQL of pass-through queries dynamically
> at runtime to use as
> record sources for reports. That wouldn't work for
> forms, as not updatable.
>
> Is the best approach to use an updatable view as the
> record source, then
> change the view definition at runtime as with a
> passthrough query?

Why would you need to do this?  If you want to show
different data to different users, then you reference
the user name (e.g. current_user or session_user) in
your view definition.

>
> Would need a unique view name for multi-user
> reasons, perhaps including part
> of a timestamp string or sequence. The original
> view's insert, update and
> delete rules should still have valid syntax except
> for the new view name, as
> only the WHERE clause and view name are changed. The
> rules could be updated
> with VBA code at the same time as the view change.

Or you could just use unbound controls.  Not really
that difficult, but a lot of coding.  Most of us are
using Access in order to avoid all of that :-)

>
> Thanks,
>
> David P. Lurie
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

"Jeff Eckermann" <jeff_eckermann@yahoo.com> wrote in message
news:20040805184412.78858.qmail@web20826.mail.yahoo.com...
> How much testing have you done?  I have never had a
> problem with this, but then, I don't have to deal with
> any huge datasets.
>
I don't have any large datasets; largest table at present only has about
10,000 records.

Had created 2 views against the largest recordset for testing, one with a
WHERE clause, and 2 bound forms, each using one of the views as a
recordsource. The form using the view without the WHERE clause had the
conditions in the WHERE clause used as a filter.

I didn't put in timing code, but the filtered form seemed a little slower.
Dropped and rebuilt the test views, forms, etc. and now don't see much
difference.

Phillipe had noticed difference between Access 2000 and Access 2002/2003
mdb, but I had already been using 2002/2003, as will be distributing runtime
executable to my employees.

Can't explain the difference in performance from first testing, but will try
using filters in upcoming production version unless real world performance
suffers.

> >
> > I change the SQL of pass-through queries dynamically
> > at runtime to use as
> > record sources for reports. That wouldn't work for
> > forms, as not updatable.
> >
> > Is the best approach to use an updatable view as the
> > record source, then
> > change the view definition at runtime as with a
> > passthrough query?
>
> Why would you need to do this?  If you want to show
> different data to different users, then you reference
> the user name (e.g. current_user or session_user) in
> your view definition.
>

Was just creating unique name for the temporary view, for which string
containing current_user would certainly be easier. All of my employees will
have similar table permissions at the present time, as small office and
similar tasks.

David P. Lurie

Hi,

What I noticed with Access 2000 MDBs under Access 2003 has more to do with a bug than with a performance decrease...

Anyway, it would be interesing to have an idea of the timings you are talking about. I'm not really surprised the view
withthe "built-in filter" gives better results. I always try to push as much code as possible on the server, this is
alwaysfaster. 

What you could try maybe is to link your bound form to a query, itself linked to the linked tables, instead of linking
yourform to the linked tables directly. This might give you slightly better results, and the recordset is still
updatable.Make some tests with an MDE as well! 

Your results, timings are welcome!

Philippe

-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de David P. Lurie
Envoyé : vendredi, 6. août 2004 17:32
À : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] Filter equivalent for Access bound form


"Jeff Eckermann" <jeff_eckermann@yahoo.com> wrote in message news:20040805184412.78858.qmail@web20826.mail.yahoo.com...
> How much testing have you done?  I have never had a problem with this,
> but then, I don't have to deal with any huge datasets.
>
I don't have any large datasets; largest table at present only has about 10,000 records.

Had created 2 views against the largest recordset for testing, one with a WHERE clause, and 2 bound forms, each using
oneof the views as a recordsource. The form using the view without the WHERE clause had the conditions in the WHERE
clauseused as a filter. 

I didn't put in timing code, but the filtered form seemed a little slower.
Dropped and rebuilt the test views, forms, etc. and now don't see much difference.

Phillipe had noticed difference between Access 2000 and Access 2002/2003 mdb, but I had already been using 2002/2003,
aswill be distributing runtime executable to my employees. 

Can't explain the difference in performance from first testing, but will try using filters in upcoming production
versionunless real world performance suffers. 

> >
> > I change the SQL of pass-through queries dynamically at runtime to
> > use as record sources for reports. That wouldn't work for forms, as
> > not updatable.
> >
> > Is the best approach to use an updatable view as the record source,
> > then change the view definition at runtime as with a passthrough
> > query?
>
> Why would you need to do this?  If you want to show different data to
> different users, then you reference the user name (e.g. current_user
> or session_user) in your view definition.
>

Was just creating unique name for the temporary view, for which string containing current_user would certainly be
easier.All of my employees will have similar table permissions at the present time, as small office and similar tasks. 

David P. Lurie




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend