QueryDef.SQL Limit? when trying SQL PassThrough

[Joey]

Hello All,

I'm trying to use an ODBC SQL Passthrough. I seem to be running into a
size limit for QueryDef.SQL I have a query string that is about 200,000
characters long and it fails as soon as I assign it to QueryDef.SQL. If
I shorten my query to 50,000 characters it seems to work fine Is there
some better way of doing this? Would ODBC Direct alleviate this problem?

THanks,
Joey.

 

[Brendan Reynolds]

According to the documentation (see the help topic 'Access specifications')
the maximum number of characters in a SQL statement is approximately 64,000.

My guess would be that the problem is most likely in the design of the
database. You should not need such a complex query to retrieve information
from a well designed database.

 

[Joey]

According to the documentation (see the help topic 'Access specifications')
the maximum number of characters in a SQL statement is approximately 64,000.

My guess would be that the problem is most likely in the design of the
database. You should not need such a complex query to retrieve information
from a well designed database.

How should I Update/Insert records into tables located on an SQL Server
with information stored locally on an Access97 application without
adding each record update/insert statement into the SQL statement?

 

[Andreas]

You could use VBA and loop through the data to be updated/inserted.

Regards,
Andreas

 

[Rick Brandt]

How should I Update/Insert records into tables located on an SQL
Server with information stored locally on an Access97 application
without adding each record update/insert statement into the SQL
statement?

Have you tried creating a link to the S/S table and then use UPDATE and/or
APPEND queries?

 

[Joey]

I am using VBA to loop through the data to create the QueryDef.SQL
statement like this:

For Each record in tblUpdate
strSQLQuery = strSQLQuery & "INSERT dbo.Table1
(Field1,Field2,...FieldN) VALUES (tblUpdateField1, tblUpdateField2,
....tblUpdateFieldN);"
Next record in tblUpdateInsert

QueryDef.SQL = strSQLQuery

The tblUpdate is a local MSAccess97 table and dbo.Table1 is a SQL Server
table. My string strSQLQuery eventually exceeds the 64,000 character
limit imposed by QueryDef.SQL. Will using an ODBC direct query (instead
of an ODBC Passthrough Query which I am currently using) solve this
limitation? Or is there some mechanism whereby I don't have to convert
my local values into strings in order to pass to the SQL Server the
updates as one huge string?

Thanks,
Joey.

 

[John Spencer (MVP)]

Have you tried the alternate syntax?

INSERT INTO DBO.Table1 (Field1, Field2, ...)
SELECT T.UpdateField1, T.Update.Field2, ...
FROM TblUpdate as T
WHERE T.SomeField = SomeLimitingValue

 

[John Spencer (MVP)]

Whoops. Hit that too soon.

You can't use that syntax as a pass-through query, but you can use it as an ODBC
query to a linked table.