Solved

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

Posted on 2003-12-04
8
1,377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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