changing query source

[ucdcrush]

I have an excel workbook where I've queried a table in access. When I
first setup the query, I did it through the get external data query
wizard, browsed, found my access database (called database_010306.mdb)
and selected my table. All was fine.

Now, I am renaming my temporary named database (database_010306.mdb) to
just database.mdb. So I've got to tell Excel the new filename.

I thought I had it when I went to "edit query" in Excel, then went to
"Microsoft Query", looked at the SQL code and changed the FROM
statement to point to my new filename. When I closed Microsoft Query,
it successfully updated the query with the correct data from the newly
renamed database.mdb.

but when I use the "refresh query" buttons on the external data
toolbar, it tells me it can't find database_010306.mdb - ODBC access
login error or something. yet, when I again look at the query in
microsoft query, it has the correct (new) filename in the FROM
statement, and again when I close Microsoft query, it refreshes as it
should have when I click on "refresh data".

Where else is this old filename stored, and can I simply edit it
without having to make a new query with the query wizard and point to
the new mdb file?

I hesitate to make all new queries since I have many sheets with many
queries and would like to see if there is a place I can just go and
change the filename, as updating in microsoft query did not seem to do
it.

Thanks

 

[Guest]

Try this:

Copy your database into the old location and rename the copy to it's
previous name.
When you refresh the query through Excel, it will find the database and
display the query. Now, you should be able to edit the SQL

When done....destroy the db file you no longer need.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[ucdcrush]

Hi Ron - I had tried that previously, and editing the SQL was no
problem. (In fact I could open the spreadsheet with no file at the
original location, it would just tell me it couldn't find it, then I'd
"cancel" instead of trying to locate it [locating it didnt solve
anythign either].)

The problem is Excel is somehow, somewhere, storing the old filename.
When I go into the SQL, I can edit the FROM statement, and when I save
the workbook and open again, the SQL is fine (points to the new
filename) but Excel still looks for the old filename.

In the meantime I just deleted all the queries and made new ones, and
it works. However there must be someplace Excel is storing the old
filename. Forwhatever reason, M$ has not allowed a user to get to it
easily.

Thanks for the reply.Dave