Easily copy an SQL statement from a query to VBA

        9 votes: ****      7,092 views      1 comments
by Allen Browne, 23 March 2007    (All versions of Access)

Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Created: March 2007.  Last Updated: November 2008.


Copy SQL statement from query to VBA

Screenshot of SQL to VBA form

Rather than typing complex query statements into VBA code, developers often mock up a query graphically, switch it to SQL View, copy, and paste into VBA.

If you've done it, you know how messy it is sorting out the quotes, and the line endings.

Solution: create a form where you paste the SQL statement, and get Access to create the SQL string for you.

Creating the form

The form just needs two text boxes, and a command button. SQL statements can be quite long, so you put the text boxes on different pages of a tab control.

  1. Create a new form (in design view.)
  2. Add a tab control.
  3. In the first page of the tab control, add a unbound text box.
    Set its Name property to txtSql.
    Increase its Height and Width so you can see many long lines at once.
  4. In the second page of the tab control, add another unbound text box.
    Name it txtVBA, and increase its height and width.
  5. Above the tab control, add a command button.
    Name it cmdSql2Vba.
    Set its On Click property to [Event Procedure].
    Click the Build button (...) beside this property.
    When Access opens the code window, set up the code like this:
Private Sub cmdSql2Vba_Click()
    Dim strSql As String
    'Purpose:   Convert a SQL statement into a string to paste into VBA code.
    Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"
    
    If IsNull(Me.txtSQL) Then
        Beep
    Else
        strSql = Me.txtSQL
        strSql = Replace(strSql, """", """""")  'Double up any quotes.
        strSql = Replace(strSql, vbCrLf, strcLineEnd)
        strSql = "strSql = """ & strSql & """"
        Me.txtVBA = strSql
        Me.txtVBA.SetFocus
        RunCommand acCmdCopy
    End If
End Sub

Using the form

To use the form:

  1. Open your query in SQL View, and copy the SQL statement to clipboard (Ctrl+C.)
  2. Paste into the first text box (Ctrl+V.)
  3. Click the button.
  4. Paste into a new line in your VBA procedure (Ctrl+V.)

Hint: If you want extra line breaks in your VBA code, press Enter to create those line breaks in the SQL View of the query or in your form.

Access 97 and earlier

These versions lacked the Replace() function. To use the code with those versions, copy the custom Replace() function from this utility.


HomeIndex of tipsTop

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Easily copy an SQL statement from a query to VBA'?


1.

Ed Ebbs says...

19 August 2008

 
Thanks!
So Cool :-)

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.