Disappearing SQL Code in Queries

[jdraw]

I am using a function that calls SendObject

Can you post the function code and the code where you execute the function?

OOOoops: Colin has requested same.

 

[RogerCooper]

This is a big database and creating a cut-down version would be time-consuming. There are over 3,000 select queries in the database and probably Access loses track of where it is sometimes. I thank all those who tried to help.

 

[The_Doc_Man]

There are over 3,000 select queries in the database and probably Access loses track of where it is sometimes.

We appreciate that it is a big database and can understand that you would have a LOT of work to do to make a presentable version. If you want to give up, OK, do so. But I hope you realize that we have very little to go on.

If this is a failure in some segment of Access, we have hope of getting that to Microsoft so they could look at it and give us some type of workaround or maybe even a patch. After all, this forum's membership includes more than one Microsoft MVP for Access, so we have some hope of being believed.

Be that as it may, we have no way to proceed, but I very SINCERELY doubt that Access just "loses track of where it is." That will ONLY happen if the database has somehow gotten corrupted such that its internal pointers no longer ponit where they should.

 

[isladogs]

The code I posted was indeed the record source for a form & came from this thread:
https://www.access-programmers.co.uk/forums/showthread.php?t=299195

I got it to happen more than once after first opening the database on a tablet but there seemed to be no pattern to it

My own similar issues (including a form from 2 days ago) were also for a form record source but I've since changed the SQL used. It was also random

However the OP has stated his issue occurs with a query def ....

 

[Galaxiom]

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.

 

[isladogs]

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.

Close - 256 characters in fact including spaces - just checked in Word
As stated in my last post, the source wasn't my database but it did have a memo field
In this case, the correct SQL was 'regained' after reopening the form so I'm not sure that the explanation fits

In almost 20 years using Access, I've only personally experienced corrupted records in the sense of 'Chinese' characters on a few occasions (though recently its come up a lot from other forum users).
IIRC, when its happened to me its NEVER been in tables with memo fields

 

[The_Doc_Man]

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.)

 

[isladogs]

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.)

Yes it really was 256.
With a knack for language like "my miserable memory moot", you should be writing newspaper headlines or getting those novels of yours published

 

[The_Doc_Man]

Might be time to look for a publisher again. However, the only headlines I make these days come from sleeping on a corduroy sofa cushion.

If it really was 256, then this phenomenon isn't due to short-text field truncation issues. I have no clue as to where this one originates because (of course) we don't have a way to see the actual internal code.

I reviewed the Access specifications page just to verify my memory. None of the maxima are listed as 256. All that are in that range are 255. The maximum number of characters in an SQL statement is "approximately 64,000."

More specifically for forms, the maximum number of characters in an SQL statement that is serving as a .Recordsource or .Rowsource is 32,750. Interesting that these are half the size of an SQL statement for a QueryDef.

Nowhere do I see anything that looks like 256 as a limit. The only thing I can think is that a specific type of corruption must have occurred. If it is true that a particular type of dynamic query building can trigger this, it is the first time I have EVER heard that a person could cause corruption by performing an action that is prima facie legal.

 

[static]

To look at the actual definition for a query called 'query1' you could use sql like

SELECT * FROM MSysQueries 
WHERE objectid = (select id from MSysObjects where Name="query1")

If you saved that as a query and look in MSysQueries, the 'Expression' field would be

objectid = (select id from MSysObjects where Name="query1")

So, a complex sub query possibly wouldn't be written properly / at all but you would expect some type of error message in that case.

 

[static]

Also, the queries could get changed if they are based on tables that are dropped and recreated and 'auto rename' is on.

 

[Mark_]

@OP,

Is there any place that you may accidentally be opening a form/query/report in design view prior to Emailing it? I've only seen query defs change on me when I find out I've done something silly along the way.

Rather than sending the whole database or a cut down version, can you post the code that generates your report and sends it?

"SELECT DISTINCTROW TOP 7"

screams "I've something that only needs 7 records and I accidentally saved the query def".

 

[The_Doc_Man]

static - good point about basing a query on a dropped table with auto-rename in place, but I would think that either opening the query would immediately signal an error or you would see some sort of indication on the navigation panel in the AllQueries section that the query has a problem even before you opened it. I would also consider it unlikely that a truncation would occur as was described. I would have to think that the query would just refuse to open.

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.

Once you did that, open the query that would either remember the table by its ID or its name, but in both cases they would match. However, if the fields mismatched, I could see Access getting confused. Even something that devious should only cause a "field not found" error.

If it DID lead to corruption, it would not be because of someone doing something legal and proper. Diddling with 'MSysObjects' is a case of playing with fire and asking to get burned really badly. So I'm still unsure how this could be caused by ordinary VBA or SQL that just has an error in it.

 

[static]

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.

edit+

Of course looking at the actual definition would be good but you could also debug.print the sql from the querydef to rule out some gui problem.

debug.? currentdb.querydefs("queryname").sql

edit 2+

And since parsing the sql will be client side, if this is a db on a server, I wouldn't rule out a dodgy network. SQL parses ok - data not written to table properly = invalid querydef.

 

[isladogs]

static

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.

Why do you say there is no table?
Despite requests, we've had no information about the query design other than the SELECT DISTINCTROW TOP 7 section.
Without additional information from the OP, all of us are just making informed guesses.

doc man

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.

As MSysObjects is basically read only, I don't follow your comment about forcing an object to have a specific ID in that table. AFAIK it can't be done

 

[static]

Why do you say there is no table?

Because there isn't one in the SQL the OP posted and SQL IS the query design.

 

[jdraw]

@RogerCooper

You said

All that remains is the first clause such as "SELECT DISTINCTROW TOP 7" while all the remaining code is gone.

Have you tried to debug.print yourQueryName.sql? repeatedly, until more SQL is printed??
Can you post the "remaining or full code" from backups?

You have not continued to participate in this thread---has something changed?

 

[isladogs]

Because there isn't one in the SQL the OP posted and SQL IS the query design.

I've checked again &still can't find any SQL in any of the posts written by the OP.
Perhaps you would be kind enough to tell me which post number as I must have missed it

 

[static]

SELECT DISTINCTROW TOP 7

That is (invalid) SQL.

If you tried to save it, Access (w)(sh)ouldn't let you.

SELECT DISTINCTROW TOP 7 FROM TABLE ...

 

[isladogs]

Its perfectly valid if you include at least one field!

For example:

SELECT DISTINCTROW TOP 7 Postcodes.PostcodeArea, Postcodes.Postcode FROM Postcodes;

He said it was truncated at SELECT DISTINCTROW TOP 7 - not that was how it was originally