Changing Query Defs in VBA

I've written some code that changes the sql of several queries. The first
set runs fine, but after that, I get an error that says "Query Input must
contain at least one table or query". I set up the code in the second query
exactly like I did in the first. I double and triple checked that I spelled
everything correctly. I'm not sure what's wrong. Please help:

Private Sub cmdCalcAdjustment_click()

' This function runs all queries necessary to update tables from which the
' Warehouse Scorecard Report is generated. The order the queries will run
in is
' as follows:
'
' 1. qryAdjustmentsMonthly
' 2. qryWareHousePOTotals
' 3. qryFrieght
' 4. qryFreightUpdate
' 5. qryShipment
' 6. qryLabor
' 7. qryLaborUpdate
' 8. qryFillKillAppend
' 9. qryCalcUnfill

Dim sMonth As String
Dim sSQL As String
Dim sPOSQL As String
Dim sFrieghtSQL As String
Dim sShipmentSQL As String
Dim sLaborSQL As String
Dim sFillKillSQL As String
Dim datMonthBegin As Date
Dim datMonthEnd As Date

DoCmd.SetWarnings (0)

' 1. Adjusts SQL of qryAdjustmentsMonthly to choose the appropriate fields
for tabulation
' and executes the query.

datMonthBegin = InputBox("Enter 1st day of the period in mm/dd/yyyy format.")
datMonthEnd = InputBox("Enter last day of the period in mm/dd/yyyy format.")
sMonth = Month(datMonthBegin)
sSQL = "INSERT INTO tblAdjustments ( Period, FISCAL_YEAR, ADJ_IN_QTY,
ADJ_OUT_QTY, ADJ_ABS_QTY, ADJ_TOTAL_QTY, ADJ_IN_COST, ADJ_OUT_COST,
ADJ_ABS_COST, ADJ_TOTAL_COST, MonthBegin) " & _
"SELECT " & sMonth & " AS Period, LAWSON_GLAMOUNTS.FISCAL_YEAR,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth & "]) AS ADJ_IN_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_OUT_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]+[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_ABS_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]-[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_TOTAL_QTY,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "] AS ADJ_IN_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_OUT_COST,
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_ABS_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_"
& sMonth & "] AS ADJ_TOTAL_COST, " & datMonthBegin & " as MonthBegin " & _
"FROM LAWSON_ICHISTORY INNER JOIN LAWSON_GLAMOUNTS ON
(LAWSON_ICHISTORY.COMPANY = LAWSON_GLAMOUNTS.COMPANY) AND
(LAWSON_ICHISTORY.R_YEAR = LAWSON_GLAMOUNTS.FISCAL_YEAR) " & _
"WHERE (((LAWSON_ICHISTORY.COMPANY) = 1) And
((LAWSON_ICHISTORY.LOCATION) = ""CHOA"") And ((LAWSON_GLAMOUNTS.ACCT_UNIT) =
""20342"") And ((LAWSON_GLAMOUNTS.ACCOUNT) = 63120)) " & _
"GROUP BY LAWSON_GLAMOUNTS.FISCAL_YEAR,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_"
& sMonth & "] " & _
"HAVING (((LAWSON_GLAMOUNTS.FISCAL_YEAR)=2006));"



CurrentDb.QueryDefs("qryAdjustmentsMonthly").SQL = sSQL
DoCmd.OpenQuery "qryAdjustmentsMonthly"

' 2.

sPOSQL = "INSERT INTO tblPOTotals ( LOCATION, PO_CODE, CountOfPO_NUMBER,
SumOfNBR_LINES, SumOfPO_PRINT_TOTAL, MonthBegin, MonthEnd ) " & _
"SELECT LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE,
Count(LAWSON_PURCHORDER.PO_NUMBER) AS CountOfPO_NUMBER,
Sum(LAWSON_PURCHORDER.NBR_LINES) AS SumOfNBR_LINES,
Sum(LAWSON_PURCHORDER.PO_PRINT_TOTAL) AS SumOfPO_PRINT_TOTAL, CDate(" &
datMonthBegin & ") AS MonthBegin, CDate(" & datMonthEnd & ") AS " &
datMonthBegin & " " & _
"FROM LAWSON_PURCHORDER " & _
"WHERE (((LAWSON_PURCHORDER.PO_DATE) Between " & datMonthBegin & "
And " & datMonthEnd & ")) " & _
"GROUP BY LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE " & _
"HAVING (((LAWSON_PURCHORDER.LOCATION)=""CHOA"") AND
((LAWSON_PURCHORDER.PO_CODE)=""W""));"

CurrentDb.QueryDefs("qryWareHousePOTotals").SQL = sPOSQL
DoCmd.OpenQuery "qryWareHousePOTotals"

Any help anyone could give would be VERRY much appriciated.

Hi Matt,

after you replace the SQL for a query...

CurrentDb.QueryDefs.Refresh

after each action query executes, if subsequent queries will need to use
the data in the table(s) you just changed...

currentdb.tabledefs.refresh

you may need to follow each Refresh with

DoEvents

don't put DoEvents in if you don't need to unless you want to be able to
BREAK the code...
~~~

DoEvents is used to make VBA pay attention to what is currently
happening and look to see if the OS (Operating System) has any requests.

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general
procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

~~~

unless you WANT to be prompted to run the action query, change (for
instance)

DoCmd.OpenQuery "qryAdjustmentsMonthly"

to

currentdb.execute "qryAdjustmentsMonthly"
currentdb.tabledefs.refresh
DoEvents 'if necessary




Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Matt Lockamy wrote:
I've written some code that changes the sql of several queries. The first
set runs fine, but after that, I get an error that says "Query Input must
contain at least one table or query". I set up the code in the second query
exactly like I did in the first. I double and triple checked that I spelled
everything correctly. I'm not sure what's wrong. Please help:

Private Sub cmdCalcAdjustment_click()

' This function runs all queries necessary to update tables from which the
' Warehouse Scorecard Report is generated. The order the queries will run
in is
' as follows:
'
' 1. qryAdjustmentsMonthly
' 2. qryWareHousePOTotals
' 3. qryFrieght
' 4. qryFreightUpdate
' 5. qryShipment
' 6. qryLabor
' 7. qryLaborUpdate
' 8. qryFillKillAppend
' 9. qryCalcUnfill

Dim sMonth As String
Dim sSQL As String
Dim sPOSQL As String
Dim sFrieghtSQL As String
Dim sShipmentSQL As String
Dim sLaborSQL As String
Dim sFillKillSQL As String
Dim datMonthBegin As Date
Dim datMonthEnd As Date

DoCmd.SetWarnings (0)

' 1. Adjusts SQL of qryAdjustmentsMonthly to choose the appropriate fields
for tabulation
' and executes the query.

datMonthBegin = InputBox("Enter 1st day of the period in mm/dd/yyyy format.")
datMonthEnd = InputBox("Enter last day of the period in mm/dd/yyyy format.")
sMonth = Month(datMonthBegin)
sSQL = "INSERT INTO tblAdjustments ( Period, FISCAL_YEAR, ADJ_IN_QTY,
ADJ_OUT_QTY, ADJ_ABS_QTY, ADJ_TOTAL_QTY, ADJ_IN_COST, ADJ_OUT_COST,
ADJ_ABS_COST, ADJ_TOTAL_COST, MonthBegin) " & _
"SELECT " & sMonth & " AS Period, LAWSON_GLAMOUNTS.FISCAL_YEAR,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth & "]) AS ADJ_IN_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_OUT_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]+[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_ABS_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]-[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_TOTAL_QTY,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "] AS ADJ_IN_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_OUT_COST,
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_ABS_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_"
& sMonth & "] AS ADJ_TOTAL_COST, " & datMonthBegin & " as MonthBegin " & _
"FROM LAWSON_ICHISTORY INNER JOIN LAWSON_GLAMOUNTS ON
(LAWSON_ICHISTORY.COMPANY = LAWSON_GLAMOUNTS.COMPANY) AND
(LAWSON_ICHISTORY.R_YEAR = LAWSON_GLAMOUNTS.FISCAL_YEAR) " & _
"WHERE (((LAWSON_ICHISTORY.COMPANY) = 1) And
((LAWSON_ICHISTORY.LOCATION) = ""CHOA"") And ((LAWSON_GLAMOUNTS.ACCT_UNIT) =
""20342"") And ((LAWSON_GLAMOUNTS.ACCOUNT) = 63120)) " & _
"GROUP BY LAWSON_GLAMOUNTS.FISCAL_YEAR,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_"
& sMonth & "] " & _
"HAVING (((LAWSON_GLAMOUNTS.FISCAL_YEAR)=2006));"



CurrentDb.QueryDefs("qryAdjustmentsMonthly").SQL = sSQL
DoCmd.OpenQuery "qryAdjustmentsMonthly"

' 2.

sPOSQL = "INSERT INTO tblPOTotals ( LOCATION, PO_CODE, CountOfPO_NUMBER,
SumOfNBR_LINES, SumOfPO_PRINT_TOTAL, MonthBegin, MonthEnd ) " & _
"SELECT LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE,
Count(LAWSON_PURCHORDER.PO_NUMBER) AS CountOfPO_NUMBER,
Sum(LAWSON_PURCHORDER.NBR_LINES) AS SumOfNBR_LINES,
Sum(LAWSON_PURCHORDER.PO_PRINT_TOTAL) AS SumOfPO_PRINT_TOTAL, CDate(" &
datMonthBegin & ") AS MonthBegin, CDate(" & datMonthEnd & ") AS " &
datMonthBegin & " " & _
"FROM LAWSON_PURCHORDER " & _
"WHERE (((LAWSON_PURCHORDER.PO_DATE) Between " & datMonthBegin & "
And " & datMonthEnd & ")) " & _
"GROUP BY LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE " & _
"HAVING (((LAWSON_PURCHORDER.LOCATION)=""CHOA"") AND
((LAWSON_PURCHORDER.PO_CODE)=""W""));"

CurrentDb.QueryDefs("qryWareHousePOTotals").SQL = sPOSQL
DoCmd.OpenQuery "qryWareHousePOTotals"

Any help anyone could give would be VERRY much appriciated.

In addition to what Crystal told you, when you use dates in SQL, you must
delimit them with # characters (and they must be in mm/dd/yyyy format*)

Change

"WHERE (((LAWSON_PURCHORDER.PO_DATE) Between " & datMonthBegin & "
And " & datMonthEnd & ")) " & _

to

"WHERE (((LAWSON_PURCHORDER.PO_DATE) Between " &
Format(datMonthBegin, "\#mm\/dd\/yyyy\#") & "
And " & Format(datMonthEnd, "\#mm\/dd\/yyyy\#") & ")) " & _


* okay, they don't HAVE to be in mm/dd/yyy format: they can be in any
unambiguous format, such as dd mmm yyyy or yyyy-mm-dd. The point is, they
can't be in dd/mm/yyyy format, because Access will ALWAYS interpret dates in
mm/dd/yyyy format if it can. That means that 12/01/2007 will ALWAYS be
interpretted as December 1st. It will, however, correctly interpret
13/01/2007

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Matt Lockamy" wrote in message
...
I've written some code that changes the sql of several queries. The first
set runs fine, but after that, I get an error that says "Query Input must
contain at least one table or query". I set up the code in the second
query
exactly like I did in the first. I double and triple checked that I
spelled
everything correctly. I'm not sure what's wrong. Please help:

Private Sub cmdCalcAdjustment_click()

' This function runs all queries necessary to update tables from which the
' Warehouse Scorecard Report is generated. The order the queries will run
in is
' as follows:
'
' 1. qryAdjustmentsMonthly
' 2. qryWareHousePOTotals
' 3. qryFrieght
' 4. qryFreightUpdate
' 5. qryShipment
' 6. qryLabor
' 7. qryLaborUpdate
' 8. qryFillKillAppend
' 9. qryCalcUnfill

Dim sMonth As String
Dim sSQL As String
Dim sPOSQL As String
Dim sFrieghtSQL As String
Dim sShipmentSQL As String
Dim sLaborSQL As String
Dim sFillKillSQL As String
Dim datMonthBegin As Date
Dim datMonthEnd As Date

DoCmd.SetWarnings (0)

' 1. Adjusts SQL of qryAdjustmentsMonthly to choose the appropriate fields
for tabulation
' and executes the query.

datMonthBegin = InputBox("Enter 1st day of the period in mm/dd/yyyy
format.")
datMonthEnd = InputBox("Enter last day of the period in mm/dd/yyyy
format.")
sMonth = Month(datMonthBegin)
sSQL = "INSERT INTO tblAdjustments ( Period, FISCAL_YEAR, ADJ_IN_QTY,
ADJ_OUT_QTY, ADJ_ABS_QTY, ADJ_TOTAL_QTY, ADJ_IN_COST, ADJ_OUT_COST,
ADJ_ABS_COST, ADJ_TOTAL_COST, MonthBegin) " & _
"SELECT " & sMonth & " AS Period, LAWSON_GLAMOUNTS.FISCAL_YEAR,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth & "]) AS ADJ_IN_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_OUT_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]+[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_ABS_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]-[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_TOTAL_QTY,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "] AS ADJ_IN_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_OUT_COST,
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_ABS_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth &
"]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_"
& sMonth & "] AS ADJ_TOTAL_COST, " & datMonthBegin & " as MonthBegin " & _
"FROM LAWSON_ICHISTORY INNER JOIN LAWSON_GLAMOUNTS ON
(LAWSON_ICHISTORY.COMPANY = LAWSON_GLAMOUNTS.COMPANY) AND
(LAWSON_ICHISTORY.R_YEAR = LAWSON_GLAMOUNTS.FISCAL_YEAR) " & _
"WHERE (((LAWSON_ICHISTORY.COMPANY) = 1) And
((LAWSON_ICHISTORY.LOCATION) = ""CHOA"") And ((LAWSON_GLAMOUNTS.ACCT_UNIT)
=
""20342"") And ((LAWSON_GLAMOUNTS.ACCOUNT) = 63120)) " & _
"GROUP BY LAWSON_GLAMOUNTS.FISCAL_YEAR,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth &
"]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_"
& sMonth & "] " & _
"HAVING (((LAWSON_GLAMOUNTS.FISCAL_YEAR)=2006));"



CurrentDb.QueryDefs("qryAdjustmentsMonthly").SQL = sSQL
DoCmd.OpenQuery "qryAdjustmentsMonthly"

' 2.

sPOSQL = "INSERT INTO tblPOTotals ( LOCATION, PO_CODE, CountOfPO_NUMBER,
SumOfNBR_LINES, SumOfPO_PRINT_TOTAL, MonthBegin, MonthEnd ) " & _
"SELECT LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE,
Count(LAWSON_PURCHORDER.PO_NUMBER) AS CountOfPO_NUMBER,
Sum(LAWSON_PURCHORDER.NBR_LINES) AS SumOfNBR_LINES,
Sum(LAWSON_PURCHORDER.PO_PRINT_TOTAL) AS SumOfPO_PRINT_TOTAL, CDate(" &
datMonthBegin & ") AS MonthBegin, CDate(" & datMonthEnd & ") AS " &
datMonthBegin & " " & _
"FROM LAWSON_PURCHORDER " & _
"WHERE (((LAWSON_PURCHORDER.PO_DATE) Between " & datMonthBegin & "
And " & datMonthEnd & ")) " & _
"GROUP BY LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE " &
_
"HAVING (((LAWSON_PURCHORDER.LOCATION)=""CHOA"") AND
((LAWSON_PURCHORDER.PO_CODE)=""W""));"

CurrentDb.QueryDefs("qryWareHousePOTotals").SQL = sPOSQL
DoCmd.OpenQuery "qryWareHousePOTotals"

Any help anyone could give would be VERRY much appriciated.

Matt Lockamy wrote in
:

I've written some code that changes the sql of several queries.

Why?

It's an honest question.

There is only one context in which I've ever written to a QueryDef
in order to change it, and that was for a graph that was presenting
TOP N results, where N was choosable by the user. Since I couldn't
find any way to alter the graph's SQL properties, and because
there's no way to use a variable for N in TOP N queries, I had to
use a saved query and change its SQL.

I have never encountered any other cases where altering a saved
QueryDef was required.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote in message
. 1...
Matt Lockamy wrote in
:

I've written some code that changes the sql of several queries.

Why?

It's an honest question.

There is only one context in which I've ever written to a QueryDef
in order to change it, and that was for a graph that was presenting
TOP N results, where N was choosable by the user. Since I couldn't
find any way to alter the graph's SQL properties, and because
there's no way to use a variable for N in TOP N queries, I had to
use a saved query and change its SQL.

I have never encountered any other cases where altering a saved
QueryDef was required.

I change the SQL of passthrough queries all the time, but I agree that changing
the SQL of a standard query is almost never required.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

"Rick Brandt" wrote in
et:

"David W. Fenton" wrote in message
. 1...
Matt Lockamy wrote in
:

I've written some code that changes the sql of several queries.

Why?

It's an honest question.

There is only one context in which I've ever written to a
QueryDef in order to change it, and that was for a graph that was
presenting TOP N results, where N was choosable by the user.
Since I couldn't find any way to alter the graph's SQL
properties, and because there's no way to use a variable for N in
TOP N queries, I had to use a saved query and change its SQL.

I have never encountered any other cases where altering a saved
QueryDef was required.

I change the SQL of passthrough queries all the time, but I agree
that changing the SQL of a standard query is almost never
required.

It seems to me that most people who are wanting to edit QueryDefs
are not using SQL constructed on-the-fly and assigned at runtime, or
do not know about the WHERE clause arguments of forms and reports.

Some experienced Access developers make the performance issue for
saved QueryDefs, but I think that's vastly overblown. It would
really only matter for massive amounts of data or for really
complicated joins, seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I have never encountered any other cases where
altering a saved QueryDef was required.

Please note this is not my practice (because it is extremely rare that I'd
find it advisable to use the same query for multiple purposes) and I don't
recommend it, so please don't ask me to "defend the practice". I'm just
pointing out why someone might do so.

When I add criteria (WHERE clause) for a Query in code, I create the entire
SQL statement and use it in the RecordSource of a Form or Query or in an
OpenRecordset directly. But, it also works if you set the SQL property of a
saved Query to the constructed SQL. Some might do so, if they used the same
Query with that selection criteria in more than one object.

Larry Linson
Microsoft Access MVP

Saved Query vs SQL performance
---

Hi David,

With all due respect ... you cannot generalize like this.

Changing the querydef that a report is based on, vs replacing the SQL,
depending on what it does, can increase performance dramatically.

Don't get me wrong, I do not like a database window cluttered up with
hundreds of queries (I don't even like having a full column of them) --
I construct SQL on the fly and rewrite RowSources all over the place,
filter reports with the Where parameter of the OpenReport action -- do
everything I can to avoid saving a query...

.... but when you start talking RecordSource, throw in variable grouping
fields along with alot of records ... there is not usually a question --
performance is much better if the SQL for the query is replaced just
before the report is rendered than if the RecordSource for the report is
assigned the SQL -- even though the SQL was just written in both cases!
Why? I do not know.

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



David W. Fenton wrote:

It seems to me that most people who are wanting to edit QueryDefs
are not using SQL constructed on-the-fly and assigned at runtime, or
do not know about the WHERE clause arguments of forms and reports.

Some experienced Access developers make the performance issue for
saved QueryDefs, but I think that's vastly overblown. It would
really only matter for massive amounts of data or for really
complicated joins, seems to me.

Hi, Crystal.

after you replace the SQL for a query...

CurrentDb.QueryDefs.Refresh

A collection should be refreshed after an object is added or deleted from
that collection (i.e., a change in the collection), not when one of the
properties of an object in that collection is changed.

after each action query executes, if subsequent queries will need to use
the data in the table(s) you just changed...

currentdb.tabledefs.refresh

Not unless one deleted a table, so that the succeeding code doesn't assume
the table is gone when it may still be in the TableDefs collection, or one
creates a table, to guarantee that the TableDefs collection includes the new
table. A Make-Table query (i.e., "SELECT * INTO tblArchiveData FROM
tblData;") is the only action query that creates a table. The rest of the
action queries (delete, update, and append) only change records, not the
TableDefs collection, so refreshing this collection in those cases does
nothing except slow performance.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"strive4peace" wrote in message
...
Hi Matt,

after you replace the SQL for a query...

CurrentDb.QueryDefs.Refresh

after each action query executes, if subsequent queries will need to use
the data in the table(s) you just changed...

currentdb.tabledefs.refresh

you may need to follow each Refresh with

DoEvents

don't put DoEvents in if you don't need to unless you want to be able to
BREAK the code...
~~~

DoEvents is used to make VBA pay attention to what is currently happening
and look to see if the OS (Operating System) has any requests.

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general procedure
or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

~~~

unless you WANT to be prompted to run the action query, change (for
instance)

DoCmd.OpenQuery "qryAdjustmentsMonthly"

to

currentdb.execute "qryAdjustmentsMonthly"
currentdb.tabledefs.refresh
DoEvents 'if necessary




Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Matt Lockamy wrote:
I've written some code that changes the sql of several queries. The
first set runs fine, but after that, I get an error that says "Query
Input must contain at least one table or query". I set up the code in
the second query exactly like I did in the first. I double and triple
checked that I spelled everything correctly. I'm not sure what's wrong.
Please help:

Private Sub cmdCalcAdjustment_click()

' This function runs all queries necessary to update tables from which
the
' Warehouse Scorecard Report is generated. The order the queries will
run in is
' as follows:
'
' 1. qryAdjustmentsMonthly
' 2. qryWareHousePOTotals
' 3. qryFrieght
' 4. qryFreightUpdate
' 5. qryShipment
' 6. qryLabor
' 7. qryLaborUpdate
' 8. qryFillKillAppend
' 9. qryCalcUnfill

Dim sMonth As String
Dim sSQL As String
Dim sPOSQL As String
Dim sFrieghtSQL As String
Dim sShipmentSQL As String
Dim sLaborSQL As String
Dim sFillKillSQL As String
Dim datMonthBegin As Date
Dim datMonthEnd As Date

DoCmd.SetWarnings (0)

' 1. Adjusts SQL of qryAdjustmentsMonthly to choose the appropriate
fields for tabulation
' and executes the query.

datMonthBegin = InputBox("Enter 1st day of the period in mm/dd/yyyy
format.")
datMonthEnd = InputBox("Enter last day of the period in mm/dd/yyyy
format.")
sMonth = Month(datMonthBegin)
sSQL = "INSERT INTO tblAdjustments ( Period, FISCAL_YEAR, ADJ_IN_QTY,
ADJ_OUT_QTY, ADJ_ABS_QTY, ADJ_TOTAL_QTY, ADJ_IN_COST, ADJ_OUT_COST,
ADJ_ABS_COST, ADJ_TOTAL_COST, MonthBegin) " & _
"SELECT " & sMonth & " AS Period, LAWSON_GLAMOUNTS.FISCAL_YEAR,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth & "]) AS ADJ_IN_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_OUT_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]+[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_ABS_QTY,
Sum([LAWSON_ICHISTORY]![ADJ_IN_QTY_" & sMonth &
"]-[LAWSON_ICHISTORY]![ADJ_OUT_QTY_" & sMonth & "]) AS ADJ_TOTAL_QTY,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "] AS ADJ_IN_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_OUT_COST,
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "] AS ADJ_ABS_COST,
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth &
"]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "] AS ADJ_TOTAL_COST, " &
datMonthBegin & " as MonthBegin " & _
"FROM LAWSON_ICHISTORY INNER JOIN LAWSON_GLAMOUNTS ON
(LAWSON_ICHISTORY.COMPANY = LAWSON_GLAMOUNTS.COMPANY) AND
(LAWSON_ICHISTORY.R_YEAR = LAWSON_GLAMOUNTS.FISCAL_YEAR) " & _
"WHERE (((LAWSON_ICHISTORY.COMPANY) = 1) And
((LAWSON_ICHISTORY.LOCATION) = ""CHOA"") And
((LAWSON_GLAMOUNTS.ACCT_UNIT) = ""20342"") And
((LAWSON_GLAMOUNTS.ACCOUNT) = 63120)) " & _
"GROUP BY LAWSON_GLAMOUNTS.FISCAL_YEAR,
[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
([LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth &
"]*-1)+[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth & "],
[LAWSON_GLAMOUNTS]![DB_AMOUNT_" & sMonth &
"]+[LAWSON_GLAMOUNTS]![CR_AMOUNT_" & sMonth & "] " & _
"HAVING (((LAWSON_GLAMOUNTS.FISCAL_YEAR)=2006));"

CurrentDb.QueryDefs("qryAdjustmentsMonthly").SQL = sSQL
DoCmd.OpenQuery "qryAdjustmentsMonthly"

' 2.

sPOSQL = "INSERT INTO tblPOTotals ( LOCATION, PO_CODE, CountOfPO_NUMBER,
SumOfNBR_LINES, SumOfPO_PRINT_TOTAL, MonthBegin, MonthEnd ) " & _
"SELECT LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE,
Count(LAWSON_PURCHORDER.PO_NUMBER) AS CountOfPO_NUMBER,
Sum(LAWSON_PURCHORDER.NBR_LINES) AS SumOfNBR_LINES,
Sum(LAWSON_PURCHORDER.PO_PRINT_TOTAL) AS SumOfPO_PRINT_TOTAL, CDate(" &
datMonthBegin & ") AS MonthBegin, CDate(" & datMonthEnd & ") AS " &
datMonthBegin & " " & _
"FROM LAWSON_PURCHORDER " & _
"WHERE (((LAWSON_PURCHORDER.PO_DATE) Between " & datMonthBegin &
" And " & datMonthEnd & ")) " & _
"GROUP BY LAWSON_PURCHORDER.LOCATION, LAWSON_PURCHORDER.PO_CODE "
& _
"HAVING (((LAWSON_PURCHORDER.LOCATION)=""CHOA"") AND
((LAWSON_PURCHORDER.PO_CODE)=""W""));"

CurrentDb.QueryDefs("qryWareHousePOTotals").SQL = sPOSQL
DoCmd.OpenQuery "qryWareHousePOTotals"

Any help anyone could give would be VERRY much appriciated.

on using currentdb.tabledefs.refresh --
"Not unless one deleted a table ..."

in my experience, I have found that to not be true ... for instance, if
a series of action queries are run and each depends on the previous
one's results, using the refresh solves the problem of not seeing
changes just made. This could be something that is 'fixed' in later
versions of Access, but I am a creature of habit so now I always do it
.... or maybe all I needed was DoEvents, which I sometimes put after ...
hmmm... something to try, thanks Gunny.


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
strive4peace2006 at yahoo.com
*



'69 Camaro wrote:
Hi, Crystal.

after you replace the SQL for a query...

CurrentDb.QueryDefs.Refresh

A collection should be refreshed after an object is added or deleted from
that collection (i.e., a change in the collection), not when one of the
properties of an object in that collection is changed.

after each action query executes, if subsequent queries will need to use
the data in the table(s) you just changed...

currentdb.tabledefs.refresh

Not unless one deleted a table, so that the succeeding code doesn't assume
the table is gone when it may still be in the TableDefs collection, or one
creates a table, to guarantee that the TableDefs collection includes the new
table. A Make-Table query (i.e., "SELECT * INTO tblArchiveData FROM
tblData;") is the only action query that creates a table. The rest of the
action queries (delete, update, and append) only change records, not the
TableDefs collection, so refreshing this collection in those cases does
nothing except slow performance.

HTH.
Gunny