Max SQL length for Passthrough Query

[Jezza0]

Does anyone know the maximum length allowed for a passthrough query in Access
2007? So far, I have found a query length of 76,944 fails, but length 52,537
works just fine.

The error I receive when attempting to assign the SQL (done in VBA) is 438 -
"object doesn't support this property or method."

If it makes any difference, the database server runs Oracle 9.

 

[Brendan Reynolds]

Does anyone know the maximum length allowed for a passthrough query in
Access
2007? So far, I have found a query length of 76,944 fails, but length
52,537
works just fine.

The error I receive when attempting to assign the SQL (done in VBA) is
438 -
"object doesn't support this property or method."

If it makes any difference, the database server runs Oracle 9.

The help file says that the maximum number of characters in a SQL statement
is approximately 64,000. It doesn't mention any difference between pass
through queries and other queries, so in the absence of any specific
documentation I'm assuming that this also applies to pass through queries.

 

[Jezza0]

Thanks for the quick response Brendan. I couldn't find it in the helpfile.

I figured I could this code to find the exact limit: 65,522 (odd how it's
just shy of 65,535)

Public Sub testqrydefs()
On Error GoTo ErrHandle
Dim qry As New QueryDef, str As String
qry.NAME = "Test"
qry.Connect = tmDocsConnect 'this is a const containing my pass-thru
connection
Do While Len(qry.SQL) < 100000
str = qry.SQL
qry.SQL = qry.SQL & "."
Loop
MsgBox "Ended ok"
ErrHandle:
If Err.Number > 0 Then
MsgBox "Max length: " & Len(str)
End If
End Sub