Append Query works in query view but not when run from VBA

#11

On Dec 11, 12:06*am, "David-W-Fenton" <dfas... (AT) dfenton (DOT) com> wrote:

Quote:

franc sutherland <franc.sutherl... (AT) googlemail (DOT) com> wrote innews:3a611a75-4370-4b42-87c6-2445ed9c577f (AT) k13g2000vbq (DOT) googlegroups.co
m:

On Dec 10, 3:39 am, "David-W-Fenton" <dfas... (AT) dfenton (DOT) com> wrote:
franc sutherland <franc.sutherl... (AT) googlemail (DOT) com> wrote
innews:b392734f-
4407-4b69-9bcc-788315253... (AT) c17g2000prm (DOT) googlegroups.co
m:

I changed that to variables (David
is that what you meant about defining the control references as
parameters?

No. This is what I mean by parameters:

PARAMETERS [Forms]![frmInventory]![InventoryID] Long;
SELECT tblInventory.*
FROM tblInventory
WHERE
tblInventory.InventoryID=[Forms]![frmInventory]![InventoryID];

This makes the query optimizable, because the data type of the
form control is defined in the parameter. It also causes certain
things like Nulls in the referenced control to be handled in a
more orderly fashion than when the control is not defined as a
parameter.

From A2002 on, it is essential that any control reference used in
a WHERE clause be defined as a parameter. Before that, it worked
OK without it, but something changed between A2000 and A2002 in
the way control references were handled that broke the old system
and from then on required the parameters in order to produce
reliable results.

Thanks for that. *That looks like a useful thing.
Can I ask what you meant by 'makes the query optimizable'?

For any saved QueryDef in Access, the database engine calculates a
query plan for executing the query in the most efficient manner
possible. When Jet/ACE doesn't know the data types of criteria, it
can't necessarily optimize the query in the most efficient manner
possible. With parameters, you remove that uncertainty, because the
parameter defines what data type the value in the WHERE clause will
be.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
contact via website only * *http://www.dfenton.com/DFA/

Thanks David.

#12

On Thu, 9 Dec 2010 09:18:54 -0800 (PST), franc sutherland
<franc.sutherland (AT) googlemail (DOT) com> wrote:

Quote:

build_sql = build_sql & "INSERT INTO TEMPtblReport1
( GroupID, GroupName, CompanyID, CompanyName, "
build_sql = build_sql & "ProductID, ProductName, Quantity,
ValueOfSale, Discount,[List Price], "

Note that I do the following with such a construct:

build_sql = build_sql & "INSERT INTO TEMPtblReport1
( GroupID, GroupName, CompanyID, CompanyName, " & _
"ProductID, ProductName, Quantity, ValueOfSale, Discount,[List Price], " & _
....

Makes it much more compact and easier to read.

Also note that as I never muck with the join lines I let those go
waaaaaaay over to the right hand side and just add the " & _
Although this might not apply to your query.

Tony

--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Append Query works in query view but not when run from VBA

comp.databases.ms-access comp.databases.ms-access

Re: Append Query works in query view but not when run from VBA - 12-11-2010 , 11:06 AM

Re: Append Query works in query view but not when run from VBA - 12-12-2010 , 06:39 PM