I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer.

I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query.

But I'm just a part time programmer and eventually this little system has to be used by actual users. So after thorough testing, I move these two little spreadsheets over to a users machine...

Anyway all works fine until I move fred and bob to a new home.

If I move fred and bob to different directory on the same computer or to say a users computer where I have to change the directory name, the query stops working.

ODBC returns a error messages that "c:\123directory\bob.xls doesn't exist".

So, I edited the macro and changed the path names (in all 3 places) to reflect the new directory path where fred and bob live. I still get the ODBC error message.

Why is the query still looking for c:\123direcotry\bob.xls when I clearly told the macro that bob now lives at c:\abcdirecotry\bob.xls ?????

I then edited the query fetch_from_bob by going to the external data area and activating a cell, then doing Data/Edit query. I then changed the path name in the from portion of the sql staement. The query editor will not let me close/ save the file. It gives an error message like "can't use parameters in a query that can't be displayed graphically"

??Huh ?? what is that ? I'm postive that the sql syntax i correct, as I onle changed the path portion from "c:\123director\bob.xls" to "c:\abcdirectory\bob.xls"

With this bizarre behavior, I decided to different route. I edit the file fetch_from_bob.dqy with notepad. I make real sure to the edit the query that lives in the same new directory "c:\abcdirectory" with bob and fred. Again I change the sql from to reflect the new desired path and close/save the file.

When I go back into the spreadsheet and execute the query/refresh data, I get the same old ODBC error "c:\123directory\bob.xls doesn't exist".

Why is query still looking for "c:\123directory\bob.xls" when I've clearly just told it that bob now lives at c:\abcdirectory\bob.xls" ???

Anyway, I kept fooling around until I noticed that when you do File..Open while in the query editor that query editor is looking down a path called User(whoever you happen to be logged on as)\Application Data\Microsoft\Queries\fetch_from_bob.dqy

If you open this instance of fetch_from_bob.dqy, you find that query still has an sql from stagement that is looking down the old path "c:\123directory\bob.xls" .

How did this file get there ??? Is query building hidden files in the background ??

So, I go over to explorer and search high and low for USER\Application Data\Microsoft\Queries\*.*.

Explorer has never heard of any such file or path. Explorer has a USER with many subdirectories, but not one called Appication Data.

I've tried this on 3 computers logged in as mutiple different users. I get the same bogus result on all machines.

Clearly, Query is building some hidden program files in the background then using those files instead of the files I'm expecting it to use.

How do I find\change those hidden files ?

PS... All machines have some XP varient and I am logged on as a "user" on each machine not and "administrator"