How to Connect to Microsoft Access Database

Multiple applications can connect to Access databases using ODBC collections.

Microsoft Access databases store data in .mdb files. Connections to the database can be made using applications other then Microsoft Access, such as .NET applications through Visual Studio 2008, or Microsoft SQL Server. The connection settings demonstrated through these two applications can apply to nearly any application with the ability to configure an ODBC connection.

Instructions

Using Visual Studio

1. Open Visual Studio. Click the "Add New Data Source" option in the "Data" menu.

2. Select "Database" and choose "New Connection" to open the new connection dialogue.

3. Select "Microsoft Access Database File" in the "Data Source" dialogue.

4. Enter the file name of the access file along with log in information. When complete, click "OK" followed by "Next".

5. Select the objects to use in the list of available objects and click "Finish".

Using Linked Servers in Microsoft SQL

1. Connect to the destination SQL database using SQL Server Management Studio.

2. Locate the "External Data Tab" at the top of the screen. Click "ODBC Database" in the Import and Link section.

3. Choose "Link To The Data Source By Creating A Linked Table" and click "OK". A linked table allows changes to be made in the underlying data source from either Access or Microsoft SQL Server.

4. Select the existing DSN file to use for the connection, or create a new one if necessary. Click "OK" when done.

5. Select all tables and views you want to link to and click "OK".

References

• Microsoft: How to: Connect to Data in an Access Database
• Microsoft: Import or link Access to SQL Server Data