Hi
We have many spreadsheets that use Microsoft Query to connect to an instance of SQL Server 2005, using an ODBC System DSN. Part of the definition is to specify the schema to use (which MS Query still refers to as "Owner"). In Excel 2003 there was a tick-box to save the query defiition, and this worked fine. The query definition, including the Owner, was saved.
In Excel 2010 however, this tick box is not present, and although the SQL statement is saved, and the Owner is used when refreshing the query, we cnnot edit the query at all - as soon as you try to edit the query, the Owner dialog shows <All>, and selecting the correct Owner blanks out the the query definition.
Even stranger, if you simply step through the wizard, leaving the Owner set to <All>, the SQL Statement changes the FROM clause to a random alternative schema, i.e. if the query stated MyDatabase.MySchema.MyTable, stepping throgh the wizard changes this value to MyDatabase.RandomOtherSchema.MyTable
Has anyone else come across this with Excel 2010? It doesn't seem to affect Excel 2003 at all.
Any help will be very much appreciated.
Screen grabs attached.
Bookmarks