QuerySurge™ Setup for Microsoft Access™
Note: Connection to Access via Java's builtin ODBC/JDBC bridge feature (on which this connection depends) is being deprecated starting with the QuerySurge 6.4 release, since Java has removed the bridge feature. While QuerySurge 6.4 is backwards-compatible for your existing Access queries, you should start planning to move your existing queries to another driver (we suggest the open-source UCanAccess driver). The current date for sunset of backwards-compatibility is October 31, 2019. For more information, see our FAQ.
Note: The option to create new Excel Connections via Java's builtin ODBC/JDBC is deprecated in QuerySurge version 9.0+. Existing Connections should still run. If you haven't already, you should convert all Excel Connections to use the QuerySurge Excel JDBC Driver.For more information, see our FAQ.
- The user login under which the QuerySurge Agent is running must have access to the directory path in which your Access files are located, and minimally must have Read permissions for the directory. This is especially important to verify if the directory is a network share directory, however it may be important even for local directories.
If you need to change the user login under which the QuerySurge Agent is running, see: Setting an Agent to Run Under a Specific Login
- The Access ODBC driver must be installed on each QuerySurge Agent box, because the Agent uses a JDBC-ODBC bridge to connect to the files through this driver. This is usually installed with a Microsoft Office installation. If it is not, an installer may be downloaded from Microsoft for the Access ODBC driver. See below for download links.
Important Note: If you have 32-bit Access or the 32-bit Access ODBC driver on an Agent box, you should install and use the 32-bit QuerySurge Agent with it. If you have 64-bit Access or the 64-bit Access ODBC driver on an Agent box, you should install and use the 64-bit QuerySurge Agent with it.
Download Links for Microsoft Office ODBC drivers
- Access 2003: Microsoft ended support for Office 2003 on April 8, 2014. Later drivers may support older Access file formats, so if you do not have a 2003 driver installed, a later driver may work.
- Access 2007: http://www.microsoft.com/en-us/download/details.aspx?id=23734
- Access 2010: http://www.microsoft.com/en-us/download/details.aspx?id=13255
Note: The 2010 package, called "Microsoft Access Database Engine 2010 Redistributable," contains the drivers for both Access and Excel. (See the link for Details.)
Setup in QuerySurge
- Choose the ‘Add Connection’ option from the Administration menu to open the Connection Wizard.
- In the Connection Wizard, enter a Connection Name and Select the 'Access' from the Data Source list.
- Choose the Connection Type based on whether your Access (or Access ODBC driver) is Access 2003 or earlier, or 2007 or later.
- For the path to the file, you must provide a full path including the Access file name with the appropriate extension (e.g. "C:\myfolder\myaccess.accdb" or "C:\myfolder\myaccess.mdb"). The directory may be a network share directory (either mapped or UNC paths work). You can input credentials if needed.
- Click Next. Review your settings. You can click the Test Connection button to make sure the Agent can 'see' the Access directory.
- Click the Save button. Your Access Connection is completed.