• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1511
  • Last Modified:

Access is adding ". AS [%$##@_Alias]" and creating strange error

Access XP is making changes to a saved query, seemingly all by itself. The query below runs fine. I need to convert it into an Update query. This has happened several times now:  I save it, copy the SQL into a new query, and if I make any changes TO EITHER QUERY, **BOTH** of them acquire the ". AS [%$##@_Alias]"   Worse, after it acquires the Alias statement, it will no longer allow any changes to the SQL at all - running it afterwards produces an error that says the query name is invalid, and it infers the name to be the entire text of the SQL statement.  Does this sound like a virus, a problem somewhere in Access, or have I offended the SQL gods somehow?

SELECT COUNT(dbo_History.CommuterID) AS IndMatching
FROM [SELECT dbo_History.CommuterId, dbo_History.ReportDate, Count(dbo_History.CommuterId) AS CountOfCommuterId, dbo_History.UserCode, dbo_History.Printed
FROM dbo_History
GROUP BY dbo_History.CommuterId, dbo_History.ReportDate, dbo_History.UserCode, dbo_History.Printed
HAVING (((dbo_History.ReportDate)= Date()) AND ((dbo_History.UserCode)="webusr"))]. AS [%$##@_Alias];
0
DKJensen
Asked:
DKJensen
  • 3
  • 3
  • 2
1 Solution
 
walterecookCommented:
I've seen it too.  
What you CAN do is just alias it yourself and thus avoid Access needing to add the garbage.

Since you are using a query as a source of a query it has to call it SOMETHING.  Either you can name it, or it will.
Not a real big deal in my experience.

Walt

0
 
DKJensenAuthor Commented:

A) Tried that - it won't accept an alias.  It seemed to, when I added the alias 'as IndMatching,' but even that went away after I tried to revise the query.

B) It's a big deal if it also corrupts the original when I'm working on a copy.  What the?

C) It's a bigger deal if I can't get it to accept any changes after it adds the alias - renders it pretty useless.



0
 
Alan WarrenCommented:
Hi DKJensen,

Is this an ADP or an MDB with a connection to a sql server catalog?
When you refer to a 'saved query' are you referring to a View?

Alan
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DKJensenAuthor Commented:
Access is serving as a front end to a FoxPro database.  As to 'saved query,' I meant that I had tested it as a Select query, saved and named it in the Queries screen, then made a copy of it to convert the copy to an Update query.  It was when I was attempting to convert it to an Update that the Alias thing started.
0
 
Alan WarrenCommented:
Some discussion here:
http://www.experts-exchange.com/Databases/MS_Access/Q_20617276.html
http://rsl.gis.umn.edu/~gliang/resac/web/access.html
http://dbforums.com/arch/108/2002/5/369185

It seems the AS [%$##@_Alias]; is a behind the scenes thing that Access does, noramally hidden from the user. Apparently Access does this to all queries but does not usually display the aliasing in the query grid.
Personally I have never come accross this before, but here seems to be quite a few posts on the subject found by searching google for 'AS [%$##@_Alias];' hmm...

Just a shot in the dark here as I am unable to reproduce this behavior on my machines.
My guess is that Access and/or Jet is trying to Alias the Alias, so maybe if you removed the Alias before saving the query, it may work. Also not sure about the square brackets around the sub-select, the norm is round brackets.

SELECT COUNT(dbo_History.CommuterID) AS IndMatching
FROM (SELECT dbo_History.CommuterId, dbo_History.ReportDate, Count(dbo_History.CommuterId) AS CountOfCommuterId, dbo_History.UserCode, dbo_History.Printed
FROM dbo_History
GROUP BY dbo_History.CommuterId, dbo_History.ReportDate, dbo_History.UserCode, dbo_History.Printed
HAVING (((dbo_History.ReportDate)= Date()) AND ((dbo_History.UserCode)="webusr")))

hth
Alan :)


0
 
DKJensenAuthor Commented:
I suspected it was the nested query that was causing this, so I've redesigned the approach to avoid this altogether - this was part of a series of queries ('squeries,' I guess), so now I've got them creating temp tables and using a second query to gather the results from each of the tables. Works fine.

I did try removing the Alias before saving, but that odd error kept coming up - it infers the entire contents of the SQL as the name of the query, then declares the name invalid. That's why it was a big deal - it just stopped the query cold. I keep wondering if the FoxPro interface had anything to do with this.

As to the square brackets, Access did that - they were round when I placed them. It also added a period after the bracket, just before the Alias statement.

Oh well - I guess with as many people as use Access, we're going to run into oddnesses occasionally. Wierd happens.

Thanks, everyone.
Deb
0
 
Alan WarrenCommented:
Stranger than strange? hmm.. Glad you got it sorted Deb!

Thanks for the feedback.

Alan :)
0
 
walterecookCommented:
Me too Deb.  
In the interest of keeping our database clean, could I ask you to either award Alan the points or request a refund in Community Support?

Thanks
Walt
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now