Excel query of Access database - changed database filename, now error

[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

 

[Ardus Petus]

You have to update the database name in
ActiveSheet.QueryTables(1).Connection

HTH

 

[ucdcrush]

Thanks for the reply Ardus. How do I get to that? I looked in the VBA
explorer, couldn't find it there.