External Query - HOW IS THIS POSSIBLE

[yorrick]

I have a Spreadsheet with External Data queries that link to an Access DB and pull data from an Access Query.
All Good.

However, if I want to change the location of the database it is not so simple. This is because I can find nowhere that tells me (even though I know) where the Spreadsheet looks for it's data.

There are no ODBCs set up for me to change. When I edit the query and view the SQL there is no information telling me of the databases location.

So, how on this pretty earth does it know to look on my D-Drive?

 

[xlbo]

Have a look at the connection property of the querytable object

Manually, this should be found on a .dqy or .oqy file (depends on the type of datasource)

Programatically, you can access it via:

msgbox Activesheet.querytables(1).connection

and then amend it by manipulating the string it returns

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question