If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Queries vs Filters

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Dec 2002
Posts: 7
Question Queries vs Filters

I am currently in process of upsizing to SQL backend for an access DB , but in the mean time I 'm trying to make the Access frontend as compatible as possible.

When navigating the DB I have a 'Find' form to find records on various forms and at present open them with a filter to the specific record in the OpenForm method. All of the secondary ('Detail') forms have query recordsources. Would it be more efficient (and make upsizing easier) to have the SQL recordsource for the forms set on the fly when movng from the Find form? I have tried without success to find out what is best practice.
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Jan 2003
Location: Vietnam
Posts: 188
form.recordsouce is much better

when we filter a form for a specific set of records, ms access has to load the whole recordset to memory, go thru every single record and show us the subset (filtered records). This will not be a performance problem if the database & client are on the same machine.

when database & client are on 2 separate machines and linked by network, filtering a form requires the whole recordset transfered over the network from database server to client machine while using SQL statement, the query is run by database server and only the subset of records is traveling along the wire to client machine.

in short, in client/ server environment (the one you'd like to upsize) the recommendation is SQL statements and not filtering

hope this helps
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Dec 2002
Posts: 7
Talking Access best practice

Thankyou for that - finally - explained in a way that makes it 100% clear. So I need to get rid of the recordset of the form altogether and set it in code when opening the form.

Thanks again
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On