General discussion

Locked

Refreshing ODBC Data Queries in Excel 97

By mark.hodges ·
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.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Refreshing ODBC Data Queries in Excel 97

by kees.valkenswaard In reply to Refreshing ODBC Data Quer ...

MS Query is something I don?t use, so I do not know all the details of your problem. I use a lot of mail merge in Word where I had the same problem. I solved it with a macro in which I can change the path and run the merge.
Make certain that you split the SQL statement correctly. Often you need both single ? and double ? quotes, together with & to build the SQL string.
Hope this helps a little bit.
Good luck

Collapse -

Refreshing ODBC Data Queries in Excel 97

by kees.valkenswaard In reply to Refreshing ODBC Data Quer ...

The lack of standardization in industry character codes converted the ? and ? into ?

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums