locked
OK to use Query Builder to create SQL statements for Access VBA ? RRS feed

  • Question

  • I have fought with Access over SQL syntax that I write from scratch for use in Access VBA code...

    Throw in the dot or bang issues... and... well... 

    Then it occured to me that I could use the Access Query Builder GUI to quickly model the query...

    Then click View | SQL View and just copy the SQL statement Access created for you...

    Then paste that into your VBA code.

    I really don't like doing this but in the heat of the battle it sure seems like an easy way to just get it done.

    Are there any issues I should worry about with this approach?

     

     


    Thursday, July 14, 2011 1:27 PM

Answers

  • No. Especially when you need to handle complex queries in VBA than it is a good start. The most important thing: Use table alias names for all your tables to get a shorter SQL statement. Also you need to keep in mind that the query editor uses a lot of unnecessary parentheses to cope with all kinds of queries. Especially in WHERE conditions. The other thing you need to remember: You cannot use pivot queries as sub-queries. And you cannot model non-equi JOIN conditions between tables. And last but not least, use a convention in your code for working with SQL statements. Mine is quite simple: All statements are declared as CONST at the beginnging of each Sub/Function or the head of the module. Variables are parsed into it, e.g.

    Const SQL_UPDATE_CUSTOMER As String = _
     "UPDATE [Customer] " & _
     "SET [Address] = @1@ " & _
     "WHERE [Id] = @2@ ;"
    
    MsgBox SqlReplace(SQL_UPDATE_CUSTOMER, SqlQuote("New Address"), 1)

    Using my two coolest helper functions ;)

    Option Compare Database
    Option Explicit
    
    Public Function SqlReplace(AString As String, ParamArray AValues() As Variant) As String
    
    Dim Count As Long Dim Result As String
    Result = AString For Count = 0 To UBound(AValues()) Result = Replace(Result, "@" & (Count + 1) & "@", Nz(AValues(Count), "NULL")) Next Count SqlReplace = Result End Function Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String
    SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter End Function Public Function SqlQuoteNull(AString As Variant, Optional ADelimiter As String = "'") As String
    If IsNull(AString) Then SqlQuoteNull = "NULL" Else SqlQuoteNull = SqlQuote(CStr(AString), ADelimiter) End If End Function

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Proposed as answer by Macy Dong Tuesday, July 19, 2011 9:12 AM
    • Marked as answer by Macy Dong Thursday, July 21, 2011 2:11 AM
    Thursday, July 14, 2011 1:43 PM
  • I like this site of Fontstuff, which do a good job explaining putting VBA and SQL together:

    http://www.fontstuff.com/access/acctut15.htm

     

    Then I use often this handy Utility of Allen Browne, to create SQL to VBA SQL, see this thread:

    Copy SQL statement from query to VBA

     

    In this thread an explanation about Dot vs Bang:

    http://blogs.office.com/b/microsoft-access/archive/2008/05/30/dot-or-bang.aspx

     

    I hope this give you some heads up.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by Macy Dong Tuesday, July 19, 2011 9:12 AM
    • Marked as answer by Macy Dong Thursday, July 21, 2011 2:11 AM
    Thursday, July 14, 2011 1:46 PM

All replies

  • No. Especially when you need to handle complex queries in VBA than it is a good start. The most important thing: Use table alias names for all your tables to get a shorter SQL statement. Also you need to keep in mind that the query editor uses a lot of unnecessary parentheses to cope with all kinds of queries. Especially in WHERE conditions. The other thing you need to remember: You cannot use pivot queries as sub-queries. And you cannot model non-equi JOIN conditions between tables. And last but not least, use a convention in your code for working with SQL statements. Mine is quite simple: All statements are declared as CONST at the beginnging of each Sub/Function or the head of the module. Variables are parsed into it, e.g.

    Const SQL_UPDATE_CUSTOMER As String = _
     "UPDATE [Customer] " & _
     "SET [Address] = @1@ " & _
     "WHERE [Id] = @2@ ;"
    
    MsgBox SqlReplace(SQL_UPDATE_CUSTOMER, SqlQuote("New Address"), 1)

    Using my two coolest helper functions ;)

    Option Compare Database
    Option Explicit
    
    Public Function SqlReplace(AString As String, ParamArray AValues() As Variant) As String
    
    Dim Count As Long Dim Result As String
    Result = AString For Count = 0 To UBound(AValues()) Result = Replace(Result, "@" & (Count + 1) & "@", Nz(AValues(Count), "NULL")) Next Count SqlReplace = Result End Function Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String
    SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter End Function Public Function SqlQuoteNull(AString As Variant, Optional ADelimiter As String = "'") As String
    If IsNull(AString) Then SqlQuoteNull = "NULL" Else SqlQuoteNull = SqlQuote(CStr(AString), ADelimiter) End If End Function

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Proposed as answer by Macy Dong Tuesday, July 19, 2011 9:12 AM
    • Marked as answer by Macy Dong Thursday, July 21, 2011 2:11 AM
    Thursday, July 14, 2011 1:43 PM
  • I like this site of Fontstuff, which do a good job explaining putting VBA and SQL together:

    http://www.fontstuff.com/access/acctut15.htm

     

    Then I use often this handy Utility of Allen Browne, to create SQL to VBA SQL, see this thread:

    Copy SQL statement from query to VBA

     

    In this thread an explanation about Dot vs Bang:

    http://blogs.office.com/b/microsoft-access/archive/2008/05/30/dot-or-bang.aspx

     

    I hope this give you some heads up.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by Macy Dong Tuesday, July 19, 2011 9:12 AM
    • Marked as answer by Macy Dong Thursday, July 21, 2011 2:11 AM
    Thursday, July 14, 2011 1:46 PM