Executing Multiple SQL Strings

[Littlemalky]

Hi, I'm curious if there is a more efficient way to execute multiple SQL strings? I'm basically running a series of append queries and will be layering in a few more. Currently, this is my structure, but I'm sure there is a faster way to run this? Also, does each SQL string have to have a different name because it's kind of annoying to Dim every single one? Any advise?

Private Sub COMMISSION_PAYOUT_APPEND()

    Dim strSQL_REP_CFS_ESR_L1, strSQL_REP_CFS_ESR_L2, strSQL_REP_CFS_ESR_L3 As String
    
    
    'Append REPS & CFS L1_PAYOUT to tbl_COMMISSIONS_PAYOUTS
    strSQL_REP_CFS_ESR_L1 = "INSERT INTO tbl_COMMISSIONS_PAYOUTS_1 ( PAYOUT_DATE, REGION_ID, EMPLOYEE_ID, SALES_ROLE_ID, POSITION_ID, PRODUCT_ID, PAYOUT_CATEGORY, " & _
                            "NUMBER_OF_TERRITORIES, PAYOUT_AMT ) SELECT DateSerial(Year(Date()),Month(Date()),0) AS PAYOUT_DATE, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.REGION_ID, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.EMPLOYEE_ID, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.SALES_ROLE_ID, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[POSITION ID], " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.PRODUCT_ID, 'L1' AS PAYOUT_CATEGORY, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.NUM_OF_TERRITORIES, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[L1_PAYOUT] AS PAYOUT_AMT FROM qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD " & _
                            "INNER JOIN qry_ACTIVE_EMPLOYEES ON (qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.EMPLOYEE_ID = qry_ACTIVE_EMPLOYEES.EMPLOYEE_ID) " & _
                            "AND (qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[POSITION ID] = qry_ACTIVE_EMPLOYEES.POSITION_ID)"
    
    CurrentDb.Execute strSQL_REP_CFS_ESR_L1, dbSeeChanges
    CurrentDb.Close
    
    'Append REPS & CFS L2_PAYOUT to tbl_COMMISSIONS_PAYOUTS
    strSQL_REP_CFS_ESR_L2 = "INSERT INTO tbl_COMMISSIONS_PAYOUTS_1 ( PAYOUT_DATE, REGION_ID, EMPLOYEE_ID, SALES_ROLE_ID, POSITION_ID, PRODUCT_ID, PAYOUT_CATEGORY, " & _
                            "NUMBER_OF_TERRITORIES, PAYOUT_AMT ) SELECT DateSerial(Year(Date()),Month(Date()),0) AS PAYOUT_DATE, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.REGION_ID, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.EMPLOYEE_ID, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.SALES_ROLE_ID, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[POSITION ID], " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.PRODUCT_ID, 'L2' AS PAYOUT_CATEGORY, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.NUM_OF_TERRITORIES, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[L2_PAYOUT] AS PAYOUT_AMT FROM qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD " & _
                            "INNER JOIN qry_ACTIVE_EMPLOYEES ON (qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.EMPLOYEE_ID = qry_ACTIVE_EMPLOYEES.EMPLOYEE_ID) " & _
                            "AND (qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[POSITION ID] = qry_ACTIVE_EMPLOYEES.POSITION_ID)"
    
    CurrentDb.Execute strSQL_REP_CFS_ESR_L2, dbSeeChanges
    CurrentDb.Close
    
    'Append REPS & CFS L3_PAYOUT to tbl_COMMISSIONS_PAYOUTS
    strSQL_REP_CFS_ESR_L3 = "INSERT INTO tbl_COMMISSIONS_PAYOUTS_1 ( PAYOUT_DATE, REGION_ID, EMPLOYEE_ID, SALES_ROLE_ID, POSITION_ID, PRODUCT_ID, PAYOUT_CATEGORY, " & _
                            "NUMBER_OF_TERRITORIES, PAYOUT_AMT ) SELECT DateSerial(Year(Date()),Month(Date()),0) AS PAYOUT_DATE, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.REGION_ID, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.EMPLOYEE_ID, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.SALES_ROLE_ID, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[POSITION ID], " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.PRODUCT_ID, 'L3' AS PAYOUT_CATEGORY, qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.NUM_OF_TERRITORIES, " & _
                            "qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[L3_PAYOUT] AS PAYOUT_AMT FROM qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD " & _
                            "INNER JOIN qry_ACTIVE_EMPLOYEES ON (qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.EMPLOYEE_ID = qry_ACTIVE_EMPLOYEES.EMPLOYEE_ID) " & _
                            "AND (qry_COMMISSION_PAYOUT_REP_CFS_ESR_QTD.[POSITION ID] = qry_ACTIVE_EMPLOYEES.POSITION_ID)"


    CurrentDb.Execute strSQL_REP_CFS_ESR_L3, dbSeeChanges
    CurrentDb.Close
End Sub

 

[Norie]

Is the only difference in the queries L1_PAYOUT, L2_PAYOUT etc?

 

[Littlemalky]

Well, that and the criteria being a little different as well. That's only true for these particular queries. They won't all be that similar. I just posted a sampling.

 

[xenou]

you can dim your queries in an array:

dim s(2) as String
s(0) = "update tabl31 set ...."
s(1) = "update table2 set ..."
s(2) = "update table3 set ..."

for i = 0 to 2
    CurrentDb.Execute s(i)
Next i

Also you can of course just save the queries as queries, then you can run them without the sql strings needing to be in variables.

dim s(2) as String
s(0) = "Query1"
s(1) = "Query2"
s(2) = "Query3"

for i = 0 to 2
    DoCmd.SetWarnings False
    DoCmd.OpenQuery s(i)
    DoCmd.SetWarnings True
Next i

Note: Not sure why you are closing CurrentDb three times.

 

[Micron]

Or if they're similar except for the criteria part, you can Dim sqlPart and assign everything up to the space before WHERE. You Dim strWhere and assign the criteria part to this variable, and Dim sql for the concatenated version (or similar). In the code block for the 1st query, you db.Excecute (assuming you dim'd and set an object for CurrentDb) like so:

strWhere = "WHERE ...."
sql = sqlPart & strWhere
db.Execute sql, dbseechanges

In subsequent blocks, you reset sql and rebuild it
sql = ""
strWhere = "WHERE for next query..."
sql = sqlPart & strWhere
db.Execute sql, dbseechanges

rinse and repeat. There's no need to Dim a variable for every variation; just reassign it, but ensure you don't continue to build on it when you shouldn't. That's why I interjected sql = "". As always, if you do create object variables (like db for CurrentDb or recordsets, ensure you reclaim that memory space by destroying those objects). In the initial stages, it's a good idea to debug.print your constructed sql statement so you can check it for missing spaces, brackets, etc.

 

[Littlemalky]

Thank you for your help everyone! I'm going to try to apply this.