microsoft query

Question

We'll be upgrading to a new ODBC driver.  Will I be able to refresh Excel spreadsheets or edit the query built with Microsoft Query using the current ODBC driver after the new ODBC driver is put in place?  If not, what do I need to do now?

Thank you,

Answer 1

You haven't really given me enough to go on.

I have no idea what DSSP24 is, what the ODBC connection string is like for your old driver or for your new driver.  If you can post an old connection string and a corresponding new connection string I might be able to help with a macro to modify the connection strings in a selected set of workbooks.

Answer 2

Bill,

I'm not familiar with VB but I saved the spreadsheet as a 1997-2003 type and opened it up in notepad.  The connection string was for DSSP24 which was the one I had removed so I guess your theory that it is just removed for future queries is correct.  Nevertheless the data in the two directories are different and it needs to display and refresh the data in the new one.  How do I do that?

Thank you,

Answer 3

The details of the connection are held within the QueryTable.

Removing a connection (I think) just removes it from the list of available connections for future queries.

I know nothing about DSSP24, but I would find out what the connection string is by putting the cursor in the querytable and Alt+F11 to the VB editor, Ctrl+G to the immediate window and

?ActiveCell.QueryTable.Connection

If you do the same for a connection via the old driver and via the new driver you will be able to compare and determine if changes will be needed.

Answer 4

Bill,

Thank you for your response.  As an experiment I built a query using the testing database connection for DSSP24 and returned the results to Excel.  I then removed this connection.  The query did not refresh as expected because the data source was not found.  I then got the Select Data Source Window.  I chose an existing one and entered the password when requested but the data that appears continues to be from the DSSP24 connection which I had disconnected.  I updated the data in that testing database and when I clicked refresh the revised data was reflected.  How is that possible if that database connection was removed?

Sincerely,

Answer 5

That is rather difficult to answer without knowing what datasource the driver is for.

If it is just a new version of an existing driver the chances are that you need do nothing.

If you have created an ODBC datasource (DSN) for use in your queries to a particular database then you may only need to change the DSN.

Otherwise you may have to run a macro to identify all workbooks containing queries that need to be changed, and to change the Connection properties of the QueryTables and PivotCaches within those workbooks.

I would suggest doing a trial on one machine - install the new driver and remove the old one, and then test an example workbook and see what happens.