What would be the quickest/best way to handle query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,
(sorry, somehow managed to post this to the Table Design forum by mistake)
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
 
Hi,

Embedded answers.

Baby Face Lee said:
Hi guys,
(sorry, somehow managed to post this to the Table Design forum by mistake)
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)
Click to expand...


You are in position to test the real case, but in general, the difference
will be minimal in favor for the saved query, but if the SQL code is just
too generic, that solution could be also much much slower, due to the
absence of possible optimization that would have been done on a more
specific statement.

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).
Click to expand...


You can win something if the data has to travel through the wire. If you
bring useless data through the wire, that is poor use of the bandwidth. On
the other hand, if the SQL statement didn't changed, a requery should be a
prefered technique. Be sure that the SQL statement bring only the required
records, as much as possible, with appropriate WHERE clause and the like.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top