Repost: Update querytable connections and refresh data

[Dale Fye]

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. 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.

 

[Guest]

Disregard. Got it figured out.

 

[DennisNY]

I am experiencing a similar problem with the same error message. How can I
change the drive letter of the query or OLAP cube? It seems that my former
driver letter is embedded in the cube and/or query.

Please reply with any suggestions.

Disregard. Got it figured out.

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

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. 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.