Mangled, with a difference

[Tom Ellison]

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The left
paren is replaced by a left square bracket. The right paren is replaced by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer. It
doesn't work when installed on the only other computer we have tried.

So, I opened it in SQL View and pasted in the original, unmangled query
text. This computer then mangles it differently when it is saved, but it
works there.

So, we have raised mangling to a new level. I'm grateful it is at least
consistent. Each computer mangles such that the query still works there.

In order to provide a distributalbe version that can be copied to other
systems, I'm thinking I need a "distribute" command button on the opening
form that fixes up this query, saving it's original, unmangled version on
each system. Just copying the front end to each one would not be enough.
This will make maintenance a bit more difficult. If anyone changes this
query, they have to know to preserve the unmangled version and put it into
the code for the "distribute" button. Few of us would know to look for such
a feature!

The systems are both Office XP, but may have different SP versions. I'm not
actually at the client site where the delivered software had this problem.
And, yes, this really doesn't help my reputation with the client! The stuff
is expected to arrive and work. That's why I test it.

This whole thing is a new level of an old difficulty to me. Has anyone else
seen this? It seems to be a fairly severe new thing, and not much fun.

Tom Ellison

 

[John Spencer]

Dear Tom,
Have you tried saving the unmangled version as a text string and then using
that to build the query each time? I don't know if that will work for you
in your situation, but it may be something you can try.

I know this may cause a performance hit, but in most cases the hit is so
small in terms of time that the users don't notice.

John

 

[Jerry Whittle]

Hi Tom,

Could the different computers have different International settings?
Possibly foreign versions of Access?

Do any of the fields in the query have special characters or spaces in their
names? That's one reason I always suggest only use the 123s and ABCs plus the
underscore ( _ ) in such names. No other special characters or spaces. Access
tries to help, but sometimes confuses, err, mangles things.

Also would you happen to have any reserved words such as Date, Day, Month,
and about 100 words as field names? That can cause problems. For the entire
list check out:

http://support.microsoft.com/kb/286335/

 

[Tom Ellison]

Dear John:

Thanks very much. I don't need any help with a solution to this. It's up
and running on the only computer that matters.

My intention in posting was to explore whether "inconsistent mangling" was a
new feature in Access. I've never heard of this before, and it concerns me.
Is this a new situation, or have others experienced it?

If this can be confirmed, perhaps we can finally get MS to create an option
NOT to mangle queries, or to do the mangling only on a copy of the saved
query at run-time. I'm assuming that whatever version difference caused my
experience is one that mangles the same "original SQL" copy in ways that
will consitently function properly when it then runs the query. If not,
then we have a real problem.

So, John, my friend, this is the first report of any related problem I've
ever heard. Is it new to you also? Anybody else ever heard of it? If it
is totally new to you, is it disturbing? I can see considerable annoyance
in the difficulty of distributing an applicaiton if it works only on some
computers without extra intervention. Does that make sense?

What you are suggestion is very close to what I am suggesting. I understand
you are suggesting to have Access rebuild its mangled version at run time
every time the query is invoked. In my case, the problem may be that the
query is not a "final" query, but is used only when referenced by another
query, one of those composite query deals we're forced to do to avoid "too
complex" issues, and to improve performance (this query's output is put into
a temp table for the next step, another query. If you embed these queries,
it runs for a few hours. If you dump the partial results in a table, it
runs in 2 seconds. I think this indicates a slight lack of query
optimization in Jet. You'll never see anything like this with MSDE!)

So, the way I understood your suggestion, how would that be implemented for
an "intermediate level" query? If you are saying to "save" the query from
its original, unmangled text each time, I don't see how that is better than
just doing the same thing once on each computer, allowing it to mangle the
SQL once and save it, assuming it will then run the query properly using its
own mangling algorithm. Or, did you intend something else by this, and I
didn't understand you?

Whether Jet requires a mangled version of a query in order to execute it is
unknown to me. The performance hit is, to my experience, rather slight. It
doesn't seem to take long to mangle. In fact, I'd say that Microsoft has
created the most efficient query mangling software I've ever seen.
Personally, I stand in awe! I will endorse the fact that this is world
class query mangling software. Cousin Larry (same last name as me) eat your
heart out!

More seriously, I'm wondering if my rather more serious problem with
mangling could not be used to motivation the creation of a Mangle option.
If turned off, saved queries would not be mangled when saved, but would be
retrieved and mangled at run-time, thus being transparent to us, except for
the processing necessary to mangle a copy of the saved query at run-time.
The difficulty would come about if an unmangled query were run with the
Mangle option turned on (the default). The system would need to track the
fact of whether each query is already Mangled, and perform the mangling
based on that switch as set for each query, and without reference to the
global Mangle option.

Even with this, I pitty the programmer who embeds a mangled query in his
code, if it won't then run on all systems to which the software is
distributed.

Tom Ellison

 

[Tom Ellison]

Dear Jerry:

Thanks for your reply.

In my experience, International settings aren't supposed to break the code
we write. I certainly HOPE there's nothing to what you suggest. That would
be a worse situation than what I'm already looking at.

I'm quite certain I've handled the query coding correctly. Names of
columns, aliases, and table names are in brackets as required, etc.

Your definition of "help" exceeds mine by several orders of magnitude. I
code queries exactly the way I want to see them. Any changes to that cannot
be considered as helpful in any degree.

Reserved words I readily recognize, and when someone else (I never do) uses
one in a column or table name, I bracket those, too. In any case, the query
in its original form runs exactly correctly. It's the mangled form that
doesn't run at all. Computer B doesn't like how Computer A mangles the
software. Both run perfectly when allowed to mangle the original query
source for themselves. I believe that fairly nails the source of the
problem.

If you have any further analysis, I would be very glad to read it.

Tom Ellison

 

[John Spencer]

Dear Tom,

No, I was just suggesting a possible workaround.
-- I haven't seen the behavior where Access changes the query differently on
different systems.
-- I have seen it change my queries.

What I would like to see is Access leave my SQL alone instead of
reformatting it as long as I stay in the text window. If I use the query
grid to write the query, then great let Access do it's thing. Otherwise,
leave my SQL alone.

I would also like to see the ability to add comments to my SQL code.

 

[Marshall Barton]

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The left
paren is replaced by a left square bracket. The right paren is replaced by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer. It
doesn't work when installed on the only other computer we have tried.

Tom,

The parens around a subquery in the FROM clause is a new
"feature" in A2K or AXP in an attempt to be a little closer
to "standard" SQL. This feature was weakly implemented as a
simple translation to the older A97 (and earlier) syntax of
the square brackets/dot. The big issue with the square
bracket syntax is that you can not have any square brackets
nested inside the square brackets, not even around a field
name.

With the above caveat, I don't expect it to have a problem.
I certainly do expect it to be the same on all machines
(though maybe not quite the same on different versions of
Access).

I'm digging pretty deep in to my fading memory here, but I
seem to remember that the dot was supposed to be followed by
a table alias name.

 

[Tom Ellison]

Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The dot
appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details here). I
received the client's database, added things to it, and sent it back. It
didn't work! We tracked it to this query, in which that is the only
mangling I saw. I took that mangling and edited it back to be just simple
parens, and emailed that text to the client, who pasted it into the SQL view
and saved it. I am only presuming that the client's computer then mangled
it somehow differently, but that it "likes" the alternate way of mangling
it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table names
inside the square brackets Access added which are themselves inside square
brackets. There are spaces in these names, so I have no choice. However,
this syntax does work on my computer, but obviously not on the client's
computer. It seems to me you have tracked this problem right to its soruce.
My system is mangling this exactly as you predicted, but doesn't have a
problem with square brackets inside square brackets, as you explained.
Likely the client's computer does have that problem, hence the inability to
run that query. My system, then, which has automatic updating, is too
up-to-date to function with theirs. Still, this seems to me an unacceptable
difficulty.

Is there any chance you'd like to post this in the private NG where some of
the MS techies may see it? I'll just have to suffer if they don't like my
humor in my earlier post. I do have a cousin Larry, but not the one you may
thought I had implied. It might also be useful to see what comments other
MVPs have on this, and to keep them informed so they can be ready in case it
happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle at
all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep a
"pre-mangled" version of it, hidden from me, ready to run, created every
time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in case
the Access there is different. For that matter, it would have to re-mangle
the first time the query is executed after an upgrade to the software on the
same computer, in case the mangling version changes. But this has to be
built into Access, not be something we have to detect and overcome at the
application level.

I'd like to know what my Access XP would do if I converted the project for
Access 2000. Would it change the mangling so this query is compatible with
older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good to
be able to point them to this thread, or to an FAC aritcle on it on one of
the web sites. Whether that is as important as I think it may be depends,
in part, on whether this is happening commonly. It would be good to know
between what version and sub-versions of Access this problem occurs. Again,
in my case, this has been a distribution related problem. When the problem
was reported, and I saw the mangling, it didn't take but a few mintues to
surmise that might be the cause, and I emailed the query in original
version, restored by simply replacing the []. with (), and had them paste
that in. The problem was fixed in no more than a quarter hour's work, but
the next developer may not suspect this as the cause, and may waste a lot of
time trying to figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you in
any case. All the more so when you have a valuable contribution, which you
so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.

Tom Ellison

 

[Marshall Barton]

Since you can not change the table names to get rid of the
space characters, how about creating a query that aliases
everything that would require square brackets around it.
Then write your problem query using the new query in the
[ . . . ]. As syntax

This issue has been around for a long time and I can't
imagine that MS is unaware of it, but I'll pass it on
anyway.
--
Marsh
MVP [MS Access]

Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The dot
appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details here). I
received the client's database, added things to it, and sent it back. It
didn't work! We tracked it to this query, in which that is the only
mangling I saw. I took that mangling and edited it back to be just simple
parens, and emailed that text to the client, who pasted it into the SQL view
and saved it. I am only presuming that the client's computer then mangled
it somehow differently, but that it "likes" the alternate way of mangling
it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table names
inside the square brackets Access added which are themselves inside square
brackets. There are spaces in these names, so I have no choice. However,
this syntax does work on my computer, but obviously not on the client's
computer. It seems to me you have tracked this problem right to its soruce.
My system is mangling this exactly as you predicted, but doesn't have a
problem with square brackets inside square brackets, as you explained.
Likely the client's computer does have that problem, hence the inability to
run that query. My system, then, which has automatic updating, is too
up-to-date to function with theirs. Still, this seems to me an unacceptable
difficulty.

Is there any chance you'd like to post this in the private NG where some of
the MS techies may see it? I'll just have to suffer if they don't like my
humor in my earlier post. I do have a cousin Larry, but not the one you may
thought I had implied. It might also be useful to see what comments other
MVPs have on this, and to keep them informed so they can be ready in case it
happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle at
all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep a
"pre-mangled" version of it, hidden from me, ready to run, created every
time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in case
the Access there is different. For that matter, it would have to re-mangle
the first time the query is executed after an upgrade to the software on the
same computer, in case the mangling version changes. But this has to be
built into Access, not be something we have to detect and overcome at the
application level.

I'd like to know what my Access XP would do if I converted the project for
Access 2000. Would it change the mangling so this query is compatible with
older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good to
be able to point them to this thread, or to an FAC aritcle on it on one of
the web sites. Whether that is as important as I think it may be depends,
in part, on whether this is happening commonly. It would be good to know
between what version and sub-versions of Access this problem occurs. Again,
in my case, this has been a distribution related problem. When the problem
was reported, and I saw the mangling, it didn't take but a few mintues to
surmise that might be the cause, and I emailed the query in original
version, restored by simply replacing the []. with (), and had them paste
that in. The problem was fixed in no more than a quarter hour's work, but
the next developer may not suspect this as the cause, and may waste a lot of
time trying to figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you in
any case. All the more so when you have a valuable contribution, which you
so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.

Tom Ellison

 

[Ken Snell \(MVP\)]

Tom -

My experience with this subquery as a "table" in SQL in ACCESS (where ACCESS
puts the [ ]. syntax around the subquery) is that it completely blows up if
I have embedded [ ] characters in the subquery. I also have found that the
query will stop working if I create the query, save it, close it (and ACCESS
"mangles" it to the [ ]. syntax), and then I try to open the query for
editing/viewing the design or SQL view. At that point, I have to rebuild the
query from scratch and let ACCESS "mangle" it all over again.

My solution has been to not use subqueries for tables -- has led to many
"hoop jumps" and to having to create separate queries that then are used as
the tables. But I found it to be much more time-efficient and
low-frustration than having to constantly fix/rebuild the queries.

I can't say that I've noted that the mangling differs on different PCs,
though, because I have never let such queries escape my PC in that form. < g
--

Ken Snell
<MS ACCESS MVP>

Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The dot
appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details here).
I received the client's database, added things to it, and sent it back.
It didn't work! We tracked it to this query, in which that is the only
mangling I saw. I took that mangling and edited it back to be just simple
parens, and emailed that text to the client, who pasted it into the SQL
view and saved it. I am only presuming that the client's computer then
mangled it somehow differently, but that it "likes" the alternate way of
mangling it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table names
inside the square brackets Access added which are themselves inside square
brackets. There are spaces in these names, so I have no choice. However,
this syntax does work on my computer, but obviously not on the client's
computer. It seems to me you have tracked this problem right to its
soruce. My system is mangling this exactly as you predicted, but doesn't
have a problem with square brackets inside square brackets, as you
explained. Likely the client's computer does have that problem, hence the
inability to run that query. My system, then, which has automatic
updating, is too up-to-date to function with theirs. Still, this seems to
me an unacceptable difficulty.

Is there any chance you'd like to post this in the private NG where some
of the MS techies may see it? I'll just have to suffer if they don't like
my humor in my earlier post. I do have a cousin Larry, but not the one
you may thought I had implied. It might also be useful to see what
comments other MVPs have on this, and to keep them informed so they can be
ready in case it happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle at
all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep a
"pre-mangled" version of it, hidden from me, ready to run, created every
time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in
case the Access there is different. For that matter, it would have to
re-mangle the first time the query is executed after an upgrade to the
software on the same computer, in case the mangling version changes. But
this has to be built into Access, not be something we have to detect and
overcome at the application level.

I'd like to know what my Access XP would do if I converted the project for
Access 2000. Would it change the mangling so this query is compatible
with older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets
sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good to
be able to point them to this thread, or to an FAC aritcle on it on one of
the web sites. Whether that is as important as I think it may be depends,
in part, on whether this is happening commonly. It would be good to know
between what version and sub-versions of Access this problem occurs.
Again, in my case, this has been a distribution related problem. When the
problem was reported, and I saw the mangling, it didn't take but a few
mintues to surmise that might be the cause, and I emailed the query in
original version, restored by simply replacing the []. with (), and had
them paste that in. The problem was fixed in no more than a quarter
hour's work, but the next developer may not suspect this as the cause, and
may waste a lot of time trying to figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you in
any case. All the more so when you have a valuable contribution, which
you so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.

Tom Ellison

 

[Tom Ellison]

Dear Marsh:

I will ask very simply. Has this ever, in your experience, led to a
database that cannot be distributed because a query that runs on one
computer does not run on another computer, given that both have the same
major version of Access? This is what was both new and distrubing to me.

Tom Ellison

Since you can not change the table names to get rid of the
space characters, how about creating a query that aliases
everything that would require square brackets around it.
Then write your problem query using the new query in the
[ . . . ]. As syntax

This issue has been around for a long time and I can't
imagine that MS is unaware of it, but I'll pass it on
anyway.
--
Marsh
MVP [MS Access]

Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The dot
appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details here).
I
received the client's database, added things to it, and sent it back. It
didn't work! We tracked it to this query, in which that is the only
mangling I saw. I took that mangling and edited it back to be just simple
parens, and emailed that text to the client, who pasted it into the SQL
view
and saved it. I am only presuming that the client's computer then mangled
it somehow differently, but that it "likes" the alternate way of mangling
it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table names
inside the square brackets Access added which are themselves inside square
brackets. There are spaces in these names, so I have no choice. However,
this syntax does work on my computer, but obviously not on the client's
computer. It seems to me you have tracked this problem right to its
soruce.
My system is mangling this exactly as you predicted, but doesn't have a
problem with square brackets inside square brackets, as you explained.
Likely the client's computer does have that problem, hence the inability
to
run that query. My system, then, which has automatic updating, is too
up-to-date to function with theirs. Still, this seems to me an
unacceptable
difficulty.

Is there any chance you'd like to post this in the private NG where some
of
the MS techies may see it? I'll just have to suffer if they don't like my
humor in my earlier post. I do have a cousin Larry, but not the one you
may
thought I had implied. It might also be useful to see what comments other
MVPs have on this, and to keep them informed so they can be ready in case
it
happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle at
all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep a
"pre-mangled" version of it, hidden from me, ready to run, created every
time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in
case
the Access there is different. For that matter, it would have to
re-mangle
the first time the query is executed after an upgrade to the software on
the
same computer, in case the mangling version changes. But this has to be
built into Access, not be something we have to detect and overcome at the
application level.

I'd like to know what my Access XP would do if I converted the project for
Access 2000. Would it change the mangling so this query is compatible
with
older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets
sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good to
be able to point them to this thread, or to an FAC aritcle on it on one of
the web sites. Whether that is as important as I think it may be depends,
in part, on whether this is happening commonly. It would be good to know
between what version and sub-versions of Access this problem occurs.
Again,
in my case, this has been a distribution related problem. When the
problem
was reported, and I saw the mangling, it didn't take but a few mintues to
surmise that might be the cause, and I emailed the query in original
version, restored by simply replacing the []. with (), and had them paste
that in. The problem was fixed in no more than a quarter hour's work, but
the next developer may not suspect this as the cause, and may waste a lot
of
time trying to figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you in
any case. All the more so when you have a valuable contribution, which
you
so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.

Tom Ellison

Tom Ellison wrote:

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The
left
paren is replaced by a left square bracket. The right paren is replaced
by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer. It
doesn't work when installed on the only other computer we have tried.

Tom,

The parens around a subquery in the FROM clause is a new
"feature" in A2K or AXP in an attempt to be a little closer
to "standard" SQL. This feature was weakly implemented as a
simple translation to the older A97 (and earlier) syntax of
the square brackets/dot. The big issue with the square
bracket syntax is that you can not have any square brackets
nested inside the square brackets, not even around a field
name.

With the above caveat, I don't expect it to have a problem.
I certainly do expect it to be the same on all machines
(though maybe not quite the same on different versions of
Access).

I'm digging pretty deep in to my fading memory here, but I
seem to remember that the dot was supposed to be followed by
a table alias name.

 

[Tom Ellison]

Dear Ken:

An interesting point about my query has these parts:

1. On my system the query was mangled so as to have square brackets within
square brackets. Perhaps a recent release has changed this limitation. My
automatic updates are in action, so this could be a very recent change.

2. The query, as mangled, operates on my system but not the client's. This
is a severe kind of distribution problem, isn't it?

3. The query, installed using my original syntax, whether it is mangled or
not on the client's computer, runs correctly.

The project query required extreme measures to achieve good performance,
including an indexed temporary table for partial results about half way
through. I'm not sure with all the optimization work I did that I would
have been willing to risk slowing it to obtain the extra portability your
solution provides through an additional layer of queries. I had 5 layers of
queries as it is.

As I asked Marsh, very simply, have you ever seen or heard of a query
mangling / distribution problem before? Your solution would suggest you may
have done so. I'd like to hear clearly what you mean.

Thanks, Ken! Till we meet again! Going to NYC again any time soon?

Tom Ellison

Tom -

My experience with this subquery as a "table" in SQL in ACCESS (where
ACCESS puts the [ ]. syntax around the subquery) is that it completely
blows up if I have embedded [ ] characters in the subquery. I also have
found that the query will stop working if I create the query, save it,
close it (and ACCESS "mangles" it to the [ ]. syntax), and then I try to
open the query for editing/viewing the design or SQL view. At that point,
I have to rebuild the query from scratch and let ACCESS "mangle" it all
over again.

My solution has been to not use subqueries for tables -- has led to many
"hoop jumps" and to having to create separate queries that then are used
as the tables. But I found it to be much more time-efficient and
low-frustration than having to constantly fix/rebuild the queries.

I can't say that I've noted that the mangling differs on different PCs,
though, because I have never let such queries escape my PC in that form. <
g
--

Ken Snell
<MS ACCESS MVP>

Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The dot
appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details here).
I received the client's database, added things to it, and sent it back.
It didn't work! We tracked it to this query, in which that is the only
mangling I saw. I took that mangling and edited it back to be just
simple parens, and emailed that text to the client, who pasted it into
the SQL view and saved it. I am only presuming that the client's
computer then mangled it somehow differently, but that it "likes" the
alternate way of mangling it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table names
inside the square brackets Access added which are themselves inside
square brackets. There are spaces in these names, so I have no choice.
However, this syntax does work on my computer, but obviously not on the
client's computer. It seems to me you have tracked this problem right to
its soruce. My system is mangling this exactly as you predicted, but
doesn't have a problem with square brackets inside square brackets, as
you explained. Likely the client's computer does have that problem, hence
the inability to run that query. My system, then, which has automatic
updating, is too up-to-date to function with theirs. Still, this seems
to me an unacceptable difficulty.

Is there any chance you'd like to post this in the private NG where some
of the MS techies may see it? I'll just have to suffer if they don't
like my humor in my earlier post. I do have a cousin Larry, but not the
one you may thought I had implied. It might also be useful to see what
comments other MVPs have on this, and to keep them informed so they can
be ready in case it happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle
at all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep
a "pre-mangled" version of it, hidden from me, ready to run, created
every time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in
case the Access there is different. For that matter, it would have to
re-mangle the first time the query is executed after an upgrade to the
software on the same computer, in case the mangling version changes. But
this has to be built into Access, not be something we have to detect and
overcome at the application level.

I'd like to know what my Access XP would do if I converted the project
for Access 2000. Would it change the mangling so this query is
compatible with older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets
sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good
to be able to point them to this thread, or to an FAC aritcle on it on
one of the web sites. Whether that is as important as I think it may be
depends, in part, on whether this is happening commonly. It would be
good to know between what version and sub-versions of Access this problem
occurs. Again, in my case, this has been a distribution related problem.
When the problem was reported, and I saw the mangling, it didn't take but
a few mintues to surmise that might be the cause, and I emailed the query
in original version, restored by simply replacing the []. with (), and
had them paste that in. The problem was fixed in no more than a quarter
hour's work, but the next developer may not suspect this as the cause,
and may waste a lot of time trying to figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you in
any case. All the more so when you have a valuable contribution, which
you so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.

Tom Ellison

Tom Ellison wrote:

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The
left
paren is replaced by a left square bracket. The right paren is replaced
by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer. It
doesn't work when installed on the only other computer we have tried.

Tom,

The parens around a subquery in the FROM clause is a new
"feature" in A2K or AXP in an attempt to be a little closer
to "standard" SQL. This feature was weakly implemented as a
simple translation to the older A97 (and earlier) syntax of
the square brackets/dot. The big issue with the square
bracket syntax is that you can not have any square brackets
nested inside the square brackets, not even around a field
name.

With the above caveat, I don't expect it to have a problem.
I certainly do expect it to be the same on all machines
(though maybe not quite the same on different versions of
Access).

I'm digging pretty deep in to my fading memory here, but I
seem to remember that the dot was supposed to be followed by
a table alias name.

 

[Wayne Morgan]

Tom,

While I haven't seen the mangling, I have seen queries run on one computer
but not the next when they both have the same version of Access. The problem
was they had different service pack levels of Jet.

I have also seen perfectly good syntax cause Access to crash when the query
was run or when trying to save the query after editing. Removing all
"unneeded" (i.e. where able depending on spaces in names and when not needed
to support the logic of the statements) brackets and parentheses from the
SQL view would solve the problem as long as I never used the design grid. As
soon as I used the design grid, Access would "help" by putting all the
brackets and parentheses back in and I'd have to go to SQL view and remove
them again.

 

[Tom Ellison]

Dear Wayne:

Your post indicates clearly that there are distribution problems with Access
due to query featues, whether the result of mangling or not mangling, which
cause a "good" query on one system to be an error on the next. This problem
can then be overcome by the application of the latest Access service pack on
all systems. Is that your understanding? Is your conficence quite high
that this is the case? This would provide a solution to what I've
experienced. The question boils down to a famous quote:

"To mangle, or not to mangle. That is the question."

So, one final question. If an application is developed in XP, with the
latest SP, and a query is mangled according to the standards there, and that
applicaiton is subsequently saved to run in, say, Access 2000, will the
query be re-mangled according to the most recent standard for Access 2000,
or will it remaing mangled in a manner possibly unfit to be run under 2000?

Again, I am aiming at a core problem, whether we have a distribution
problem, or rather, how wide a distribution problem do we have, and how can
it best be avoided or overcome.

I do not expect you will have so much information as I have asked. If you
do, that's phenomenal. I do want to hear from you, and others, what is
known about this. I am currently involved in an average of 2 daily small
projects, often just to write a query and return this to a client. Most of
my clients are now other developers. This is what brought up the topic
here.

It certainly sounds like I need to request the clients to keep their Access
software completely updated, so we can avoid such problems and the time
wasted. Is that the primary solution? Any others?

Thanks for your assistance and opinions. I look forward to hearing just a
bit more from you and any others, to help confirm the analyses made here and
to evaluate the best course of action.

Tom Ellison

 

[Ken Snell \(MVP\)]

Hey Tom! (No, no current plans for NYC at this time, but I am hoping!)

I don't have the details of the previous problems because they were more
than a year ago, and all my notes from back then have been tossed. I am
sorry that I cannot give you more specific information, other than to
commisserate and to empathize with what you're experiencing, as I've had
similar issues. Recalling the complexity of various SQL statements that
you've used in the past, I am not surprised that workarounds will not be
attractive or even feasible for you in this case.

Hoping that another solution presents itself for you....
--

Ken Snell
<MS ACCESS MVP>

Dear Ken:

An interesting point about my query has these parts:

1. On my system the query was mangled so as to have square brackets
within square brackets. Perhaps a recent release has changed this
limitation. My automatic updates are in action, so this could be a very
recent change.

2. The query, as mangled, operates on my system but not the client's.
This is a severe kind of distribution problem, isn't it?

3. The query, installed using my original syntax, whether it is mangled
or not on the client's computer, runs correctly.

The project query required extreme measures to achieve good performance,
including an indexed temporary table for partial results about half way
through. I'm not sure with all the optimization work I did that I would
have been willing to risk slowing it to obtain the extra portability your
solution provides through an additional layer of queries. I had 5 layers
of queries as it is.

As I asked Marsh, very simply, have you ever seen or heard of a query
mangling / distribution problem before? Your solution would suggest you
may have done so. I'd like to hear clearly what you mean.

Thanks, Ken! Till we meet again! Going to NYC again any time soon?

Tom Ellison

Tom -

My experience with this subquery as a "table" in SQL in ACCESS (where
ACCESS puts the [ ]. syntax around the subquery) is that it completely
blows up if I have embedded [ ] characters in the subquery. I also have
found that the query will stop working if I create the query, save it,
close it (and ACCESS "mangles" it to the [ ]. syntax), and then I try to
open the query for editing/viewing the design or SQL view. At that point,
I have to rebuild the query from scratch and let ACCESS "mangle" it all
over again.

My solution has been to not use subqueries for tables -- has led to many
"hoop jumps" and to having to create separate queries that then are used
as the tables. But I found it to be much more time-efficient and
low-frustration than having to constantly fix/rebuild the queries.

I can't say that I've noted that the mangling differs on different PCs,
though, because I have never let such queries escape my PC in that form.
< g
--

Ken Snell
<MS ACCESS MVP>

Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The
dot appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details
here). I received the client's database, added things to it, and sent it
back. It didn't work! We tracked it to this query, in which that is the
only mangling I saw. I took that mangling and edited it back to be just
simple parens, and emailed that text to the client, who pasted it into
the SQL view and saved it. I am only presuming that the client's
computer then mangled it somehow differently, but that it "likes" the
alternate way of mangling it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table
names inside the square brackets Access added which are themselves
inside square brackets. There are spaces in these names, so I have no
choice. However, this syntax does work on my computer, but obviously not
on the client's computer. It seems to me you have tracked this problem
right to its soruce. My system is mangling this exactly as you
predicted, but doesn't have a problem with square brackets inside square
brackets, as you explained. Likely the client's computer does have that
problem, hence the inability to run that query. My system, then, which
has automatic updating, is too up-to-date to function with theirs.
Still, this seems to me an unacceptable difficulty.

Is there any chance you'd like to post this in the private NG where some
of the MS techies may see it? I'll just have to suffer if they don't
like my humor in my earlier post. I do have a cousin Larry, but not the
one you may thought I had implied. It might also be useful to see what
comments other MVPs have on this, and to keep them informed so they can
be ready in case it happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle
at all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep
a "pre-mangled" version of it, hidden from me, ready to run, created
every time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in
case the Access there is different. For that matter, it would have to
re-mangle the first time the query is executed after an upgrade to the
software on the same computer, in case the mangling version changes.
But this has to be built into Access, not be something we have to detect
and overcome at the application level.

I'd like to know what my Access XP would do if I converted the project
for Access 2000. Would it change the mangling so this query is
compatible with older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets
sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good
to be able to point them to this thread, or to an FAC aritcle on it on
one of the web sites. Whether that is as important as I think it may be
depends, in part, on whether this is happening commonly. It would be
good to know between what version and sub-versions of Access this
problem occurs. Again, in my case, this has been a distribution related
problem. When the problem was reported, and I saw the mangling, it
didn't take but a few mintues to surmise that might be the cause, and I
emailed the query in original version, restored by simply replacing the
[]. with (), and had them paste that in. The problem was fixed in no
more than a quarter hour's work, but the next developer may not suspect
this as the cause, and may waste a lot of time trying to figure this
out.

Thanks for jumping in here, Marsh. It's always great to hear from you
in any case. All the more so when you have a valuable contribution,
which you so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.

Tom Ellison


Tom Ellison wrote:

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The
left
paren is replaced by a left square bracket. The right paren is
replaced by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer. It
doesn't work when installed on the only other computer we have tried.

Tom,

The parens around a subquery in the FROM clause is a new
"feature" in A2K or AXP in an attempt to be a little closer
to "standard" SQL. This feature was weakly implemented as a
simple translation to the older A97 (and earlier) syntax of
the square brackets/dot. The big issue with the square
bracket syntax is that you can not have any square brackets
nested inside the square brackets, not even around a field
name.

With the above caveat, I don't expect it to have a problem.
I certainly do expect it to be the same on all machines
(though maybe not quite the same on different versions of
Access).

I'm digging pretty deep in to my fading memory here, but I
seem to remember that the dot was supposed to be followed by
a table alias name.

 

[Marshall Barton]

I've never heard of this issue in relation to different
machines behaving differently.

My only recollection about the "mangling" is something about
the first time the query is saved, it works, but the next
change would be in the square bracket context and would not
save. Not at all clear if something like a Compact forcing
a query recompile ran into the issue or not.

I don't know what's happening behind the scenes, but it sort
of feels like something along the lines of:
* user entered syntax is saved in some canonical form
Later, user views query and
* Canonical form is used to reconstruct query's SQL
statement, but it's different from the original.
* This different syntax can not be translated back into the
original canonical form.

I have only seen the unsavable "mangled" query problem once
or twice and, if I remember correctly, there were names that
needed to be enclosed in square brackets. Getting rid of
those names avoided the issue.
--
Marsh
MVP [MS Access]

I will ask very simply. Has this ever, in your experience, led to a
database that cannot be distributed because a query that runs on one
computer does not run on another computer, given that both have the same
major version of Access? This is what was both new and distrubing to me.


"Marshall Barton" wrote

Since you can not change the table names to get rid of the
space characters, how about creating a query that aliases
everything that would require square brackets around it.
Then write your problem query using the new query in the
[ . . . ]. As syntax

This issue has been around for a long time and I can't
imagine that MS is unaware of it, but I'll pass it on
anyway.

Your are spot on about this being a subquery in the FROM clause. The dot
appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives an
error message (sorry, I didn't bother the client with full details here).
I
received the client's database, added things to it, and sent it back. It
didn't work! We tracked it to this query, in which that is the only
mangling I saw. I took that mangling and edited it back to be just simple
parens, and emailed that text to the client, who pasted it into the SQL
view
and saved it. I am only presuming that the client's computer then mangled
it somehow differently, but that it "likes" the alternate way of mangling
it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table names
inside the square brackets Access added which are themselves inside square
brackets. There are spaces in these names, so I have no choice. However,
this syntax does work on my computer, but obviously not on the client's
computer. It seems to me you have tracked this problem right to its
soruce.
My system is mangling this exactly as you predicted, but doesn't have a
problem with square brackets inside square brackets, as you explained.
Likely the client's computer does have that problem, hence the inability
to
run that query. My system, then, which has automatic updating, is too
up-to-date to function with theirs. Still, this seems to me an
unacceptable
difficulty.

Is there any chance you'd like to post this in the private NG where some
of
the MS techies may see it? I'll just have to suffer if they don't like my
humor in my earlier post. I do have a cousin Larry, but not the one you
may
thought I had implied. It might also be useful to see what comments other
MVPs have on this, and to keep them informed so they can be ready in case
it
happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle at
all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before execution,
then do the mangling as a step every time it is run, or better yet, keep a
"pre-mangled" version of it, hidden from me, ready to run, created every
time the query is saved. Something like that. But it also has to
"remangle" the query if the database is copied to another computer, in
case
the Access there is different. For that matter, it would have to
re-mangle
the first time the query is executed after an upgrade to the software on
the
same computer, in case the mangling version changes. But this has to be
built into Access, not be something we have to detect and overcome at the
application level.

I'd like to know what my Access XP would do if I converted the project for
Access 2000. Would it change the mangling so this query is compatible
with
older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets
sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance to
know about this. When another developer runs into it, it would be good to
be able to point them to this thread, or to an FAC aritcle on it on one of
the web sites. Whether that is as important as I think it may be depends,
in part, on whether this is happening commonly. It would be good to know
between what version and sub-versions of Access this problem occurs.
Again,
in my case, this has been a distribution related problem. When the
problem
was reported, and I saw the mangling, it didn't take but a few mintues to
surmise that might be the cause, and I emailed the query in original
version, restored by simply replacing the []. with (), and had them paste
that in. The problem was fixed in no more than a quarter hour's work, but
the next developer may not suspect this as the cause, and may waste a lot
of
time trying to figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you in
any case. All the more so when you have a valuable contribution, which
you
so often do.

I'm glad somebody reads all the release notes on everything, and retains
them so well.


"Marshall Barton" wrote
Tom Ellison wrote:

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The
left
paren is replaced by a left square bracket. The right paren is replaced
by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer. It
doesn't work when installed on the only other computer we have tried.

Tom,

The parens around a subquery in the FROM clause is a new
"feature" in A2K or AXP in an attempt to be a little closer
to "standard" SQL. This feature was weakly implemented as a
simple translation to the older A97 (and earlier) syntax of
the square brackets/dot. The big issue with the square
bracket syntax is that you can not have any square brackets
nested inside the square brackets, not even around a field
name.

With the above caveat, I don't expect it to have a problem.
I certainly do expect it to be the same on all machines
(though maybe not quite the same on different versions of
Access).

I'm digging pretty deep in to my fading memory here, but I
seem to remember that the dot was supposed to be followed by
a table alias name.

 

[Tom Ellison]

Dear Ken:

Well, thanks for the sympathy. This instance of the problem is solved, the
software installed and working. I really didn't take more than 10 minutes
of everybody's time put together. But, for someone who has the same thing,
but starts in the wrong direction to look for it, it could waste hours or
days. Also, if this occurs in a project meant for wide distribution (which
is not the case for me right now) it would need some thinking about a
broader solution. Those were my reasons for posting. That and the
question: Is it possible to convince MS to make a change so we would have
the option to mangle at run time, and be able to keep a "portable" copy of
our queries as the saved version?

I thought this deserved an airing in the newsgroup, if for no other reason
than to alert MVPs and other frequent "answer guys" and to get our heads
together briefly about what would be a good solution. Thanks for your
participation.

Let me know next time we can meet in NYC. It's such a short hop from here
in Central Nebraska.

See you there!

Tom Ellison

Hey Tom! (No, no current plans for NYC at this time, but I am hoping!)

I don't have the details of the previous problems because they were more
than a year ago, and all my notes from back then have been tossed. I am
sorry that I cannot give you more specific information, other than to
commisserate and to empathize with what you're experiencing, as I've had
similar issues. Recalling the complexity of various SQL statements that
you've used in the past, I am not surprised that workarounds will not be
attractive or even feasible for you in this case.

Hoping that another solution presents itself for you....
--

Ken Snell
<MS ACCESS MVP>

Dear Ken:

An interesting point about my query has these parts:

1. On my system the query was mangled so as to have square brackets
within square brackets. Perhaps a recent release has changed this
limitation. My automatic updates are in action, so this could be a very
recent change.

2. The query, as mangled, operates on my system but not the client's.
This is a severe kind of distribution problem, isn't it?

3. The query, installed using my original syntax, whether it is mangled
or not on the client's computer, runs correctly.

The project query required extreme measures to achieve good performance,
including an indexed temporary table for partial results about half way
through. I'm not sure with all the optimization work I did that I would
have been willing to risk slowing it to obtain the extra portability your
solution provides through an additional layer of queries. I had 5 layers
of queries as it is.

As I asked Marsh, very simply, have you ever seen or heard of a query
mangling / distribution problem before? Your solution would suggest you
may have done so. I'd like to hear clearly what you mean.

Thanks, Ken! Till we meet again! Going to NYC again any time soon?

Tom Ellison

Tom -

My experience with this subquery as a "table" in SQL in ACCESS (where
ACCESS puts the [ ]. syntax around the subquery) is that it completely
blows up if I have embedded [ ] characters in the subquery. I also have
found that the query will stop working if I create the query, save it,
close it (and ACCESS "mangles" it to the [ ]. syntax), and then I try to
open the query for editing/viewing the design or SQL view. At that
point, I have to rebuild the query from scratch and let ACCESS "mangle"
it all over again.

My solution has been to not use subqueries for tables -- has led to many
"hoop jumps" and to having to create separate queries that then are used
as the tables. But I found it to be much more time-efficient and
low-frustration than having to constantly fix/rebuild the queries.

I can't say that I've noted that the mangling differs on different PCs,
though, because I have never let such queries escape my PC in that form.
< g


--

Ken Snell
<MS ACCESS MVP>


Dear Marsh:

Your are spot on about this being a subquery in the FROM clause. The
dot appears before the alias:

]. AS AliasName

has replaced my

) AS AliasName

It's very much as you have said. However, my client's computer gives
an error message (sorry, I didn't bother the client with full details
here). I received the client's database, added things to it, and sent
it back. It didn't work! We tracked it to this query, in which that is
the only mangling I saw. I took that mangling and edited it back to be
just simple parens, and emailed that text to the client, who pasted it
into the SQL view and saved it. I am only presuming that the client's
computer then mangled it somehow differently, but that it "likes" the
alternate way of mangling it. Or, perhaps, it didn't mangle it at all.

You are also absolutely correct about the fact that there are table
names inside the square brackets Access added which are themselves
inside square brackets. There are spaces in these names, so I have no
choice. However, this syntax does work on my computer, but obviously
not on the client's computer. It seems to me you have tracked this
problem right to its soruce. My system is mangling this exactly as you
predicted, but doesn't have a problem with square brackets inside
square brackets, as you explained. Likely the client's computer does
have that problem, hence the inability to run that query. My system,
then, which has automatic updating, is too up-to-date to function with
theirs. Still, this seems to me an unacceptable difficulty.

Is there any chance you'd like to post this in the private NG where
some of the MS techies may see it? I'll just have to suffer if they
don't like my humor in my earlier post. I do have a cousin Larry, but
not the one you may thought I had implied. It might also be useful to
see what comments other MVPs have on this, and to keep them informed so
they can be ready in case it happens to them.

If I use syntax that is acceptable, I'd so much prefer it not to mangle
at all. But it MUSTN'T mangle it so other versions of Access (or SP
sub-versions) cannot run it. If mangling is essential before
execution, then do the mangling as a step every time it is run, or
better yet, keep a "pre-mangled" version of it, hidden from me, ready
to run, created every time the query is saved. Something like that.
But it also has to "remangle" the query if the database is copied to
another computer, in case the Access there is different. For that
matter, it would have to re-mangle the first time the query is executed
after an upgrade to the software on the same computer, in case the
mangling version changes. But this has to be built into Access, not be
something we have to detect and overcome at the application level.

I'd like to know what my Access XP would do if I converted the project
for Access 2000. Would it change the mangling so this query is
compatible with older software?

I may want to ask this client to see that the system involved is fully
upgraded. But that is the computer of my client's client. It gets
sticky.

Does that make sense? Do you agree? Any other suggestions?

I'd prefer to know the MVPs, and all Access developers, have a chance
to know about this. When another developer runs into it, it would be
good to be able to point them to this thread, or to an FAC aritcle on
it on one of the web sites. Whether that is as important as I think it
may be depends, in part, on whether this is happening commonly. It
would be good to know between what version and sub-versions of Access
this problem occurs. Again, in my case, this has been a distribution
related problem. When the problem was reported, and I saw the mangling,
it didn't take but a few mintues to surmise that might be the cause,
and I emailed the query in original version, restored by simply
replacing the []. with (), and had them paste that in. The problem was
fixed in no more than a quarter hour's work, but the next developer may
not suspect this as the cause, and may waste a lot of time trying to
figure this out.

Thanks for jumping in here, Marsh. It's always great to hear from you
in any case. All the more so when you have a valuable contribution,
which you so often do.

I'm glad somebody reads all the release notes on everything, and
retains them so well.

Tom Ellison


Tom Ellison wrote:

A query I recently successfully completed gets mangled when I save it.

No surprise yet.

The mangling consists of replacing the parens around a subquery. The
left
paren is replaced by a left square bracket. The right paren is
replaced by
a right square bracket and a period.

The query, subsequent to this mangling, still works on my computer.
It
doesn't work when installed on the only other computer we have tried.

Tom,

The parens around a subquery in the FROM clause is a new
"feature" in A2K or AXP in an attempt to be a little closer
to "standard" SQL. This feature was weakly implemented as a
simple translation to the older A97 (and earlier) syntax of
the square brackets/dot. The big issue with the square
bracket syntax is that you can not have any square brackets
nested inside the square brackets, not even around a field
name.

With the above caveat, I don't expect it to have a problem.
I certainly do expect it to be the same on all machines
(though maybe not quite the same on different versions of
Access).

I'm digging pretty deep in to my fading memory here, but I
seem to remember that the dot was supposed to be followed by
a table alias name.

 

[Wayne Morgan]

Tom,

What it will do when it mangles the query, I don't know. As I stated, I
hadn't run into the mangle problem, just that I had had problems going from
one machine to the next. The problem wasn't the Access (i.e. Office)
patches, although I'm sure that they could cause the problem also, the
problem was the Jet service pack level. Access 2000, 2002, and 2003 all use
Jet 4 and the same Jet service pack applies to all of them. While Access may
be what is mangling the query, the query (I believe) is being interpreted by
Jet. So, if your version of Jet will accept the query mangled a certain way,
I would expect the same result on another machine with the same service pack
level of Jet.

 

[Tom Ellison]

Dear Wayne:

I agree. The Jet SP level is the primary suspect here. I'm just guessing,
but this is probably where the different version of mangling originate.
Otherwise, it would not be the case that a given system mangles the query
just right to be run on that system, but perhpas not on another system. So,
assiming this is true, and I'm about convinced it is, keeping all systems
updated to the most recent SP level would be a bit step.

Scary, but this also then makes me hope that newer SP levels are always
backward compatible, and that what I've just seen is a forward-compatible
problem. Makes sense, though, doesn't it?

Tom Ellison

 

[Wayne Morgan]

Yes, it makes sense, but we won't now until you check the version levels.
I've seen newer Jet service packs introduce problems where the older ones
worked. The current lever is SP8, which seems to have the "introduced" bugs
that I've found fixed again.

--
Wayne Morgan
MS Access MVP

Dear Wayne:

I agree. The Jet SP level is the primary suspect here. I'm just
guessing, but this is probably where the different version of mangling
originate. Otherwise, it would not be the case that a given system mangles
the query just right to be run on that system, but perhpas not on another
system. So, assiming this is true, and I'm about convinced it is, keeping
all systems updated to the most recent SP level would be a bit step.

Scary, but this also then makes me hope that newer SP levels are always
backward compatible, and that what I've just seen is a forward-compatible
problem. Makes sense, though, doesn't it?

Tom Ellison