Modifying MS Query at run-time

I'm struggling with a problem that is driving me mad. I have found numerous
references to this problem, and tried the suggested solutions, but to no
avail. Help from the guru's needed.

I have an Excel sheet with an embedded MS Query. It works beautifully. I
then move this workbook to another drive. I have a piece of VB (checked 100
times for correct operation) that modifies the Connection and CommandText
strings. Below are the Connection & CommandText strings both before and
after the run-time mod.

After the mod, when I try to edit the query I get "Unrecognised database
format 'C:\z\x.xls'. I then get a file open dialog which allows me to select
the correct file (which is already default). When I select the file it says
"This query cannot be edited by the Query Wizard", but I just ignore it and
I can.

I'm at my wits end, and I've tried everything. Can anyone shed some light on
this ?

(All the x,y,z & K's are sanitising, so I hope I haven't ruined the syntax)


Before

DSN=Excel
Files;DBQ=G:\y\x.xls;DefaultDir=G:\y\Resources;DriverId=790;MaxBufferSize=2048;PageTimeout=5;

SELECT DISTINCT `'Project Resourcing$'`.Resource
FROM `G:\y\x`.`'K Projects$'` `'K Projects$'`, `G:\y\x`.`'Project
Resourcing$'` `'Project Resourcing$'`
WHERE `'Project Resourcing$'`.ProjectId = `'K Projects$'`.F3 AND ((`'Project
Resourcing$'`.Resource<>''))

After

DSN=Excel
Files;DBQ=C:\z\x.xls;DefaultDir=C:\z;DriverId=790;MaxBufferSize=2048;PageTimeout=5;

SELECT DISTINCT `'Project Resourcing$'`.Resource
FROM `C:\z\x`.`'K Projects$'` `'K Projects$'`, `C:\z\x`.`'Project
Resourcing$'` `'Project Resourcing$'`
WHERE `'Project Resourcing$'`.ProjectId = `'K Projects$'`.F3 AND ((`'Project
Resourcing$'`.Resource<>''))

Nothing obviously amiss.

You could simplify the syntax of the query to

SELECT DISTINCT R.Resource
FROM [K Projects$] P, [Project Resourcing$] R
WHERE R.ProjectId = P.F3 AND R.Resource<>''

...but it may not help.

Bill Manville
MVP - Microsoft Excel, Oxford, England