Data Access Object (DAO) and Visual Basic.NET to create an Access database interrogation tool

Problem

The institution I work with receives data through a variety of vehicles and since they conduct different surveys and investigations the data does not always fit into a single model. Once the data is received, I need to get the data loaded into SQL Server. One of the common vehicles used to deliver the data is Microsoft Access 2007. I spend a significant amount of time looking through these databases to figure out how the data is arranged and would like to find a way of interrogating the database in a more efficient manner.

Solution

Evaluating an Access database manually can be quite time consuming, particularly when there is a large number of tables and queries. Luckily you can create a tool that will assist with this by using either Visual Studio or Visual Studio Express, some .NET Framework references, and COM objects. The examples used here will be using Visual Basic, but the same can be accomplished using C#.

The Microsoft Office 2007 Access Database Engine Object

There are a few COM objects that interact with Microsoft Access, but the relevant one that can dive into the innerds of Access is the Microsoft Office 2007 Access Database Engine Object. This object took the place of DAO libraries in Office 2007 and is specific to Access 2007; if you want to obtain information on previous versions of Access then you should use one of the previous DAO libraries (like the Microsoft DAO 3.6 Object Library). My assumption when creating the evaluation application was that I want to view the properties and structure of the database, tables, queries, and relationships within Access 2007 databases. This database engine can be installed as a redistributable with applications that interact with other Office products. It also provides the ability to import Excel 2007 spreadsheets using SSIS and the Import/Export Wizard as mentioned in a previous tip.

Access houses information on its tables in a TableDefs object, which houses individual TableDef objects for each table. The TableDef has a number of methods and properties available, but of most importance for this project is:

Information on queries in Access databases are housed in the QueryDefs object. Within QueryDefs is a collection of QueryDef objects similar to the TableDef objects in the TableDefs collection. The properties I will use to find information on the queries are:

Relationships between tables in Access are stored in a Relations object. Below are the areas of the Relations object I will use to obtain information on relationships:

Creating the Evaluation Tool

The first step in setting up the project is to create a project using the Visual Studio product of your choice. Once Visual Studio is open click File and choose New Project:

Chances are that you will want to do more with the data than just view it or present it in a logical and user-friendly format. In this project I chose a Windows form application:

The next step is to add the necessary references to the project. That is accomplished by right-clicking the Solution in the Solution Explorer pane and choosing Add Reference:

A set of references are automatically added to the project:

There is another reference that should be added to the project. If using Microsoft Access 2007 then add C:\Program Files\Microsoft Office\Office12\ACEDAO.DLL by clicking the Browse tab, navigating to the appropriate folder, and then double-clicking the file. If using Access 2003 add the Microsoft Data Access Objects (DAO) 3.6 Object Library using the COM tab:

The Code to Access the Database

Once the reference is added you can access it using the namespace or you can use an alias to import the namespace. For example, if you want to reference the Microsoft.Office.Interop.Access.Dao namespace as MX then at the very top of the page type Imports MX = Microsoft.Office.Interop.Access.Dao. I decided to have a series of TreeViews to represent the tables, queries, and relationships. Once you have the form available you can add an OnClick event on a button. In the code I declare a new DBEngine and use it to open the Access database. In this project I want to find out everything I can about the database, so we start by gathering the database properties into a StringBuilder and displaying it in a textbox. The next step is that I loop through the items in each collection, obtaining the desired information.

In the Data Viewer section I had to be a little more creative. I built a data table using the fields represented and used the New Row function to populate the data table. Once the table was fully populated I set the datasource of a DataGridView:

Next Steps

• The example provided is a general example to get you started with building a tool to evaluate Access databases, so the sky’s the limit
• Read about how to add a reference in Visual Studio
• Review the DAO Reference on MSDN
• Download the 2007 Office System Driver: Data Connectivity Components
• Review information on Office Development in Visual Studio
• Stay tuned for more tips on http://www.MSSQLTips.com!

Tim Cullen

Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.

• MSSQLTips Awards: Trendsetter (25+ tips)