Hi TLowe,

If this section of your code isn't too long, would you mind posting it here?
What you have posted so far looks OK, but you don't show all the steps, so
maybe there is an issue elsewhere in your code. AFAIK, there is no
practical limit on the length of the Source argument to the Open method, so
I don't think you're running into a limitation that would be cutting off
your statement.

As onedaywhen pointed out, my reply to your original post was flat out
wrong. So please disregard it. But it is a good function to use to clean
up particular data items when it's possible they may contain apostrophes. I
don't use it often, however, because I typically use stored procedures
instead of ad-hoc SQL statements. With stored procedures, you don't have to
clean up your parameters before sending them to SQL.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


TLowe wrote:
Thanks Jake
That is an excellent function to use to clean up data. I was looking
for something like that to clean up some of the data passed to SQL.
I dont think that the source of this problem is the quotes. This is
just cutting off data like their is some kind of limit to the size.
I will keep looking and see if I can find anything that might shed
some light on the issue.




"Jake Marx" wrote in message
...
Hi TLowe,

You must "escape" any single quotes when sending SQL statements
directly to SQL Server via ADO. By that, I mean that you must add
another single quote immediately after any existing single quotes in
your SQL statement. Typically, I use a simple function to accomplish
this:

Public Function gsMakeStringSQLSafe(rsSQL As String) As String
gsMakeStringSQLSafe=Replace$(rsSQL, "'", "''")
End Function

Just pass your SQL statement through this function when opening your
recordset:

rs.Open gsMakeStringSQLSafe(strSQL), cnnStoredProc, _
adUseClient, adLockReadOnly

Hopefully, this will fix your problem.


TLowe wrote:
Hello all!
I am using an ADO recordset to import data into Excel 2000 from a Win
2003/SQL Server2000 setup. My problem is the SQL string is getting
truncated and is not passed to SQL server 2000 in a complete form.
Its cut off. It appears to cutoff around 185 characters. Is this a
setting that can be reset? Is this an ADO Property that should be
set to allow a bigger SQL statement to be passed to SQL server. Its
really weird that it only takes so many characters, truncates the
balance of the statement and then still tacks the double quotes at
the end of the statement? The

Here is the ADO recordset :
rs.Open strSQL, cnnStoredProc, adUseClient, adLockReadOnly

Here is the full SQL statement that is assigned to the strSQL
variable that is initially stored in the variable strSQL. This
statement runs in the SQL analyzer with no problems (variables x, and
y with actual values).

SELECT [description],count(t2.Result) as total,t2.Result,t2.Vendor,
t1.[date]from Tblquestions t1 inner join TblResultsQuestion t2 on
T1.[id] = T2.QuestionID where T1.type = 4 and vendor like '" & x & "'
and [description] like '" & y & "' group by [description],Vendor,
[date], result order by [description],Result desc

Here is the actual statement that gets put into the strSQL variable
when passed via the above rs.open statement.

strSQL : "SELECT [description],count(t2.Result) as total,t2.Result,
t2.Vendor, t1.[date] from Tblquestions t1 inner join
TblResultsQuestion t2 on T1.[id] = T2.QuestionID where t1.type = 4
and ve"