title: insert date and time into acces with SQL visual basic
body: <p>I am trying to use SQL in visual basic to insert Date and Time value into Access 2007 running in Access2002/3 mode. Using my code I have managed to insert text strings and numerical values into the table. However for the DateTime field get a syntax error. The field in Access is set as a Date/Time field type.</p>
  
  <p>Below is my first function which generates the queries for the Database Accessing function:</p>
  
  <pre><code>Public Function NewUpload(ByVal UploadType As String) As Single
  
      Dim UploadNumber As Single
      Dim ColumnString As String
      Dim ValueString As String
  
      If DatabaseConnection("SELECT ID_UPL FROM tabUpload", "Read Recordset") = "Error" Then GoTo close
      Do Until rdrOLEDB.Read = False
          If Val(rdrOLEDB.Item(0).ToString()) &gt; UploadNumber Then UploadNumber = Val(rdrOLEDB.Item(0).ToString())
      Loop
      rdrOLEDB.Close()
      cnnOLEDB.Close()
  
      UploadNumber = UploadNumber + 1
  
      'Update Uploads table:
      ColumnString = "ID_UPL,DateTime,IDUser,DataCalc"
      ValueString = Format(UploadNumber, "0000") &amp; ",#" &amp; Now.ToLongDateString &amp; " " &amp; Now.ToLongTimeString &amp; "#,'" &amp; My.User.Name &amp; "','" &amp; UploadType &amp; "'"
      If DatabaseConnection("INSERT INTO tabUpload(" &amp; ColumnString &amp; ") VALUES(" &amp; ValueString &amp; ")", "Non-Query") = "Error" Then GoTo Close
  
      NewUpload = UploadNumber
  </code></pre>
  
  <p>Close:
          cnnOLEDB.Close()</p>
  
  <pre><code>End Function
  </code></pre>
  
  <p>Here is the second Function which connects to the Database</p>
  
  <pre><code>Public Function DatabaseConnection(ByVal Query As String, ByVal Task As String) As String
      'On Error GoTo Err
  
      cnnOLEDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; DataDirectoryName &amp; DatabaseFileName
      cmdOLEDB.Connection = cnnOLEDB
      cmdOLEDB.CommandText = Query
      cnnOLEDB.Open()
  
      Select Case Task
          Case "Read Recordset"
              rdrOLEDB = cmdOLEDB.ExecuteReader()
              DatabaseConnection = "Read Recordset"
          Case "Read Scalar"
              DatabaseConnection = Str(cmdOLEDB.ExecuteScalar)
          Case "Non-Query"
              DatabaseConnection = Str(cmdOLEDB.ExecuteNonQuery())
              DatabaseConnection = "Non-Query"
      End Select
  
      Exit Function
  Err:
      MsgBox("Database connection error.")
      DatabaseConnection = "Error"
  
  End Function
  </code></pre>
  
  <p>When I run this code I get the Query:
  <code>INSERT INTO tabUpload(IDUPL, DateTime, User, DataCalc) VALUES(0003, #17 August 2012 14:23:27#, 'UK\Pej', 'Calc')"</code></p>
  
  <p>I have also tried several variations of DateTime using format and now(Year) giving yyyy-mm-dd etc.</p>
  
  <p>Any help would be greatly appreciated.</p>