How do Access Queries Work with ODBC "jd" <jd[ at ]msn.com> 12/2/2008 8:10:19 PM I have a question on how Access queries work. I have a .mdb and all tables are linked to a SQL backend. When I create a query and add criteria, does Access bring all the data from that table to the client and then applies the criteria or are the criteria applied on the server and only the data requested by the query sent to the client?

Re: How do Access Queries Work with ODBC Rick Brandt <rickbrandt2[ at ]hotmail.com> 12/3/2008 12:26:40 AM On Tue, 02 Dec 2008 13:10:19 -0700, jd wrote: [Quoted Text] > I have a question on how Access queries work. I have a .mdb and all > tables are linked to a SQL backend. When I create a query and add > criteria, does Access bring all the data from that table to the client > and then applies the criteria or are the criteria applied on the server > and only the data requested > by the query sent to the client? It depends. It is possible to create a query that drags all the data over and then processes the data locally, but that is actually pretty rare. Access will try to pass a statement to the server that lets it do as much work as possible, but if for example the WHERE clause used a VBA function then that filtering cannot be passed to the server. The idea that Access always brings over all the data is an old wives tale that the Access community has never been able to kill off. It is true that if you want to be absolutely sure that the server is doing 100% of the work and then returning the results you have to use pass-through queries, stored procedures, or links to Views. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com

Re: How do Access Queries Work with ODBC "jd" <jd[ at ]msn.com> 12/4/2008 7:52:04 PM Thank you "Rick Brandt" <rickbrandt2[ at ]hotmail.com> wrote in message news:4nkZk.9122$be.4065[ at ]nlpi061.nbdc.sbc.com... [Quoted Text] > On Tue, 02 Dec 2008 13:10:19 -0700, jd wrote: > >> I have a question on how Access queries work. I have a .mdb and all >> tables are linked to a SQL backend. When I create a query and add >> criteria, does Access bring all the data from that table to the client >> and then applies the criteria or are the criteria applied on the server >> and only the data requested >> by the query sent to the client? > > It depends. It is possible to create a query that drags all the data > over and then processes the data locally, but that is actually pretty > rare. Access will try to pass a statement to the server that lets it do > as much work as possible, but if for example the WHERE clause used a VBA > function then that filtering cannot be passed to the server. > > The idea that Access always brings over all the data is an old wives tale > that the Access community has never been able to kill off. It is true > that if you want to be absolutely sure that the server is doing 100% of > the work and then returning the results you have to use pass-through > queries, stored procedures, or links to Views. > > > -- > Rick Brandt, Microsoft Access MVP > Email (as appropriate) to... > RBrandt at Hunter dot com