title: ado in excel, inserting records into access database
body: <p>First time asker, usually I can find the answer by searching, but my google-fu seems weak today.</p>
  
  <p>I have an excel workbook connecting to an access 2003 database to insert usage records</p>
  
  <p>The code i'm using is:</p>
  
  <pre><code>sdbpath = ThisWorkbook.Path &amp; "\Data.mdb"
  sCommand = "INSERT INTO Usage VALUES('" &amp; Environ("Username") &amp; "',#" &amp; Now() &amp; "#)"
  
  Dim dbCon As New ADODB.Connection
  Dim dbCommand As New ADODB.Command
  
  dbCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; sdbpath &amp; "; Jet OLEDB:Database Password=TestPass;"
  dbCommand.ActiveConnection = dbCon
  
  dbCommand.CommandText = sCommand
  dbCommand.Execute
  
  dbCon.Close
  </code></pre>
  
  <p>The code fails on the line dbCommand.Execute with run-time error '-2147217900 (80040e14)' : Automation error.</p>
  
  <p>The database its trying to insert the record into contains one table, usage, with two columns - UserID and AccessDate, formatted as text and DateTime respectively. </p>
  
  <p>The odd part is the connection string seems OK, since it fails after the connection is already open, yet if I take the sCommand value before the execute is run, then paste it into a query in access and execute that - it runs fine!</p>
  
  <p>In case it was access struggling with the datetime format i've tried switching it to text (and the hashtags in the code) but that still fails. I've also tried specifying the column names too.</p>
  
  <p>Can anyone shed some light on what i'm doing wrong? I've never had so much trouble with a very simple bit of SQL.</p>
  
  <p>Thanks in advance!</p>