Why is my Excel query not working with ODBC?
Cause: Excel uses an add-in Xlquery.xla, which uses dynamic data exchange (DDE) with MS Query to get the external data. If the downloaded data is visible in MS Query, the download has been successful. This ends the involvement of the Client Access ODBC driver in the download.
Is there an ODBC add-in for MS query?
One involves using Data Access Objects (DAO) (Excel 7.0 only), and the other involves using an ODBC add-in called Xlodbc.xla. These options use ODBC directly rather than relying on the DDE link with MS Query. These options are also discussed in Microsoft technical support documents Q141227 and Q124218, which are available on the Microsoft web site.
When to enter parameters in ODBC Power Query?
Although you can define parameters with a fixed number of values (that is, a dropdown list in the UI), parameters are entered before the user is authenticated. Any values that can be discovered programmatically after the user is authenticated (such as catalog or database name) should be selectable through the Navigator.
How does the DataSource function in ODBC work?
The Odbc.DataSource function takes two parameters—a connectionString for your driver, and an options record that lets you override various driver behaviors. Through the options record you can override capabilities and other information reported by the driver, control the navigator behavior, and affect the SQL queries generated by the M engine.
How to optimize access when using ODBC data sources?
On a form, each list box, combo box, subform, and control that contains a total requires a separate query. Against local data, performance may be adequate. Against remote data, however, long delays may occur when you open a form because each query must be sent to the server and a response must be returned before the form can be opened.
How does Microsoft Jet database engine work with ODBC?
When you attach a remote table, the Microsoft Jet database engine queries the ODBC driver for its capabilities. If the required functionality is supported by the driver and the server, the Microsoft Jet database engine sends the operation to the server for processing. If not, the Microsoft Jet database engine performs the operation locally.