What type of query would be quickest?

[Baby Face Lee]

Hi guys,
I've got a form where the 'after update' event triggers an append query.
I've currently written it in SQL within the vb code of the form and use the
DoCmd.RunSQL instruction to execute it. Also, for simplicity, I've written
the query so that ALL records are attempted to be appended to the table.
However, I've set the primary key to avoid any duplicates so only the extra
record will actually be written to the table (all warning messages are
supressed).
Would it be quicker if:
a) I had a separate query in the design grid that my vb code instructed to
run (rather than using SQL within the code)
b) Referred to the values within various controls on the subform/main form
so that the query only attempted to append the extra record (rather than
pushing all records at the table for it to determine which is the new one).
I've moved away from doing it this way as my problem is that I'm working on a
subform, within another subform within a main form and as hard as I try I
can't seem to get the syntax right within the query design grid to refer to
the controls on the nested subform.
(Forms!frm_Main!frm_Subform1.Form!frm_Subform2.Form!Control and permutations
of this where I've included/excluded '.Form' wont work) It then occurred to
me that it might be quicker anyway for Access to handle the larger query
rather than search for values within controls.
The database will be a front/back end system used on an office network and
the max number of records that it will handle during the append procedure is
5000 (ish).
Any thoughts or suggestions to improve things?
Thanks for your advice,

Lee

 

[Jerry Whittle]

Hi,

Great question. Each time Access runs a query, it optimizes it first. If it
is a named query or one used as the record source for a form or report,
Access stores the execution plan for that query. Therefore a saved query may
be faster.

As a primary key is indexed, I think that I'd use a subquery to check which
records to append instead of the brute force method of letting Access handle
duplicate errors.

In my desk I keep a stopwatch. It is often the best way to answer such
questions.

 

[Baby Face Lee]

Hi Jerry,
Thanks very much for your reply Jerry.
I think I'll do as you suggest and create a named query as it sounds like it
would be better than using vb code.
With regard to using a stop-watch, the trouble is I've got a really fast PC
(I work from home) and it executes it almost instantaneously so I can't
really tell what's quickest but I know that things will be rather different
when I load the database on an office server!
Much appreciated.

Lee