Memo field issues

[XP]

Using Office 2003 and Windows XP;

I have a text box control on a form that updates a memo field in a table.
The control is not bound. I am using ADO to upload the data
(Currentproject.Connection.Execute sSQL). The update saves several controls
at once to fields in a single table.

I want to be able to enter research notes into the text box that could
include international characters, commas, apostrophe's, and virtually any
other characters you would find on a keyboard; and also new paragraphs, etc.

I'm having a problem in that I can enter a certain amount of data and it
works fine. I enter some more, and it's fine. However at some point, the
program won't update. I get: Run-time error -21427217900 (80090e14) syntax
error (missing operator) in query expression --- and then it shows the first
part of my text entry.

I have not been able to isolate why or when this occurs. Is it the
characters I'm using? The inserted new paragraphs ([Ctrl]+[Enter]) or what?
The field type in the table is set to MEMO; I have not imposed any character
limits. Later, I can easily exceed the current number of characters without
error. It's sporadic.

Anybody have any ideas? Is there a way I can make it more stable? Is it
possible to import an MS-Word document instead and just store that?

Thanks much in advance for your input.

 

[XP]

Not completely sure, but I think my issue is related to the apostrophe
character. When I upload the data, I am already surrounding the variable as
in:

sSQL = sSQL & "CITATION_DESCRIPTION = '" & Me.CitationDesc & "', "

What else does one need to do to get this to work?