I am stepping through the recordset of a list of all the queries and then usingThat send object line won't alter your saved queries.
What is the code used to set / adjust the record source in the objects you are sending?
I am using a function that calls SendObject
There are over 3,000 select queries in the database and probably Access loses track of where it is sometimes.
The SQL in the RecordSource of a form can "break". I don't know what causes it but the problem is ONLY with strings in the RecordSource property. Queries embedded in VBA or saved as querydefs don't break
I've just managed to replicate the issue on another database uploaded to the forum today.
The screenshots show a form record source truncated at an apparently random place and then opened again but this time with the full sql.
I didn't do an exact count but that looks very like 255 characters. I suspect this is a case of BLOB storage failure.
Access databases store a lot about themselves in system tables. Just like ordinary tables, a text field exceeding 255 characters only stores the first 255 characters in the field. The rest is stored elsewhere in a BLOB (Binary Large Object).
If you look in MSysObjects you will see several fields containing the value "Long Binary Data". I expect these are the related to references to the BLOB.
It is possible for the field to lose or corrupt the reference to the rest of the text stored in the BLOB.
BTW This problem is the main source of failure with Memo (Long Text) fields. Lose the pointer, lose the text. Damage the BLOB, damage the content of the Memo fields. If the pointer gets corrupted they can display "Chinese" characters because it is pointing to the wrong part of the BLOB.
Many developers avoid Memo fields for this reason and prefer to use a related table with multiple records if practical.
The thing is, 256 characters means it isn't a short text/long text problem because the range of short text is 0-255. There is no room for a 256th character. If the count was wrong and it really IS 255, OK. But as I understand it, and this comes from a discussion a LONG time ago, the string pointers for short text point to what is called an ASCIC string for which the count is the first byte and the next n bytes are the string. And 0 IS a valid string length, so 256 cannot be.
I would be willing to believe that the internal format has changed since that discussion, which would make my miserable memory moot. (But I haven't lost my writer's touch for alliteration.)
SELECT * FROM MSysQueries
WHERE objectid = (select id from MSysObjects where Name="query1")
objectid = (select id from MSysObjects where Name="query1")
screams "I've something that only needs 7 records and I accidentally saved the query def"."SELECT DISTINCTROW TOP 7"
debug.? currentdb.querydefs("queryname").sql
The query should error anyway.
"SELECT DISTINCTROW TOP 7" isn't valid SQL because there is no table
selected.
So I'm more intrigued by how/why Access is saving this SQL at all.
But that leads to another question that would be an absolute beast to investigate because of all of the ifs, ands, and buts associated therewith. If you have a query based on a table, that table will have an internal ID number. I checked and the ID field for 'MSysObjects' is NOT auto-numbered. So in theory it would be possible to create a query based on a table named ATable with ID 12345. Then you could delete that table and create another (different) table but force it to have ID 12345.
Why do you say there is no table?