Bug occurring when executing saved sql queries which had been executed before they have been saved?

Question

When I write new queries in Access I often run these queries before I save them to figure out whether my queries do actually work. Before saving a query access gives them standard names like, e.g., "query1". If "query1" works as expected, I save this query, e.g., as "myquery1". However, if I afterwards execute "myquery1" Access asks me to enter missing parameters such as "query1.expr1001". Of course, "query1" doesn't exists any more (this was the temporary name given by Access before saving). I could also copy and save the same sql statement from myquery1/query1 to a new query which works perfectly - provided that I have saved the query before I execute it the first time.

For me this looks like an extremely annoying bug which occurs because Access makes a mess with temporarily saved or renamed queries. Did someone else encounter the same behavior? Is there an explanation (why this is not a bug)?

By the way, I encountered the same issue in Access 2007/2010/2013. Sometimes I wonder whether MS actually improves its software from one version to the next (e.g. why does the Access query editor not remember text formatting (line breaks), why does a short cut like ctrl+a not work in this text field? These are just two examples of two extremely small and easy UI improvements which would help me to deal with complex queries)...

Answer 1

I think you misunderstand the OP's point, which is that if they open the query design window in SQL view (and possibly in design view, they don't say) it is captioned by default Abfrage6 (Abfrage is German for 'query').  If they then enter an SQL statement and opens it as a datasheet it's fine.  If they then go back into SQL view and enter a different SQL statement and save it under a name of their choice, when they subsequently try to open the now saved query, they are prompted for a value of a parameter with a name which includes a reference to Abfrage6.

I tried to reproduce this behaviour at the time of the original post without success, and cannot do so now.  The behaviour is inexplicable to me.  There is no mention of Abfrage6 in the SQL statement, and no attempt is being made to reference a query of that name anywhere else as far as I can see on the evidence available.

I did originally raise the question of whether a computed column Expr1001 had been created at some time, but there is no column of that name referenced in the SQL statement.  The SQL statement is clearly fine per se as it opens successfully as Abfrage7.  In view of my failure to reproduce the behaviour described, all I can think of is that this is behaviour experienced only in the German language version of Access.  If so, it's justifiable to regard it as a bug.

UPDDATE:  A thought occurred to me, so I tested it.  I created a simple query SELECT * FROM Contacts; and opened it as a datasheet. I then ordered the query by the LastName column, not by creating an ORDER BY clause, but by right clicking the column in datasheet view and selecting the ascending order option (A-Z).  This actually creates an OrderBy property for the query under the skin.  I then reverted to SQL view and entered an SQL statement SELECT * FROM AgeGroups;  Right enough when I tried to open this as a datasheet I was prompted for Query1.LastName, i.e. I was able to reproduce the behaviour experienced by the OP.  If that's not a bug, then I don't know what is!

I've always felt that a querydef object having an OrderBy property was one of Microsoft's more stupid endeavours as the way to order a query's result set is by an ORDER BY clause.  Now I'm more convinced than ever.

Answer 2

I'm afraid that I cannot reproduce the behaviour you are experiencing, and without seeing an SQL statement it's difficult to comment further.

Answer 3

I get this unspecified parameter issues when I do the following:

Go to "Create", and create a new query by clicking on "Query Design". Change to "SQL view" and write a query. Now, to test whether your query works you can simply click on datasheet view. Yes, you are right, Access will give it a, name e.g. Query1 (and save it temporarily, that is, it won't appear in your list of queries and when you close the query, Access will ask you whether you want to save the query or not).

If you now close this query and save it you will propably not keep the proposed name Query1 but will give it a different name, say MyQuery. Now when I try to run MyQuery I frequently encounter that Access asks me to specify a parameter like "query1.expr1001". However, Query1 doesn't and shouldn't exist anymore. Moreover, I have to mention that there is NO REFERENCE to a query1 in my query. My question is, why does Access ask my for a parameter like "query1.expr1001" if there is no such parameter in my query?

Unfortuanetly, I can not give you an example for a query where this occurrs because it doesn't happen everytime and so far I couldn't figure out whether it happens when a query has a certain structure or includes certain statements.

This kind of issues doesn't occur if I write the same query in SQL view and save it before I execute it the first time.

Answer 4

I've never experienced that behaviour.  The only thing I can think of is that you might be returning a computed column without having given it a column heading, and then referencing that column.  Computed columns should always be named, e.g. SELECT UnitPrice*Quantity*(VatRate+1) AS GrossPrice.

Answer 5

I'm a bit lost with this.  It doesn't sound like a bug.

You can't use a query that has not been saved.  When you create a query Access will give it a name and to use it you have to save it - you can choose to use the name access gave it or something else.

If you use this query in another query and change the name of the 1st you need need to close the 2nd before it will reconise the name change, obviously.