sql = "insert into history (historyid, type, accountid, accountname, "
    sql = sql & "contactid, contactname, startdate, userid, timeless, duration, "
    sql = sql & "description, notes, longnotes, "
    sql = sql & "createuser, createdate, completeduser, completeddate) "
    sql = sql & "values ('"
    sql = sql & m_historyID & "', "
    sql = sql & "'" & type & "', "
    sql = sql & "'" & GetContactValue(conId, "accountid") & "', "
    sql = sql & "'" & GetContactValue(conId, "account") & "', "
    sql = sql & "'" & conId & "', "
    sql = sql & "'" & GetContactValue(conId, "fullname") & "', "
    sql = sql & "'" & startDT & "', "
    sql = sql & "'" & userID & "', "
    sql = sql & "'T', "
    sql = sql & "'0', "
    sql = sql & "'" & Replace(desc, "'", "''") & "', "
    sql = sql & "'" & Replace(Mid(notes, 1, 254), "'", "''") & "', "
    sql = sql & "'" & Replace(notes, "'", "''") & "', "
    sql = sql & "'" & Application.BasicFunctions.CurrentUserID & "', "
    sql = sql & "'" & Now & "', "
    sql = sql & "'" & userID & "', "
    sql = sql & "'" & completedDT & "')"

    Set cn = Application.GetNewConnection
    cn.Execute sql

1. You must build logic to determine if you need to insert or update a record. The statements then require separate formatting for each kind of statement.
2. The SQL statement is passed as a string with limitations in the provider at handling items like dates being in ISO date format.
3. It is hard to read/modify a long SQL statement if you must add or remove certain fields at a later time.

1. To begin with we need to create a recordset object
   
   

   Set rs = CreateObject("ADODB.Recordset")

   
   
2. Now we work with the record set object, setting its properties accordingly.
   
   

   With rs
       Set .ActiveConnection = Application.GetNewConnection
       .CursorLocation = adUseClient
       .CursorType = adOpenStatic
       .LockType = adLockOptimistic

   
   
3. Finally we populate the recordset with a SQL statement
   
   

       .Open "select * from c_test where c_testid = '" & labelID.Caption & "'"

   
   In this example we are building a statement based upon the value in a label on a form.
   
   
4. Next we test to see if the recordsets, recordcount property is 0, if it is we know that this record does not exist
   
   

       If .RecordCount = 0 Then

   
   
5. Again, if the recordcount is 0 then we must add the record. We add the following code in to handle this.
   
   

           .AddNew
           .Fields("c_testid").Value = Application.BasicFunctions.GetIDFor("c_test")
           .Fields("accountid").Value = CurrentID
           .Fields("createuser").Value = Application.BasicFunctions.CurrentUserID
           .Fields("createdate").Value = Now
       End If

   
   Notice that with this code, since the values are separate parameters, instead of an inline SQL statement we do not need to worry about formatting our dates into ISO format as the provider will do this for us.
   
   
6. Now that we have added the fields into the code that are required for every new record we can now go to the records that get updated regardless of if the record already exists.
   
   

       .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
       .Fields("modifydate").Value = Now
       .Fields("field1").Value = edit1.Text
       .Fields("field2").Value = edit2.Text
   

   
   
7. Once we have listed all of the fields we want populated we can now update our recordset
   
   

       .Update

   
   
8. After the recordset has been updated we wrap up the code with the clean up of closing the recordset, and destroying it
   
   

       .Close
   End With
   Set rs = Nothing

   
   

Set rs = CreateObject("ADODB.Recordset")
With rs
         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open "select * from c_test where c_testid = '" & labelID.Caption & "'"

         If .RecordCount = 0 Then
            .AddNew
            .Fields("c_testid").Value = Application.BasicFunctions.GetIDFor("c_test")
            .Fields("accountid").Value = CurrentID
            .Fields("createuser").Value = Application.BasicFunctions.CurrentUserID
            .Fields("createdate").Value = Now
         End If

         .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
         .Fields("modifydate").Value = Now
         .Fields("field1").Value = edit1.Text
         .Fields("field2").Value = edit2.Text
         .Update

         .Close
End With
Set rs = Nothing