Microsoft Access Generic ODBC (Revision #0)

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.

Contents

• Create an ODBC Data Source from your Access file
• Register a connection for your ODBC Data Source in Aqua Data Studio
• Querying MS Access table names that contain spaces in a folder containing multiple database files
• Microsoft's ODBC Driver

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 on the System DSN tab if you want any other user of that computer to be able to access the ODBC data source.
   Click on 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 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 Group	Register 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 on server group and click on the 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