Requery issue when moving master file to other PC

[Mark Ivey]

I am having an issue with a requery from my master file when I try to use it
on other PCs at my workplace.

I went into the Edit Query mode and attempted to change the path for my data
file, but I am getting the following message [Microsoft][ODBC Excel Driver]
and then the file path is an invalid path.

Has anyone experienced an issue like this before?

Can anyone offer a suggestion?

Mark Ivey

[Bill Manville]

Sorry no-one got back to you sooner.

MSQuery has an annoying habit of including the path to the datasource
in the SQL of the query. Like
SELECT MyField FROM `C:\MyDir\MySubDir\MyFile.mdb`.MyTable MyTable
when
SELECT MyField FROM MyTable
would suffice, since the Connection parameter defines the location of
the datasource.

So you have to persuade it to change the SQL as well as the Connection
parameter of the QueryTable.

There are a number of ways you could go about programatically changing
the SQL and Connection properties to address the file correctly.
My preference is to create the query, put the cursor in the result area
and in the VB editor immediate window do
?ActiveCell.QueryTable.SQL
and
?ActiveCell.QueryTable.Connection

Then I write code that sets the SQL and Connection correctly to where
the source file will be found (often relative to ThisWorkbook.Path)

Bill Manville
MVP - Microsoft Excel, Oxford, England

[Mark Ivey]

Many thanks for the response and information...

It may be a day or two before I can test this information, but I will let
you know the results either way...

Mark Ivey

"Bill Manville" <Bill-M...@msn.com> wrote in message
news:VA.0000149...@msn.com...