Query Field Rename Causes TransferSpreadsheet error!

[idbr]

Hi,

I have a data output process that is behaving a little strangely. It's just a simple TransferSpreadsheet (TS)operation, outputting query results to a file.

I renamed two of the fields in the query from Address3 & Address4 to Town & County respectively. Now on running the TS I get an 'Enter Paramter Value' inputbox asking for the values for Address3 & 4!

Running the query manually does not replicate the error, neither does running the TS from the immediate window.

Deleting the queries and re-creating them solves the problem.

I'm just wondering if this is an issue anyone else has come across? How can I avoid it as an issue in future?

I'm running A2K on Win2K.

Thanks, Iain

 

[WildHare]

Did you rename fields in the QUERY, or in the table they're coming from?

When I first started with Access, this little "quirk" caught me a couple of times, until I figured it out (I think...)

You know how, in a query design window, if you have a text string enclosed in brackets, in either the field name row or the criteria row, and it's not a valid table object, then that string becomes an "input box" parameter prompt? Well, that's what happened here. Somewhere between "Address3" and "Town", Access lost track of the fact that "Address3" is no longer a valid field name, and instead thought "[Address3]" was a parameter input prompt....

This happens often if one misspells a field name in a query...all of a sudden, you get a "Enter Parameter" input box with "Adress" as the text string..confusion reigns..until you realize that you misspelled "Address" in the query...

I'd surmise that somehow, your transfer spreadsheet guy was using a pre-compiled and stored querydef that still had the old "[Address3]" guy in it, rather than "[Town]", since you say that when you rebuilt the query it worked OK.

Just another reason to NEVER rename table fields...get 'em right the first time.....






--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[idbr]

Good point WildHare, getting things right first time doesn't half save a lot of problems!

I did indeed rename a table field, rather than a query field, although there were no bracketed expressions to throw the spanner in the works. The query ran fine from design view, running the TS direct from the immediate window also went fine.

Having dug about a bit more, it looks like a system table issue. The MsysQueries table holds a list of all the table fields required to run your query. These field descriptions don't seem to update when table field names are changed.

Somehow, Access seems to be able to 'see' that the table fields have changed name, yet VBA seems unable to and so expects a different field in the query. Still don't know why this wouldn't be an issue running from the immediate window though...

Odd I know, but running an update on the field names in the MsysQueries table stops the error, so I reckon this has to be the case!

Iain