Microsoft Access ODBC for Windows

This tutorial shows how to set up a data source and connect to a Microsoft Access^® database using the Database Explorer app or the command line. This tutorial uses the Microsoft Access Driver (*.mdb, *accdb) to connect to a sample Microsoft Access 2016 database.

Step 1. Set up the sample Access database.

You can access the sample database file, tutorial.accdb, in the folder returned by entering this code at the command line.

fullfile(matlabroot,'toolbox','database','dbdata')

Copy this database file into a folder where you have permission to write. Ensure that the database file is writable by verifying its properties:

Right-click the database file and select Properties.
On the General tab, if the Read-only option is selected, clear it.

Note

To write data to the sample database, ensure that you run MATLAB^® as an administrator.

Note

Depending on the Access^® version you are running, you might need to convert the database to that version. For example, beginning in Access 2007, the software includes the option to save as *.accdb. For details, consult your database administrator.

Step 2. Verify the driver installation.

The ODBC driver is typically preinstalled on your computer. For details about the driver installation or troubleshooting the installation, contact your database administrator or refer to your database documentation on ODBC drivers. For information about the Microsoft^® ODBC Data Source Administrator, see Driver Installation.

Note

Database Toolbox™ no longer supports connecting to a database using a 32-bit driver. Use the 64-bit version of Access. If you want to connect to a 32-bit Microsoft Access database, you can find information about downloading and installing Microsoft Access Database Engine 2016 on the Microsoft website.

Step 3. Set up the data source using the Database Explorer app.

Set up the sample Access database as the data source by using the Database Explorer app. You can locate the target database on a PC running the Windows^® operating system or on another system to which the PC is networked. These instructions use the Microsoft ODBC Data Source Administrator Version 10.0.16299.15 for the US English version of Microsoft Access 2016 for Windows systems.

The Database Explorer app accesses the Microsoft ODBC Data Source Administrator automatically when you configure an ODBC data source. Alternatively, you can access the Microsoft ODBC Data Source Administrator using the configureODBCDataSource function.

Close any open Access databases.
Open the Database Explorer app by clicking the Apps tab on the MATLAB Toolstrip. Then, on the right of the Apps section, click the Show more arrow to open the apps gallery. Under Database Connectivity and Reporting, click Database Explorer. Alternatively, enter databaseExplorer at the command line.
In the Data Source section, select Configure Data Source > Configure ODBC data source.
In the ODBC Data Source Administrator dialog box, you define the ODBC data source.
Tip
When setting up an ODBC data source, you can specify a user data source name (DSN) or a system DSN. A user DSN is specific to the person logged into a machine. Only this person sees the data sources that are defined on the user DSN tab. A system DSN is not specific to the person logged into a machine. Any person who logs into the machine can see the data sources that are defined on the system DSN tab. Your ability to set up a user DSN or system DSN depends on the database and ODBC driver you are using. For details, contact the database administrator or refer to the ODBC driver documentation.
On the User DSN tab, click Add. The Create New Data Source dialog box opens and displays a list of installed ODBC drivers.
Select Microsoft Access Driver (*.mdb, *.accdb) and click Finish.
In the ODBC Microsoft Access Setup dialog box for your driver, enter dbdemo as the data source name. Enter tutorial database as the description.
Click Select to open the Select Database dialog box, where you specify the database you want to use. For the dbdemo data source, select tutorial.accdb. If the database is on a system to which your PC is connected:
1. Click Network.
2. In the Map Network Drive dialog box, specify the folder containing the database you want to use. Ensure that you map to the folder and not the database file.
3. Click Finish.
Click OK to close the Select Database dialog box. In the ODBC Microsoft Access Setup dialog box, click OK. The ODBC Data Source Administrator dialog box displays the dbdemo data source and any additional data sources that you added on the User DSN tab. Click OK to close the dialog box.

After you complete the data source setup, connect to the Access database using the Database Explorer app or the command line with the native ODBC connection.

Step 4. Connect using the Database Explorer app or the command line.

Connect to Access Using Database Explorer App

On the Database Explorer tab, in the Connections section, click Connect and select the data source for the connection.
- If you stored your user credentials using the Set Credentials button, the Database Explorer app makes the connection and the Catalog and Schema dialog box opens.
- If you have not stored your user credentials, the connection dialog box opens. Enter your username and password, or leave these boxes blank if your database does not require them. Click Connect and the Catalog and Schema dialog box opens.
In the connection dialog box, leave the Username and Password boxes blank, and click Connect.
The app connects to the database and displays a list of its objects, such as tables, in the Data Browser pane. The data source tab named dbdemo appears to the right of the pane. The data source tab contains empty SQL Query and Data Preview panes.
$Database Browser shows the C:\tutorial.accdb catalog and its contents.$
Select tables in the Data Browser pane to query the database.
Close the data source tab to close the SQL query. In the Connections section, close the database connection by clicking Close Connection.
Note
If multiple connections are open, close the database connection of your choice by selecting the corresponding data source from the Close Connection list.

Connect to Access Using ODBC Driver and Command Line

Connect to the database with the ODBC data source name. For example, this code assumes that you are connecting to a data source named dbdemo with a blank user name and password.
```
conn = database('dbdemo','','');
```
Close the database connection.
```
close(conn)
```