Howdy all, please assist me here.

I am trying to insert an excel sheet into an Access database using a simple SQL query, insert/update and FIXFORSQL command.  This is to remove the apostrophe and other characters that SQL hates.  My problem is that the cell contains several hundred character and it seems the maximum amount of characters that the below fixforsql or replace command will work with is 256 characters.  Please see the code below and help - I have needed to add a left command to only insert the first 254 characters and this works ok, I want to be able to add an unlimited amount of characters into the memo field.


While Not objrs.EOF

DetailedDES = left(objrs("detailed_description"),254)
if not detailedDES = "" then
detailedDES = FixForSQL(DetailedDES)
end if

Function FixForSQL(tmpText1)
Dim tmpText2
tmpText2 = tmpText1
tmpText2 = Replace(tmpText1,vbCrLf,Chr(13))
tmpText2 = Replace(tmpText2,Chr(39),Chr(45))
tmpText2 = Replace(tmpText2,Chr(253),(chr(60) & chr(66) & chr(82) & chr(62)))
FixForSQL = tmpText2
End Function

and so on for the update command - this all works fine however only for the left 254 characters, remove the left command and it returns an error.
Microsoft OLE DB Provider for ODBC Drivers error 80040e14

[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression

Can someone please help

Thanks Stu -- burty1109 ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------

SQLXML 4.0 "timestamp" updategram question
Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
SQL Newbie
Iteration in a stored procedure