Update querytable connections and refresh data
 

I've cross posted this to several of the Excel and Office automation groups
in hopes of getting a quick response.

I've got an Excel spreadsheet that uses info from an Acccess database to
generate a series of charts. In the Excel spreadsheet, are a number of
querytables that are the data source for the charts.

Unfortunately, I'm having to carry these files around with me, and move them
to new locations on the network (or my USB drive) on a regular basis (don't
ask why). I'm trying to write a procedure to update the querytable
connection strings to point to the copy of the Access database which resides
in the same folder as the Excel workbook. I get the old path from the
connection, then determine the newpath based on the workbooks fullname, then
replace the oldpath with the newpath in the connection string. Most of that
process appears to be working.

qt.Connection = Replace( qt.connection, strOldPath, strNewPath)

But then I want to refresh the connection and refresh the data. I'm
currently using the following line, which is generating an error message.

qt.Refresh False

Run-time error '1004' General ODBC Error

If I don't put this line in the procedure, I get the proper connection
string when I type debug.print the connection in the Immediate window, but
when I put my cursor in one of the cells of the query table results, and
select 'Refresh Data' I get the following message:

[microsoft][odbc microsoft access driver] 'P:\...\...\filename.mdb' is not a
valid path. Make sure that the path name is spelled correctly and that you
are connected to the server on which the file resides.

and the filename that is listed in the message is the filename that was in
the old connection string, not the new one.

Ideas would be greatly appreciated.