SQL Command Box for Microsoft Access

Among the many things I miss in Access from Foxpro is the ability to enter SQL statements on-the-fly into the command box. So, as a first cut, I created a form with a textbox and a button. The textbox holds the SQL code, and a button that calls a subroutine to stuff the code into a scratch query defined in the .MDB Queries collection.

There are lots of possible refinements, error checking, parsing of other commands, etc, but already I’m taken with this as it eliminates half the clicking around when doing SQL queries. Thanks to Martin Green’s Office tips for most of the code. Here is the VBA code for the command button.

Private Sub cmdExecute_Click()
' The following code processes an on-the-fly SQL command
' entered in the text box. The command requires a "scratch"
' query be included in the database query collection. The code
' takes the SQL string, passes it to the scratch query, and
' then executes it.
' Code cribbed from Martin Green's Office Tips at
' http://www.fontstuff.com

Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strSQL = Trim(Me.txtQuery.value)

'Here is the code which will apply the SQL statement to the query:

Set db = CurrentDb
Set qdf = db.QueryDefs("qryScratch")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryScratch"
End Sub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s