title: VBA-SQL UPDATE/INSERT/SELECT to/from Excel worksheet
body: <p><strong>In a nutshell:</strong> I'm making a scheduler for my client and, due to constraints, it needs to be in a single excel file (as small as possible).  So one worksheet works as the UI and any others will be tables or settings.</p>
  
  <p>I'm trying to use SQL (to which I'm new) to work with the schedule data on a single worksheet (named "TblEmpDays"). So I need to add/update and retrieve records to/from this worksheet.  I was able to get a <code>SELECT</code> query to work with some arbitrary data (and paste to a Range).  However, I'm not able to get <code>INSERT</code> or <code>UPDATE</code> to work.  I've seen it structured as <code>INSERT INTO [&lt;table name&gt;$] (&lt;field names&gt;) VALUES (&lt;data&gt;);</code>.  However this gives me a run-time error "<em>'-2147217900 (80040e14)' Syntax error in INSERT INTO statement.</em>"</p>
  
  <p>I'm using VBA to write all of this and I made an SQL helper class to make the query execution easier.</p>
  
  <p>To clarify, my question is: How do I need to construct the <code>INSERT</code> and <code>UPDATE</code> queries? What am I missing?  I'm trying to post as much related info as possible, so let me know if I missed anything.</p>
  
  <p><strong>Class SQL:</strong></p>
  
  <pre><code>Private pCn ' As Database
  Private pResult 'As Recordset
  Private pSqlStr As String
  
  Public Property Get Result()
      Result = pResult
  End Property
  
  Public Function Init()
      Set pCn = CreateObject("ADODB.Connection")
  
      With pCn
          .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
                              "Data Source=" &amp; ThisWorkbook.FullName &amp; ";" &amp; _
                              "Extended Properties=""Excel 12.0 Macro;HDR=YES;ReadOnly=False"";"
          .Open
      End With
  
  End Function
  
  Public Function Cleanup()
      If Not (pCn Is Nothing) Then
          pCn.Close
          Set pCn = Nothing
      End If
  
      If Not pResult Is Nothing Then
          Set pResult = Nothing
      End If
  
  End Function
  
  Public Function CopyResultToRange(rg As Range)
      If Not rg Is Nothing And Not pResult Is Nothing Then
          rg.CopyFromRecordset pResult
      End If
  End Function
  
  Public Property Get query() As String
      query = pSqlStr
  End Property
  Public Property Let query(value As String)
      pSqlStr = value
  End Property
  
  Public Function Execute(Optional sqlQuery As String)
      If sqlQuery = "" Then
          sqlQuery = query
      End If
  
      If Not pCn Is Nothing Then
          Set pResult = pCn.Execute(sqlQuery, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
      Else
          MsgBox "SQL connection not established"
      End If
  
  End Function
  </code></pre>
  
  <p><strong>Executing function:</strong></p>
  
  <pre><code>Dim s As SQL ' this is the SQL class '
  Dim tbl As String
  ' rcDay=date string, rcIn &amp; rcOut = time strings, rcVac=boolean string, rcSls=number string'
  Dim rcName As String, rcDay As String, rcIn As String, rcOut As String, rcVac As String, rcSls As String
  Dim qry As String
  
  tbl = "[TblEmpDays$]"
  qry = "INSERT INTO &lt;tbl&gt; (name, date, in, out, vac, sales)" &amp; vbNewLine &amp; _
          "VALUES ('&lt;name&gt;', '&lt;date&gt;', '&lt;in&gt;', '&lt;out&gt;', '&lt;vac&gt;', &lt;sales&gt;);"
  
  ' Set rc* vars '
  
  s.Init
  s.query = Replace(Replace(Replace(Replace(Replace(Replace(Replace(qry, _
                                      "&lt;tbl&gt;", tbl), _
                                      "&lt;sales&gt;", rcSls), _
                                      "&lt;vac&gt;", rcVac), _
                                      "&lt;out&gt;", rcOut), _
                                      "&lt;in&gt;", rcIn), _
                                      "&lt;date&gt;", rcDay), _
                                      "&lt;name&gt;", rcName)
  MsgBox s.query
  s.Execute
  s.Cleanup
  </code></pre>
  
  <p>I've looked all over an can't find a solution.  I'm sure I just haven't searched the right phrase or something simple.</p>