Change database queried in MS query from Excel

[timroop]

We have several excel spreadsheets that reference SQL databases via ODBC connections. I need to change the database that they are querying because we have moved data to an archive database.

Even though the ODBC link sets the default database to the new historical data the Query seems to specifically reference the live database.

Basically I need to change the database queried from one named LIVEDATA to new one named ARCHIVE0708.

Is there a way to change that reference outside of MS Query? Because we pass parameters, once the query is set it cannot be edited properly with MS Query and I do not look forward to recreating all of the spreadsheets.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[SkipVought]

Hi,

You can examine the Connection and CommandText properties of the AueryTable object...

With YourSheetObject.QueryTables(1)
  Debug.Print .Connection
  Debug.Print .CommandText
end with

You can assign each string to a variable and change the DB. Sometimes this also has to be changed in the CommandText.

Then assign the variable(s) to the property as required...

dim sConn as string, sSQL as string

sConn = "YourConnectStringFromTheImmediateWindow"

sSQL = "YourCommandTextFromTheImmediateWindow"

With YourSheetObject.QueryTables(1)
  .Connection = sConn
  .CommandText = sSQL
  .Refresh False   'background refresh
end with

Post back with your string(s) if you need help.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}