DEVelopers HUT

'---------------------------------------------------------------------------------------
' Procedure : RedefQry
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Redefine a query's SQL using VBA
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sQryName  : Name of the query to redefine the SQL of
' sSQL      : New SQL statement to use to define the query
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ? RedefQry("qry_ClientList","SELECT * FROM Clients ORDER BY ClientName")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Nov-07                 Initial Release
'---------------------------------------------------------------------------------------
Sub RedefQry(sQryName As String, sSQL As String)
On Error GoTo Error_Handler
    Dim qdf         As DAO.QueryDef
 
    Set qdf = CurrentDb.QueryDefs(sQryName)
    qdf.SQL = sSQL  'Redefine the Query's SQL

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: RedefQry" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub