Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Question Unanswered: 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.

  2. #2
    Join Date
    Jan 2003

    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

  3. #3
    Join Date
    Dec 2002

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts