title: Building SQL strings in Access/VBA
body: <p>Occasionally, I have had to build a SQL string in VBA and execute it with <code>Docmd.RunSql()</code>. I have always built these strings by concatenating variables into the string, e.g:</p>
  
  <pre><code>Dim mysqlstring as String
  mysqlstring = "INSERT INTO MyTable (Field1, Field2, Field3 ...) VALUES ("
  mysqlstring = mysqlstring + Me.TextMyField1 + ", " 'parameter comments
  mysqlstring = mysqlstring + Me.TextMyField2 + ", " 
  mysqlstring = mysqlstring + Me.TextMyField3 + ", " 
  ...
  mysqlstring = mysqlstring + ");"
  Docmd.RunSql mysqlstring
  </code></pre>
  
  <p>VBA doesn't seem to have a unary concatenation operator (like +=) and while this doesn't look ideal, at least I can comment each of my parameters and change them independently. It makes it easier to read and to change than one monster concatenated string. But it still seems like a terrible way to build SQL strings. I have one with about 50 parameters at work, so 50 lines of <code>mysqlstring = mysqlstring +...</code>. Not cute.</p>
  
  <p>Incidentally, that rules out the use of line-continuations to format the string, as there is a <a href="http://www.dailydoseofexcel.com/archives/2005/04/18/line-continuation-limit/" rel="nofollow noreferrer">limit on the number of line-continuations you can use on a single string</a> (hint: less than 50). Also, VBA doesn't let you put a comment after the line-continuation, grr!</p>
  
  <p>Up until recently, I thought this was the only way to build these strings. But recently I have seen a different pattern, injecting the parameters in the string like <a href="https://stackoverflow.com/questions/1775066/data-type-mismatch-in-criteria-expression">this question (VB.NET</a>) that I posted an answer on, and wondered if there was an equivalent of <code>Parameters.AddWithValue()</code> for VBA, or if that would even be any better than the string concatenation approach. So I figured that this deserves its own question. Maybe there's something I'm missing here.</p>
  
  <p><strong>Can some of the Access experts please clarify what are the best practices for building SQL strings in Access/VBA.</strong> </p>