Solved

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

Posted on 2003-12-04
8
1,363 Views
Last Modified: 2008-02-01
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
Comment
Question by:DKJensen
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 9878355
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
 

Author Comment

by:DKJensen
ID: 9879055

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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9879901
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DKJensen
ID: 9882230
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9885450
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
 

Author Comment

by:DKJensen
ID: 9889820
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 9889845
Stranger than strange? hmm.. Glad you got it sorted Deb!

Thanks for the feedback.

Alan :)
0
 
LVL 17

Expert Comment

by:walterecook
ID: 9922317
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question