Scenario:
Using excel, getting external data from a local database via an ODBC data source.
Run a query from excel using the ODBC. Change the mdb that the ODBC points to.
Why, when refreshing the query, does it not pull data from the new database? The query continues to look at the database the ODBC was pointing to when the query was built.
This is the SQL statement from MS Query:
SELECT AFM1Usage.AtTime, AFM1Usage.AFM1Capacity, AFM1Usage.AFM1Usage
FROM `C:\Test\Testv2`.AFM1Usage AFM1Usage
As can be seen from this, although the query was built using an ODBC data source, the menu path of the database is stored in the SQL statement.
What can I replace the `c:\Test\Testv2` with in order to instead look at the an ODBC named “RPS” rather than a menu path?
Is this even possible?
I want to have a standard reporting tool that uses queries to connect to an ODBC rather than a menu path so I don’t have to rebuild the queries whenever a new database (with the same structure) is used.
Any help would be appreciated.