Strange errror with strings in VBA for Outlook [closed]

A very weird error! In a VBA module for Outlook I update a sql database, using a ADODB recordset. It all does fine, until I try to set a value for the field ‘sampletext’ with a string fabricated by a function which uses the ‘HTMLbody’ of an email. On one email, I get an error which is (how stupid of MS!) in Dutch but translates to something like “in a OLE DB-action consisting of several steps an error has occurred”. Note that the error occurrs as soon as I try to set the value for the field, that is well before the line which saves the record.
Whatever could be wrong with that string?

Investigating further, I printed the string in the direct-window, copied it and put it in my code instead of the fabricated string. No error!

Now here is where it gets really weird.
I compared the two strings and indeed they proved not identical. To find the difference I wrote a routine comparing the two strings character-by-character. The routine stopped when meeting the character “?”. In the one string it has ascii value 63, in the other string it has – what do you think – ascii value 63! In fact, when comparing just the ascii values instead of the one-character-strings themselves, no differences between the two strings are detected. How can that be?

The error seems fairly exceptionel by the way; when simply ignoring the error the field gets duly filled with the result of the function for most emails. But clearly not all.
There are two questions here:

1. What could be the difference between those two strings; where does it come from and how to detect it?
2. Why does Outlook start complaining as soon as I try to set this string as a value for the field in a ADODB recordset?
   Any ideas, someone?
   Hans

Added oct 15th: I know it’s customary to include code which would allow others to reproduce the problem, but the thing is: I do not know how to send an Outlook MailItem. An thát is the ssential part: the error occurrs with SOME emails only.
Thus, I had hoped that someone would simply recognise this problem from experience. In fact it aqll boils down to these questions which need no code to explain them:

• Did you ever meet an occasion where a string value from VBA was deemed unacceptable for a text-field in SQL-server?
• Did you ever meet this: Two strings (both declared with ‘Dim strX as string’) are compared byte-by byte in two ways: 1. comparison using one-character strings and 2. comparison of the ascii values of those one-character strings. With both precedures giving a different result: comparing the ascii values the strings are identical but comparing the character-strings themselves they meet some character which is somehow different.

Actually reproducing the error would be preferable but, as I said, I don´t know how to go about that. I guess I could write some code which would allow users of Outlook desktop version to monitor incoming emails for this error. Would that be an idea?

Just to satisfy the question “gimme your code” I’ll put it here although I don’t think it’s of much use without an example of an ‘offending email’ as i described. I brought it down to some completely trivial procedures.

1. I made a table in SQL like so:

   /****** Object: Table [dbo].[mailarchive] Script Date: 15-10-2023 15:52:23 ******/
   SET ANSI_NULLS ON
   GO

   SET QUOTED_IDENTIFIER ON
   GO

   SET ANSI_PADDING ON
   GO

   CREATE TABLE [dbo].[mailarchive](
   [EmailID] [decimal](19, 0) IDENTITY(1,1) NOT NULL,
   [AuthorAddress] varchar NULL,
   [sampletext] varchar NULL)
   ON [PRIMARY]

   GO

   SET ANSI_PADDING OFF
   GO

2. I made a System-DSN named ‘test’ which points to the db holding the above table.
   (user ‘yyy’, pwd ‘zzz’)

3. In Outlook I wrote this sub:

   Option Explicit
   Private dbConnect As New ADODB.connection

   Sub MailRegister()

   Dim Recset As New ADODB.Recordset

   Dim olNS As Outlook.NameSpace

   Dim olFolder As MAPIFolder

   Dim olMail As Outlook.MailItem

   Dim strBody As String

   dbConnect.ConnectionString = “DSN=test;UID=yyy;PWD=zzz”

   dbConnect.Open
   Recset.Open “Select * from mailarchive”, dbConnect, adOpenDynamic, adLockOptimistic

    Set olNS = Application.GetNamespace("MAPI")
   

   Set olFolder = olNS.PickFolder

    For Each olMail In olFolder.Items
   
        strBody = olMail.HTMLBody
   
        Recset.AddNew
   
            Recset("AuthorAddress") = olMail.SenderEmailAddress
   

‘error is in next line (sometimes 🙂

        Recset("sampletext") = Left(strBody, 500)

        Recset.Update

Next

    Recset.Close

    dbConnect.Close

End Sub