Disappearing SQL Code in Queries

[RogerCooper]

Each day, I run a long series of queries, the results of which are e-mailed. Sometimes, most of the SQL code in the query disappears after sending the e-mail. All that remains is the first clause such as "SELECT DISTINCTROW TOP 7" while all the remaining code is gone. The e-mail itself has the correct data.

This is long-standing problem, but it has been getting worse lately. I keep a table that has all the SQL code for queries so I can restore it easily, but it would be better if I did not need to do so. I repair/compact daily.

Does anyone know anything about this bug?

 

[arnelgp]

maybe the sql text is too long. i think the editor can only accommodate 255 chars.

 

[Minty]

Disappearing from where ? Your forms event / VBA project ?

That seems very unlikely.

 

[RogerCooper]

maybe the sql text is too long. i think the editor can only accommodate 255 chars.

The SQL code of a query is not limited to 255 characters. This bug occurs regardless of code length.

 

[RogerCooper]

Disappearing from where ? Your forms event / VBA project ?

That seems very unlikely.

It is disappearing from the Query itself.

 

[isladogs]

The actual limit is approx 64k characters. Slightly more than 255!
The number 255 is a limit for fields in a recordset or characters in a parameter.

For other such 'interesting' info see https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c?ui=en-US&rs=en-US&ad=US

I sometimes find the sql is truncated after an error. E.g. A lookup is used in a query field but the record doesn't exist. Look for such issues and try adding error handling e.g Nz functions if relevant to your situation

 

[RogerCooper]

I sometimes find the sql is truncated after an error. E.g. A lookup is used in a query field but the record doesn't exist. Look for such issues and try adding error handling e.g Nz functions if relevant to your situation

The queries themselves are correct and run properly after I restore the code. Furthermore, this is not truncation to 255 characters, instead they are being truncated to the SELECT clause.

 

[Minty]

You must have something that is modifying the queries programmatically.
I've never known a saved query change itself.

Either that or you have weird corruption issues.
Try importing all your objects into a new database.

 

[RogerCooper]

You must have something that is modifying the queries programmatically.
I've never known a saved query change itself.

Either that or you have weird corruption issues.
Try importing all your objects into a new database.

I have already tried a new database and it does not resolve the issue. And this occurs immediately after sending an e-mail. It is not an issue on queries that I print rather than e-mail.

I do have a program that restores the code automatically, but it still causes me some difficulties. I have found other posts with this problem, but none have solutions.

 

[isladogs]

When it happens to me, it's not limited to 255 either. Usually the sql ends randomly in the middle of a field name and always before the likely cause of any issue. Happened to me repeatedly the other day. That was a dlookup issue

 

[RogerCooper]

You have yet to tell us whether you are using an embedded string or a querydef. I suppose it is possible that you are using the RecordSource of a form but I think we should be done grasping at straws.

I am not using a form, these are querydef's.

 

[arnelgp]

The editor can only hold as much char 255, you have search and not found the solution, then these people cant help you either.

 

[arnelgp]

it always happens to me this.
i always use sub-query on my queries.
at first it will allow you to save it (long text query).
but when its time to load it again in SQL view, it is truncated.
it is my mistake that i always re-create it whenever i see that it is truncated.

if you found it that it is being truncated, don't save the query, immediately exit the editor and don't save. your query string is still intact. only the editor bug.

 

[The_Doc_Man]

Arnel, the "255" limit only occurs when something comes through a short text field that is in table somewhere. Truncation for length issues, if it occurs as such, would ALWAYS occur at the 255th character. (Been there, done that.) RogerCooper claims that it stops at a considerably shorter length AND at a syntactically complete sub-clause.

I am with Minty on this. To have this occur as described makes me think that the query is being modified dynamically, which I have often done - but never with this particular side-effect. Just lucky, I guess?

When preparing your e-mail, are you attempting to dynamically perform query modification in the query such that even the selected field list (before the FROM clause) is being altered? And since you said you can print results without this happening, what other query handling do you do differently?

 

[jdraw]

@RogerCooper

Is this a split database (front end/backend)?
Is this multi user database?
Do you manipulate the sql via code?

 

[isladogs]

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.

As previously mentioned I had the same issue in one of my own forms a couple of days ago. I've since altered the form sql and no longer have the issue

 

[RogerCooper]

How are you running them? What code are you using to email them?

I am using a function that calls SendObject

DoCmd.SendObject ObjectType, ObjectName, ObjectFormat, SendTo, CC, BCC, Subject, Body, False

 

[Minty]

That 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?

 

[RogerCooper]

That 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 stepping through the recordset of a list of all the queries and then using

QueryName = ![Query]

To set the source for the query to be sent.

 

[isladogs]

As previously requested by someone else, please post the full code used or a cut down version of your database.
At the moment all any of us can do is make informed guesses.