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
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
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" wrote in message news:VA.00001497.13081b27@msn.com... > 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 >
