Hi Young,
My problem occurs when I attempt the update after changing a datetime field
that was previously contained a null value. (Everything works if the previous
value is not null...)
However I've managed a work around....
The work around involves using objconn.Excecute with an SQL UPDATE string....
This works well for most cases however I do not know how to correctly deal
with " characters occurin in the middl of long text values (e.g. in the
middle of a memo field) so I ended up using both methods. When the first
method throws an error then the error processing uses the second method to
handle the exception before returning control back.
Effectively...
Dim and set the ADODB connection object objconn
Dim and set an ADODB recordset rst
On err goto Err_Handler
Set up the SQL update string
For each record that has changed...
For each fld that needs updating...
objconn.Execute strSQL
Continue:
Next fld
Next record
Exit:
Close and nothing everything
Exit Sub
Err_Handler:
If err is SQL syntax error then
rst(fldname).Value = new value
rst.Update
Resume at Continue
Else
Report error
Resume Exit
End if
End Sub
I've attached a code sample below....Please note the the SQL method seemed to
work well so I used that as the main method and only use the ADO update
method when an SQL syntax error is thrown (due to those " characters...).
However no reason it would not work the other way around...
A couple of points to note:
1. In this case the connection object is public and is created/set elsewhere.
2. The software deals one at a time with each field in each record that needs
to be updated. This makes it easy to switch to the other method although it's
probably not the best for performance.
Hope all this helps your situation....
Giles
Function UpdateContact(strAction As String, rsSugar As ADODB.Recordset,
objContact As Object, strID As String)
On Error GoTo Err_UpdateContact
Dim db As DAO.Database
Dim rsFields As DAO.Recordset ' rsFields contains the names of the
fields for synching
Dim qdf As DAO.QueryDef
Dim i As Boolean
Dim strTextS, strItemS As String
Dim strTextO, strItemO As String
Dim lngIndexO As Long
Dim old As String
Dim strSQLUpdate As String
Dim objItems As Outlook.ItemProperties
Dim objItem As Outlook.ItemProperty
Set db = CurrentDb
Set qdf = db.QueryDefs("qryFieldsForContacts")
Set rsFields = qdf.OpenRecordset
UpdateContact = False ' Assume it did not update...
Select Case strAction
Case "UpdateSugar"
rsFields.MoveFirst
Do Until rsFields.EOF
strItemS = rsFields!SugarFieldName
If strItemS <> "name" Then
strItemO = rsFields!OutlookFieldIndex
lngIndexO = Val(strItemO)
Set objItems = objContact.ItemProperties
Set objItem = objItems.Item(lngIndexO)
strTextO = objItem.Value
If strTextO <> "" And Not IsNull(strTextO) Then
If rsSugar(strItemS) <> strTextO Then
old = """" ' sets up the SQL text delimiter
character...
strSQLUpdate = "UPDATE contacts SET contacts." &
strItemS
strSQLUpdate = strSQLUpdate & " = " & old &
strTextO & old & " WHERE (("
strSQLUpdate = strSQLUpdate & "Contacts.id)=" &
old & strID & old & ")"
pubconnCRM.Execute strSQLUpdate, ,
adExecuteNoRecords
Next_UpdateContact:
End If
End If
End If
rsFields.MoveNext
Loop
UpdateContact = True
End Select
Exit_UpdateContact:
qdf.Close
rsFields.Close
db.Close
Set qdf = Nothing
Set rsFields = Nothing
Set db = Nothing
Exit Function
Err_UpdateContact:
If Err.Number = -2147217900 Then
' This is an SQL syntax error, most likely caused by a " character in
a field
' Try to correct using the ADO update method...
rsSugar(strItemS) = strTextO
rsSugar.Update
Resume Next_UpdateContact
Else
MsgBox Err.Description
UpdateContact = False
Resume Exit_UpdateContact
End If
End Function
zMisc wrote:
Giles,
I've not been able to fix it.
Please see http://bugs.mysql.com/bug.php?id=17213 (I've reported this to
MySQL). Have not tried the possible fix on rounding the floating number to 2
decimal places yet.
Are you updating a floating point number (mine is)?
Rgds
Young
Young,
I have exactly the same problem with "row cannot be located..." I've trid
[quoted text clipped - 27 lines] Regards,
Bill K.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....neral/200602/1 
