Modify select Query with VBA
- Thread starter rhesus
- Start date
Assuming there is a query Query1:-
SELECT *
FROM TableName
WHERE ID>=2;
the following code uses a DAO QueryDef object to change the criteria to ID=2.
-----------------------------
Private Sub Command0_Click()
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Set db = CurrentDb
Set qDef = db.QueryDefs("Query1")
SQL = qDef.SQL
' view SQL statement before edit.
MsgBox SQL
' use functions to manipulate the SQL statement.
SQL = Left(SQL, InStr(SQL, ">") - 1) & Mid(SQL, InStr(SQL, ">") + 1)
' view SQL statement after edit.
MsgBox SQL
' save the edited SQL statement.
qDef.SQL = SQL
End Sub
------------------------------
Note
Once the query is saved, running the code again will result in an error.
The code was written in Access 97, so DAO was used. If you write the code in Access 2000, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select Microsoft DAO 3.6 Object Library from the Available References list.)
SELECT *
FROM TableName
WHERE ID>=2;
the following code uses a DAO QueryDef object to change the criteria to ID=2.
-----------------------------
Private Sub Command0_Click()
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Set db = CurrentDb
Set qDef = db.QueryDefs("Query1")
SQL = qDef.SQL
' view SQL statement before edit.
MsgBox SQL
' use functions to manipulate the SQL statement.
SQL = Left(SQL, InStr(SQL, ">") - 1) & Mid(SQL, InStr(SQL, ">") + 1)
' view SQL statement after edit.
MsgBox SQL
' save the edited SQL statement.
qDef.SQL = SQL
End Sub
------------------------------
Note
Once the query is saved, running the code again will result in an error.
The code was written in Access 97, so DAO was used. If you write the code in Access 2000, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select Microsoft DAO 3.6 Object Library from the Available References list.)
