VBA SQL query gets truncated

[DCross]

Hello.


I am trying to add data to an existing, populated, Access table from a VBA data array. I only want to add the record to the Access table if it does not already exist in the table. I determine this using an SQL select command as follows:

sql_snippet.txt

My issue is that the "text" variable can be up to 2000characters and appears to be truncated in the "strSql" string (note: strSql didn't make it into the code snippet but it is dimensioned as a String).
Is there a limit to the size of a pattern match string in Access? If so, can it be increased?

Also, the TRTEXT field in the table which "text" is meant to search in a memo type field.

Thanks

[June7]

Exactly why I avoid memo type fields. Review http://allenbrowne.com/ser-63.html

[DCross]

Thanks for the quick response June7.
My query doesn't seem to be an exact match for the items described in http://allenbrowne.com/ser-63.html but I think I can see why it is getting truncated.

So is there any way to determine if a record in my access table is an exact match for my data array when one of the fields I have to compare is over 2000 characters?

Thanks again.

[June7]

Never had to so don't know.

Seem to remember reading something about breaking up the string into smaller pieces and search each piece. Problem with that is the string to match might be split between pieces.

[DCross]

OK.
Thanks for the information.