Manipulate QueryDefs From VBA
QueryDefs are stored queries that reside in Access. These QueryDefs can be opened and modified using VBA. Sometimes it is useful to modify an original stored QueryDef by adding additional filters or modifying the SQL String.
For a technical explanation of QueryDefs, See QueryDefs
The code below shows how to modify an original stored query by adding a dynamic WHERE clause, then execute the modified version to append records to a table.Program Code
Private Sub cmdExportToExcel_Click()
' ****************************************************************
' Sample Of Using QueryDefs To Modify A Stored Query
' ****************************************************************
Dim cdlg As New CommonDialogAPI
Dim db As DAO.Database
' ****************************************************************
' Define Original Stored QueryDef Created With Query Builder
' ****************************************************************
Dim qd As DAO.QueryDef
' ****************************************************************
' Define New Temporary QueryDef Created By Adding a WHERE Clause
' ****************************************************************
Dim qdNew As DAO.QueryDef
' ****************************************************************
' Define Variables Used To Construct New Modified SQL Statement
' ****************************************************************
Dim strSQL As String
Dim intLenSQL As Integer
' ****************************************************************
' Delete The Contents of the Target Table For an Append Query
' ****************************************************************
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteCombinedExplosionsReportFormatPic1", acViewNormal, acEdit
DoCmd.SetWarnings True
' ****************************************************************
' Bring In The Original QueryDef (Instantiate It)
' ****************************************************************
Set db = CurrentDb()
Set qd = db.QueryDefs("qryListInventoryAvailablePicsCreate")
' ****************************************************************
' Extract The SQL from the Original QueryDef
' ****************************************************************
strSQL = qd.SQL
' ****************************************************************
' Remove the Ending ";" and Space Characters from Original SQL
' ****************************************************************
intLenSQL = Len(strSQL) - 3
strSQL = Left(strSQL, intLenSQL)
' ****************************************************************
' Depending Upon The Type of SQL Modification Required Append
' a New WHERE Clause At The End Of The Original Query
' (1) Append WHERE BodyCode IN ("Value1", "Value2", "Value3);
' (2) Append WHERE BodyCode = "Value";
' ****************************************************************
If strSelectedBodyCode = "" Or IsNull(strSelectedBodyCode) _
Or strSelectedBodyCode = "ALL" Then
If strMultipleSelectFilter <> "" Then
strSQL = strSQL & " WHERE tblCombinedExplosionsReportFormatPic.BodyCode " & _
strMultipleSelectFilter & ";"
End If
Else
strSQL = strSQL & " WHERE tblCombinedExplosionsReportFormatPic.BodyCode = " & """" & strSelectedBodyCode & """" & ";"
End If
' ****************************************************************
' Close The Original Query without any Modifications
' ****************************************************************
qd.Close
Set qd = Nothing
' ****************************************************************
' Create A New Temporary QueryDef with the Modified SQL string
' The "" Creates a Temporary QueryDef That Is Not Saved
' ****************************************************************
Set qdNew = db.CreateQueryDef("", strSQL)
' ****************************************************************
' Execute the Modified QueryDef And Append Data To The Table
' ****************************************************************
qdNew.Execute dbFailOnError
' ****************************************************************
' Close The Temporary QueryDef Without Saving It
' ****************************************************************
qdNew.Close
Set qdNew = Nothing
End Sub
Program Code For a SELECT (Not Action) Query
Option Compare Database
Option Explicit
Public Sub FCSTTestSelectQuery()
' ********************************************************************************
' This Code Will Create a new SELECT Query or Update an Existing Select Query
' The db.Execute command can not be used on SELECT queries
' ********************************************************************************
Dim strLogicalCompareOperator As String
Dim strSQLForSelect As String
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Set db = CurrentDb()
strLogicalCompareOperator = "<=""B""))"
strSQLForSelect = "SELECT tblFCSTByStyleColorSizeDetail.SalesRep, tblFCSTByStyleColorSizeDetail.Account, tblFCSTByStyleColorSizeDetail.BodyCode, " & _
"tblFCSTByStyleColorSizeDetail.Style, tblFCSTByStyleColorSizeDetail.Color, tblFCSTByStyleColorSizeDetail.SizeDescription, " & _
"tblFCSTByStyleColorSizeDetail.ForecastYearMonth, Sum(tblFCSTByStyleColorSizeDetail.Quantity) AS ActualQty, " & _
"Sum(tblFCSTByStyleColorSizeDetail.Quantity) AS ForecastQty, First(tblFCSTByStyleColorSizeDetail.WholesalePrice) AS Wholesale, " & _
"Sum(tblFCSTByStyleColorSizeDetail.BookedUnits) AS BookedUnits, tblFCSTByStyleColorSizeDetail.ReportingYearMonth " & _
"FROM tblFCSTByStyleColorSizeDetail " & _
"GROUP BY tblFCSTByStyleColorSizeDetail.SalesRep, tblFCSTByStyleColorSizeDetail.Account, tblFCSTByStyleColorSizeDetail.BodyCode, " & _
"tblFCSTByStyleColorSizeDetail.Style, tblFCSTByStyleColorSizeDetail.Color, tblFCSTByStyleColorSizeDetail.SizeDescription, " & _
"tblFCSTByStyleColorSizeDetail.ForecastYearMonth, tblFCSTByStyleColorSizeDetail.ReportingYearMonth, " & _
"tblFCSTByStyleColorSizeDetail.SizeNumber, tblFCSTByStyleColorSizeDetail.ForecastYearMonth " & _
"HAVING (((tblFCSTByStyleColorSizeDetail.SalesRep)" & _
strLogicalCompareOperator & _
" ORDER BY tblFCSTByStyleColorSizeDetail.SalesRep, tblFCSTByStyleColorSizeDetail.Account, " & _
"tblFCSTByStyleColorSizeDetail.BodyCode, tblFCSTByStyleColorSizeDetail.Style, tblFCSTByStyleColorSizeDetail.Color, " & _
"tblFCSTByStyleColorSizeDetail.SizeNumber, tblFCSTByStyleColorSizeDetail.ForecastYearMonth;"
GoTo UpdateExistingQuery
' ********************************************************************************
' Create A New SELECT Query That Didn't Exist Before
' ********************************************************************************
Set qDef = db.CreateQueryDef("qryFCSTSelectiveExport", strSQLForSelect)
qDef.Close
Set qDef = Nothing
Set db = Nothing
DoCmd.OpenQuery "qryFCSTSelectiveExport", acViewNormal
' ********************************************************************************
' Update An Existing Query Already Viewable from the List of Access Objects
' ********************************************************************************
UpdateExistingQuery:
Set qDef = db.QueryDefs("qryFCSTSelectiveExport")
qDef.SQL = strSQLForSelect
qDef.Close
Set qDef = Nothing
Set db = Nothing
DoCmd.OpenQuery "qryFCSTSelectiveExport", acViewNormal
End Sub