If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Changing Query Defs in VBA
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. |
#3
|
|||
|
|||
Changing Query Defs in VBA
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. |
#4
|
|||
|
|||
Changing Query Defs in VBA
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/ |
#5
|
|||
|
|||
Changing Query Defs in VBA
"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 |
#6
|
|||
|
|||
Changing Query Defs in VBA
"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/ |
#7
|
|||
|
|||
Changing Query Defs in VBA
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 |
#8
|
|||
|
|||
Changing Query Defs in VBA
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. |
#9
|
|||
|
|||
Changing Query Defs in VBA
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. |
#10
|
|||
|
|||
Changing Query Defs in VBA
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 |
Thread Tools | |
Display Modes | |
|
|