1

I am working in an Access 2016 database with 17 tables and about 300MB of data. I have to create several queries and views on top of this. Due to the nature of the queries and my background with SQL, I have been writing the queries directly in the SQL-view instead of using the GUI.

Every once in a while, typically after saving a query, I suddenly start getting prompted for a parameter. However, there is nothing wrong with the SQL query. If I copy and paste the text into a new query, it works fine.

The parameters I'm getting prompted for don't even exist (they usually include "Query1" in the parameter name, plus maybe a name that I assigned to a column). Why is this happening and how can I prevent it? I've tried saving and refreshing the whole Access file, in case some data was outdated. It doesn't help.

EDIT: It happened again. This time I had just added a new column to a query (via the SQL editor). Suddenly started getting this popup:

translation: write in parameter value, Query1.Expression1001

Translation: Write in parameter value, Query1.Expression1001

  • Are you working on the live database? Are other users/services connecting to it while you make these changes? Could there be temporary tables being made in macros or VBscript? – Burgi May 10 '17 at 8:07
  • I am working directly in the .accdb file, no other users connecting to it. I do have a separate script which I use to update data in some of the tables; however I always close the whole file beforehand and reopen it. I suspect that Access does some kind of automatic formatting on the query when it is saved, but I have no idea how I can see that. As mentioned, I can copy the SQL text directly from a non-working query to a new, "clean" query and it will work perfectly. Once a query is "broken" it never works again, even if I save everything, close the file and reopen it. – schneiju May 10 '17 at 9:45
0

I know this was a long time ago, but I wanted to drop a quick message for anyone that might be having this same issue as I currently am. I actually managed to solve this issue - so here's what it was: (At least for me- and hopefully for you as well)

You have to go into the SQL view of your query and click on the 'property sheet' button on the menu ribbon. When that opens, look for anything in the property sheet that mentions 'Query1' - mine happened to be in the 'Order by' field telling my query to order by 'Query1.COMPANY" so every time I ran my query it was prompting me for that parameter.

Cleared that bugger out of there and my query is back to normal! Hope that helps :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.