Changing Query Defs in VBA

Matt Lockamy · Jan 27, 2007

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.

strive4peace · Jan 27, 2007

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 said:
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.

Douglas J. Steele · Jan 27, 2007

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

(no private e-mails, please)

Matt Lockamy said:
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.

David W. Fenton · Jan 27, 2007

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.

Rick Brandt · Jan 27, 2007

David W. Fenton said:
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.

David W. Fenton · Jan 28, 2007

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.

Larry Linson · Jan 28, 2007

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

strive4peace · Jan 28, 2007

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
*

'69 Camaro · Jan 28, 2007

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/expert_contributors2.html for contact
info.

strive4peace said:
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 said:

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.

Click to expand...

strive4peace · Jan 29, 2007

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
*

Rick Brandt · Jan 29, 2007

strive4peace said:
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.

I would not expect refreshing the tabledefs collection to have anything at all
to do with *data* changes. Only should matter if I have changed, added, or
deleted table structures.

'69 Camaro · Jan 29, 2007

Hi, Crystal.

I think you're right that DoEvents afterwards will allow the operation to
complete before the next query executes. That or a flush cache operation so
that everything is written to disk before the next operation. The extra
time it takes to refresh the collection may be all the delay that was needed
for the record changes from the first action query to be completed,
coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well for me.

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/expert_contributors2.html for contact
info.

strive4peace · Jan 29, 2007

Hi gunny,

Thank you -- well, I am definitely going to stop doing the unnecessary
steps

Warm Regards,
Crystal
*

have an awesome day

*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

'69 Camaro · Jan 29, 2007

Hi, Crystal.

A single DoEvents after a record-intensive action query works well for me.

I should add that this is only when another action query or other operation
follows that requires the data from the first action query to be written to
disk before it executes, and only if it's a long-running action query (the
first action query, that is). So far it's been uncommon that I've needed
DoEvents, but it's happened, so I know what error messages one receives when
those expected record changes haven't been made in time for the next
operation. It's usually when I get those error messages that I realize I
should have put the DoEvents between the operations.

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/expert_contributors2.html for contact
info.

strive4peace · Jan 29, 2007

Thanks, Rick -- that was what I initially thought too, but putting the
statement in there took care of problems -- and Gunny explained why ...

to summarize:

TableDefs.Refresh is needed when a table is added or deleted, or the
structure is modified.

Warm Regards,
Crystal
*

have an awesome day

*
MVP Access
strive4peace2006 at yahoo.com
*

strive4peace · Jan 29, 2007

thanks, Gunny

Warm Regards,
Crystal
*

have an awesome day

*
MVP Access
strive4peace2006 at yahoo.com
*

David W. Fenton · Jan 29, 2007

... 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.

I have seen no such performance differences in reports. The thing
that most speeds of report performance, in my experience, is
removing tasks performed in the report formatting process. This
includes *not* having "Page [Page] in [Pages]" expressions, which
cannot be renderd on page 1 until the last page of the report has
been formatted. The data retrieval and grouping stages of report
rendering take up much less time than that.

So, we can agree to disagree. I don't see any real-world performance
improvement using saved QueryDefs as reporting recordsources,
except, as I already said, for extremely large data sets and with
very complicated join structures.

David W. Fenton · Jan 29, 2007

I think you're right that DoEvents afterwards will allow the
operation to complete before the next query executes. That or a
flush cache operation so that everything is written to disk before
the next operation. The extra time it takes to refresh the
collection may be all the delay that was needed for the record
changes from the first action query to be completed,
coincidentally curing the incomplete action query.

A single DoEvents after a record-intensive action query works well
for me.

I don't understand what you're saying here. DoEvents doesn't have
anything to do with the internal Jet operations -- all it does is
release control to the OS, which can allow screen painting and other
operations to happen asynchronously (when they'd usually have to
wait).

Two action queries executed one after the other *must* in *all*
circumstances have synchronously, i.e., the 2nd can't begin until
the 1st has finished. Anything else would be completely undesirable.

'69 Camaro · Jan 29, 2007

You're welcome, Crystal. Any time.

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/expert_contributors2.html for contact
info.

'69 Camaro · Jan 29, 2007

Hi, David.

I don't understand what you're saying here. DoEvents doesn't have
anything to do with the internal Jet operations -- all it does is
release control to the OS, which can allow screen painting and other
operations to happen asynchronously (when they'd usually have to
wait).

.. . . And the completion of the write to disk of the data changes before the
next query begins. And yes, it's possible that this is a coincidence that
there's just enough time delay during the DoEvents operations to appear to
cure the problem, too. ;-)

Two action queries executed one after the other *must* in *all*
circumstances have synchronously, i.e., the 2nd can't begin until
the 1st has finished. Anything else would be completely undesirable.

I've encountered occasions (rarely, mind you) where the last chunk of record
changes hadn't been written to disk yet and the next query errored because
it couldn't find the data that I was expecting to be there already. That's
the problem with a desktop database engine as opposed to a client/server
database engine. The client/server does these operations in memory and has
a snapshot of the data that includes the results of the previous data
manipulations and uses that snapshot for subsequent operations which read
from the snapshot, not from the disk, while the database engine writes to
disk the earlier transactions. (I'm not sure I'm explaining this very
clearly. Sorry.) The desktop database engine doesn't have a snapshot to
read from and always reads what's already been written to disk. The lack of
ability to do these "will be done" operations in memory is one of the
reasons one sees lightning speed in client/server database engines and Jet
seems to take its sweet time.

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/expert_contributors2.html for contact
info.

Changing Query Defs in VBA

Matt Lockamy

strive4peace

Douglas J. Steele

David W. Fenton

Rick Brandt

David W. Fenton

Larry Linson

strive4peace

'69 Camaro

strive4peace

Rick Brandt

'69 Camaro

strive4peace

'69 Camaro

strive4peace

strive4peace

David W. Fenton

David W. Fenton

'69 Camaro

'69 Camaro