|
Quick Tip Details |
Question:
Using VBA, how can I modify the SQL statement of an existing Query? |
Answer:
I find using DAO works best for changing the SQL of a query object (ADO is a bit slower). The Microsoft DAO Object Library needs to be added as a reference via the VBA Editor. |
Code:Function SQLEdit_MEI(QryName As String, SQL As String) As Boolean
'------------------------------------------------------------------------------------------------------------
'Comments: Changes the SQL in an Existing Query
'Parameters: QryName-Name of Query; SQL-New SQL Statement
'Returns: True if sucessusful; False if the function failed
'------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Trap
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
SQLEdit_MEI = True
Set db = CurrentDb()
Set qdf = db.QueryDefs(QryName)
qdf.SQL = SQL
Set db = Nothing
Err_Trap_Exit:
Exit Function
Err_Trap:
SQLEdit_MEI = False
Resume Err_Trap_Exit
End Function |
|
