This is Access 2007 with a SQL Server 2005 back end. Within my application I have a routine called ExecuteSQL() which is used constantly to write strings of data to the SQL Server. It uses the .Execute method of an ADODB.command object I have noticed today that this routine is failing on a statement which has got CR/LF pairs in the data being written.
However, if I paste the exact same SQL Statement into a Query window in SQL Management Studio, it executes without a problem.
If I then remove the CR/LF pairs, the statement will execute within MsAccess.
My original statement looks like this:
Insert into RevisionDetails (RevisionRef, FieldName, OldValue,NewValue) Values(22981, 'Payment Details', '', 'Settlement of this invoice preferably using SWIFT
should be made by paying our Bankers:
The Royal Bank of Scotland plc
International Payment London Operations
P.O. Box 34842 Islington High Street,
LONDON N1 8XLSwift Address:
RBOS GB 2LAccount ')