Aqua Data Studio (ADS) supports connecting to Microsoft Access Databases via Generic ODBC. The Mac operating system does not allow Aqua Data Studio to connect to Microsoft Access Databases with Generic ODBC.

Aqua Data Studio version 17 and higher use Java 1.8. Oracle has removed the JDBC-ODBC bridge driver in Java 1.8. The driver is not bundled with Aqua Data Studio which could cause a missing driver error. Aqua Data Studio can run using Java 1.8 and still use the JDBC-ODBC bridge from Java 1.7 if Java 1.7 is added to the installation. See How to Install the Java JDBC-ODBC Driver for installation instructions.  

Create an ODBC Data Source from your Microsoft Access file

  1. Within Windows, go to Start > Control Panel > Administrative Tools > Data Sources (ODBC) and right-click on Data Sources to Run as Administrator
  2. Click the System DSN tab if you want any other user of that computer to be able to access the ODBC data source.
    Click the User DSN tab if you want only your username on that computer to be able to access the ODBC data source.
  3. Click the Add button within the DSN tab.
  4. Within the list of available drivers, choose "Microsoft Access Driver (*.mdb, *.accdb)" and click Finish
  5. Within the ODBC Microsoft Access Setup dialog, enter a Data Source Name, enter a description and click the Select Database... button
  6. Browse to the Access database file you wish to use and click OK

  7. Within the DSN tab, you should now see the Data Source Name you entered above in step 5. Click OK
  8. Now, within Aqua Data Studio, continue with the server registration process

Register a connection for your ODBC Data Source in Aqua Data Studio

For Microsoft Access, the entire database is stored as a single file on a host machine either as a .mdb file or .accdb file.

Add the ODBC
data source		Create Server GroupRegister a Microsoft
Access Database		Enter Properties

Generic ODBC
Properties MS Access		Microsoft Access
Schema		Query in Microsoft Access

 To connect to a Microsoft Access Database using Aqua Data Studio Generic ODBC:

  • Make sure you have installed Microsoft Access and have the ODBC driver available.
  • Create an ODBC datasource in the Windows > Control Panel > Administrative Tools > Data Sources (ODBC) for the Access database.
  • Create a new server Group and name it as MS Access ODBC
  • Right-click the server group and click the Item which displays Register Server
  • Provide a new name for the Microsoft Access Database
  • In the General tab, pick Generic - ODBC
  • In the Location Datasource field, browse to locate the ODBC datasource for Microsoft Access you created.

The Microsoft Access Database server is registered successfully in Aqua Data Studio. You can now connect to the newly registered Server and perform Data Manipulations

Querying MS Access table names that contain spaces in a folder containing multiple database files

When executing queries for Microsoft Access Database tables that contain spaces in their names in the Query Analyzer and there are multiple Access database files in the same folder, your select statements require the following format:
select * from "full path to access file in quotes without file extension".[table name with spaces]

Example

select * from "C:\ms_access_files\DBSAMPLE".[Airplane - Seat Locations]
GO

Microsoft's ODBC Driver

You can get more information about Microsoft ODBC connections here

Vendor: Microsoft

  • No labels