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


By:   |   Updated: 2010-10-08   |   Comments (10)   |   Related: More > Application Development

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:

Property/Method Description
Name The name of the table. The TableDef collection of tables also includes system tables within Access, so may have to filter them out

Attributes Attributes of the table stored as bitwise operation

Fields The fields that reside within the table. This collection also has properties and methods that we will discuss soon

Properties A collection of table properties

RecordCount A count of rows in the table. If this table is a linked table then this value will be -1

ValidationRule Any restriction on the values entered (domain)

ValidationText The text to be displayed when the data entered does not follow the validation rule

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:

Property/Method Description
Name The name of the query

Type The type refers to whether it is a SELECT, APPEND (insert), UPDATE, or PASS-THROUGH. An integer is returned.

SQL The actual SQL statement of the query

Parameters A collection of parameters that the query will accept

Fields A collection of columns/fields returned by the query

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:

Property/Method Description
Name The name of the relationship

Table The table being referenced by the relationship-the parent

ForeignTable The child table in the relationship

Fields The columns that define the relationship

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:

Creating the Evaluation Tool

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:

you will want to do more with the data than just view it or present it in a logical and user-friendly format

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:

add the necessary references to the project

A set of references are automatically added to the project:

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:

using Microsoft Access 2007 then add C:\Program Files\Microsoft Office\Office12\ACEDAO.DLL

 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.

The Code to Access the Database

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:

build a data table using the fields represented and use the New Row function to populate the data table
Next Steps


Last Updated: 2010-10-08


get scripts

next tip button



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, December 18, 2012 - 7:25:41 AM - Piet Lampo Back To Top

sorry,the code also works as is in vbexpress 1012

i tried to access a db that was in a dir i had no access to

sorry

Pierre


Sunday, December 16, 2012 - 5:11:36 PM - Piet Lampo Back To Top

thanks tim, i would immensely appresiate that

however i just tried with vb expres 2010, and it succeeded by following your instructions

the test code

PrivateSub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

Button4.Click

Dim dbEngine AsNew Microsoft.Office.Interop.Access.Dao.

DBEngine

Dim db As Microsoft.Office.Interop.Access.Dao.

Database

Dim RS As Microsoft.Office.Interop.Access.Dao.

Recordset

db = dbEngine.OpenDatabase(

"c:\users\jenny\documents\noordenwind.accdb"

)

RS = db.OpenRecordset(

"klanten"

)

Label1.Text = RS.Fields(0).Value.ToString

MessageBox.Show("qqq"

)

'RS.Edit()

'RS.Fields("Functie").Value = "qaz"

'RS.Update()

RS.Index =

"City"

RS.Seek(

"=", "Boston"

)

IfNot RS.NoMatch

Then

Label1.Text = RS.Fields(0).Value.ToString

End

If

MessageBox.Show("qqq"

)

RS.MoveNext()

Label1.Text = RS.Fields(0).Value.ToString

RS.Close()

db.Close()

db =

Nothing

 

EndSub

however still no succes with vb 2012 expres,i wonder why

 


Thursday, December 13, 2012 - 12:50:55 PM - Tim Cullen Back To Top

Sorry, guys-I can't seem to find the solution so I'll create the tool again.


Tuesday, December 11, 2012 - 1:28:08 PM - Piet Lampo Back To Top

hi i to would like to see the code

i have tried and tried again with vb2012 but no succes

tia

Pierre


Tuesday, March 27, 2012 - 10:18:36 PM - Tim Cullen Back To Top

Hey guys:

 

Let me dig through my code stashes to see if I can find it.  If I can't then I'll re-create some of it and post.

 

Tim Cullen


Tuesday, March 27, 2012 - 5:19:02 PM - Kevin J Back To Top

Tim,
I too am interested in the code you have described in this article.  I realize it's been 17 months since this article was published, but maybe (hopefully) you still have access to that soure code and are willing to post it here?

 

Thanks in advance,
Kevin


Friday, October 08, 2010 - 5:33:11 PM - Tim Cullen Back To Top
Kaspal:

Thanks for writing!  There are a number of ways to achieve this.  My quick and dirty Visual Basic method is one you can insert into an SSIS package and have it execute as often as necessary-I stuck the code below into a console application, which could also be executed via a SQL Server Agent job.  I'm sure there is a PowerShell way to do it but I'm not familiar enough with PowerShell to give a good example.  Take a look at the code and let me know if it works for you-TC

Imports MSMO = Microsoft.SqlServer.Management.Smo

Imports SD = System.Data

Imports SDS = System.Data.SqlClient

Imports SIO = System.IO

 

Module Module1

    Public strCurrentErrorLog As String = String.Empty

    Public strSQLServer As String = "[Server name]"

    Public intMaxSize As Int16 = [Whatever value you want-this represents the number of megabytes you want to limit the log file to]

 

    Sub Main()

        GetErrorLogs()

 

    End Sub

 

    Public Sub GetErrorLogs()

        Try

            Dim svr As New MSMO.Server(strSQLServer)

            Dim strErrorLog As String = svr.ErrorLogPath

            Dim strLogs As String() = SIO.Directory.GetFiles(strErrorLog)

            For Each strLog As String In strLogs

                Dim fiLogName As New SIO.FileInfo(strLog)

                If (LCase(Left(fiLogName.Name, 5)) = "error" And fiLogName.Name.IndexOf(".") < 0) Then

                    If (fiLogName.Length / (1024 * 1024)) > intMaxSize Then

                        CycleSQLErrorLog()

                    Else

                        System.Environment.Exit(0)

                    End If

                End If

            Next

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

 

    End Sub

 

    Public Function CycleSQLErrorLog() As Boolean

        Dim cnSQLServer As New SDS.SqlConnection("server=" & strSQLServer & ";database=master;Trusted_Connection=True;")

        Dim stmErrorLog As New SDS.SqlCommand

        With stmErrorLog

            .Connection = cnSQLServer

            .CommandType = CommandType.StoredProcedure

            .CommandText = "master.dbo.sp_cycle_errorlog"

            .CommandTimeout = 5

            .Parameters.Add("@return_value", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

        End With

        Try

            cnSQLServer.Open()

            stmErrorLog.ExecuteNonQuery()

            cnSQLServer.Close()

            System.Environment.Exit(0)

        Catch ex As Exception

            'Whatever you want here, but don't do the Console.Write for automated processed or it will hang

            Console.Write(ex.Message)

            Console.Read()

        End Try

        '

    End Function

 

End Module




Friday, October 08, 2010 - 5:26:36 PM - Tim Cullen Back To Top
Oded-

As soon as my wife relinquishes control of the laptop I'll snag the code and get it posted.


Friday, October 08, 2010 - 2:45:53 PM - kaspal Back To Top
hello,

We have problem with SQL error log. The error log is getting large size every 2 or 3 days. So I scheduled the job to run the sp_cycle_errorlog. The job is running fine but now a days the file size is reaching 1 GB with in one day some time .

 

I need to write script to check the error log file size when the job runs, the job has to check whether the error Log file size reached to 300MB then it has to run the  sp_cycle_errorlog other wise it has to skip the job. Could any body please help me to write the script. I am new to development part .

 

Thanks in advance

 




Friday, October 08, 2010 - 8:53:58 AM - Oded Dror Back To Top
Hi there,

Can you post the code also?

 

Thanks,

Oded



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools