Solved

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

Posted on 2003-12-04
8
1,321 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
Comment Utility
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
Comment Utility

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
Comment Utility
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
 

Author Comment

by:DKJensen
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
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
Comment Utility
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
Comment Utility
Stranger than strange? hmm.. Glad you got it sorted Deb!

Thanks for the feedback.

Alan :)
0
 
LVL 17

Expert Comment

by:walterecook
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now