geefx
asked on
Max Length of ADO Recordset Source property
Does anyone know if there is a maximum length of the source property of an ado recordset.
If there is, what is the maximum length, and does anyone have a way of working around this.
I have a search form in which the user can enter criteria into a lot (40+) fields. Needless to say, the SQL query text can become quite long, and I seem to be having a problem if the query string is too long, am getting an error
"7874 : Microsoft Access can't find the object 'Recordset.'"
Thanks in advance for any assistance!
If there is, what is the maximum length, and does anyone have a way of working around this.
I have a search form in which the user can enter criteria into a lot (40+) fields. Needless to say, the SQL query text can become quite long, and I seem to be having a problem if the query string is too long, am getting an error
"7874 : Microsoft Access can't find the object 'Recordset.'"
Thanks in advance for any assistance!
Id say its the same as a query def (gueesing here) but that would be about 64k chars.
Are you sure that's the problem... does it work with a simple Select * from tblMyTable.... ???
Dave
Are you sure that's the problem... does it work with a simple Select * from tblMyTable.... ???
Dave
ASKER
Hmmm...not sure that is the problem at all, but seemed to be the only thing that it could be from my experimentation - if I shortened the sql string it seemed to work - it definitely wasn't an error in the sql string.
code portion:
Dim rsx As ADODB.Recordset
Set rsx = New ADODB.Recordset
With rsx
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
strSQLz = "LONG SQL STRING HERE"
.Source = strSQLz
.Open
Set Me.sfmSearchAllResults.For
.ActiveConnection = Nothing
End With
The error occurs (with a long sql string only) with the statement
Set Me.sfmSearchAllResults.For
The recordset is actually returned fine (so I guess that it is not the length of the ado .source property), but when I assign the recordset to the subform, that is when it fails.
It works fine if I use a "select * from products" type string.
Very Very strange!!!
code portion:
Dim rsx As ADODB.Recordset
Set rsx = New ADODB.Recordset
With rsx
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
strSQLz = "LONG SQL STRING HERE"
.Source = strSQLz
.Open
Set Me.sfmSearchAllResults.For
.ActiveConnection = Nothing
End With
The error occurs (with a long sql string only) with the statement
Set Me.sfmSearchAllResults.For
The recordset is actually returned fine (so I guess that it is not the length of the ado .source property), but when I assign the recordset to the subform, that is when it fails.
It works fine if I use a "select * from products" type string.
Very Very strange!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the limit on the length of the sql string does appear to be 2048 chars as you say LSMConsulting - did a bit of playing around, and found it was fine for 2048, failed for 2049 characters in the sql string.
So based on that here is what I think is happening:
The ado recordset is returned with no problems, so there doesn't seem to be a limit on the size of the sql string here (at least not that I've hit)
When the recordset is assigned to the form, the source of the ADO recordset is assigned to the source of the form. So if this is > 2048 characters, then it fails, just as it would have if I had assigned the sql statement as the recordsource of the form itself.
Pretty annoying - don't know if there is a workaround for this??
LSMConsulting, have accepted your answer since you pointed me in the right direction.
Thanks for your help on this - very much appreciated - if you have any ideas on how to work around this, I'd love to hear them!
So based on that here is what I think is happening:
The ado recordset is returned with no problems, so there doesn't seem to be a limit on the size of the sql string here (at least not that I've hit)
When the recordset is assigned to the form, the source of the ADO recordset is assigned to the source of the form. So if this is > 2048 characters, then it fails, just as it would have if I had assigned the sql statement as the recordsource of the form itself.
Pretty annoying - don't know if there is a workaround for this??
LSMConsulting, have accepted your answer since you pointed me in the right direction.
Thanks for your help on this - very much appreciated - if you have any ideas on how to work around this, I'd love to hear them!
Two things come to mind:
1) Trim your sql as much as possible - for example, remove table names where possible:
SELECT tblCust.FName, tblCust.LName FROM tblCust
Instead, write this:
SELECT FName, LName FROM tblCust
2) Try writing your results to a temporary table, then you can do a SELECT * FROM YourTempTable" to populate your form/contro.
1) Trim your sql as much as possible - for example, remove table names where possible:
SELECT tblCust.FName, tblCust.LName FROM tblCust
Instead, write this:
SELECT FName, LName FROM tblCust
2) Try writing your results to a temporary table, then you can do a SELECT * FROM YourTempTable" to populate your form/contro.
Could you post the portion of the code where you're setting/resetting and filling your recordset?