"Enter parameter value" error when exporting to Excel
Access user
I have recently come across a problem where certain queries run fine
in access, but prompt me with an 'Enter parameter value' message box
when i try to export the query to excel (exporting to a text file
works fine). A warning, this is a long post.
Some background:
OS: Vista Business
Program: Acess 2007
I tried googling for similar problems and I came across this:
Which describes my problem exactly, but does not go into much detail
and is somewhat lacking in proposed solutions (non of the ideas
worked).
I also looked at this article:
http://support.microsoft.com/kb/303134/en-us?spid=2509&sid=98
But it was not helpful. There are no parameters in this file (i can
recreate a blank database and reproduce this) and the parameter that
is asked for in the dialog box is a truncated column name from the
query.
The problem appears to be related to the length of saved query's name
(I know this sounds incredible).
This problem occurs when I use a UNION query that references other
saved queries instead of raw SQL. For example (this is only an
example, these sample queries are not the ones causing the error as I
will explain later), I let's say I have "Query_1" which is simply
"SELECT * FROM TABLE_1;" and "Query_2" which is "SELECT * FROM
TABLE_2;".
This UNION query (let's name it "UNION_1") is the following:
"SELECT QUERY_1.*
FROM QUERY_1
UNION
SELECT QUERY_2.*
FROM QUERY_2;"
Since it is possible to select "queries' from the 'Show table' dialog
box in 'Query Design', I assume that this is an acceptable behavior.
Now, in my problem, the "UNION_1" query works fine when it is run. I
can see the expected results in the table view. however, exporting to
excel fails with the parameter prompt. If I replace the named queries
with the actual SQL, like the following:
"SELECT TABLE_1.* FROM TABLE_1
UNION
SELECT TABLE_2.* FROM TABLE_2;"
The query also runs fine AND it exports without the parameter prompt.
Now, if you test this you will probably be able to export the results
successfuly. For me, the problem is reproducible for longer query
names (with each table, column, and query name within excel's length
limit). Chaning 'QUERY_1' to something like
'QUERY_1_WITH_A_LONG_NAME' (and updating 'UNION_1' accordingly will
cause it to fail repeatedly only on the export step. Changing the
saved query's name back to something shorter will allow it to work.
I have uploaded a sample database which can be downloaded at the
following address:
http://www.mediafire.com/?8wdaw0ye0xl
if you are not comfortable downloading a random file from a random
person (and I can't say I blame you), I will write out the necessary
steps required to recreate this database.
1. Create a new table with the name "THIS_IS_A_LONG_TABLE_NAME"
2. Add two columns, 'ID' the autoincrement key, and 'some_info' a text
column
3. Add a second table "THIS_IS_ALSO_A_LONG_TABLE_NAME"
4. Add two columns to the second table:
'long_column_name_fails' (autonumber), 'other_information' (text)
5. Create a query which joins the two tables on their autonumbers, my
SQL is as follows:
SELECT THIS_IS_ALSO_A_LONG_TABLE_NAME.*
FROM THIS_IS_A_LONG_TABLE_NAME INNER JOIN
THIS_IS_ALSO_A_LONG_TABLE_NAME ON THIS_IS_A_LONG_TABLE_NAME.ID =
THIS_IS_ALSO_A_LONG_TABLE_NAME.long_column_name_fails;
6. Save this query as 'short_name'
7. Copy the query and rename the copy as 'this_is_a_long_query_name'
8. Create a UNION query unions 'short_name' with 'short_name' (I know
this is a useless UNION, but it showcases the problem simply). My SQL
is as follows:
SELECT short_name.*
FROM short_name
UNION SELECT short_name.*
FROM short_name;
9. Save the union query (I called it 'union_short_query(works)')
10. Create a new UNION query which uses only
'this_is_a_long_query_name' instead. The SQL is as follows:
SELECT this_is_a_long_query_name.*
FROM this_is_a_long_query_name
UNION SELECT this_is_a_long_query_name.*
FROM this_is_a_long_query_name;
11. Save the union query (I named it 'union_long_query(fails)')
12. Run both queries (you can add data to the respective tables so you
can see that they actually return the expected results). Both queries
should run fine.
13. Try exporting 'union_short_query(works)' to excel, it should work
fine.
14. Try exporting 'union_long_query(fails)' to excel. You should be
prompted for a parameter (it doesn't matter if you run the query then
try to export it, or if you try to export it fby right clicking the
name and selecting 'export' before running it).
15. If you create a new query 'union with raw sql (works)' and use the
raw SQL as follows:
SELECT THIS_IS_ALSO_A_LONG_TABLE_NAME.*
FROM THIS_IS_A_LONG_TABLE_NAME INNER JOIN
THIS_IS_ALSO_A_LONG_TABLE_NAME ON THIS_IS_A_LONG_TABLE_NAME.ID =
THIS_IS_ALSO_A_LONG_TABLE_NAME.long_column_name_fails
UNION SELECT THIS_IS_ALSO_A_LONG_TABLE_NAME.*
FROM THIS_IS_A_LONG_TABLE_NAME INNER JOIN
THIS_IS_ALSO_A_LONG_TABLE_NAME ON THIS_IS_A_LONG_TABLE_NAME.ID =
THIS_IS_ALSO_A_LONG_TABLE_NAME.long_column_name_fails;
then exporting works fine.
16. If you rename the query 'this_is_a_long_query_name' to something
shorter ('abcd') and then update 'union_long_query(fails)'
accordingly, the query will now export successfully.
The problem seems to be related to the lengths of the query name, the
table name, and the column name. My only theory is that by using the
saved query 'this_is_a_long_query_name' in the UNION query, it is
somehow running into the table name/column name length limits.
However, I don't understand how or why it ONLY occurs when exporting
to excel (it might happen with other exports too, I only tried excel
and .txt. Excel fails, .txt works).
if anyone could offer any insight, I would appreciate it.
Thank you.