Help with passthrough query / error 3035

[ejstefl]

Hello,

I have a problem. I have a Pass Through query that I have saved (qryPassThrough) and I am using some VBA code to update the SQL statement in the query, like such:

Dim qdf as QueryDef
set qdf = CurrentDb.Querydefs("qryPassThrough")
qdf.SQL = "Insert SQL here..."

The error occurs on the "qdf.SQL = " line. The error is Error 3035: System Resouces Exceeded.

The error seems to occur only when the SQL portion of the query gets too big. If I start with a blank query, I can use the above code to modify the SQL with no problems. However, once I want to reset the SQL to a different statement, I get the above error message, but only if the SQL that is currently in the query is too long.

Can anyone help??

 

[Colby]

give this a shot

sSQL is a string
qdTemp is a string


sSQL = "Your vba created sql goes here"

Set qdTemp = db.QueryDefs("query name")
qdTemp.SQL = sSQL
qdTemp.Close

Don't know if this will help you out, but I always set my sql after
I have built the new statement.

HTH, let me know

 

[ejstefl]

That's pretty much what I'm doing... I'm setting it with a variable that I define first. Thank anyway!

 

[Colby]

Dim qdf as QueryDef
set qdf = CurrentDb.Querydefs("qryPassThrough")
qdf.SQL = "Insert SQL here..."

seems to be saying make my qdf be an existing query and then
make qdf.sql equal a different sql statement. It appears that it
is getting confused on what is what. I am new at this stuff too, but
I would try

sSQL = "Insert SQL here...."

Set qdTemp = db.QueryDefs("qryPassThrough")
qdTemp,SQL = sSQL
qdTemp.Close

Could be 6 one way and 1/2 dozen the other; but if you are still stuck it can't hurt.