Connecting to a Microsoft Access database with ASP.NET

In this article, I shall discuss different methodologies for connecting to a Microsoft Access database using ASP.NET without any errors.

Contributed by
Rating: 4 stars4 stars4 stars4 stars4 stars / 111
April 04, 2006
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

A downloadable file for this program is available here.

Every .NET programmer knows how to connect to Microsoft SQL Server 2000/2005 using ASP.NET very easily.  Connecting to (and working with) a Microsoft Access database is not as simple as MS SQL Server.  It is always possible to run into errors when working with Microsoft Access together with ASP.NET. 

It all starts with security.  MS SQL Server has its own security policies, and it is a concrete RDBMS when compared to MS Access.  Connecting to a Microsoft Access 2003 database is very easy, but when you try to manipulate some data within the database, your application may run into simple errors.  I try to address the most important issues in this article.

In this article, I shall demonstrate how to connect and work with a Microsoft Access 2003 database in a very easy manner, with several possibilities.  For the sake of this demonstration, I simply created a database using Microsoft Access 2003 with only one table, “emp,” having fields “empno,” “ename,” “sal” and “deptno.”  I added a few rows to the same table and saved the database with the file name “MyDB.mdb” in the path “c:\AccessDB”. 

As you can see, I didn’t save the MS Access DB file in any virtual directory (like “c:\inetpub\wwwroot”) where my ASP.NET application generally lives!  It is strongly recommended to have the database stay outside any virtual directory (due to several security considerations).

I enclosed the source code in the form of a single file (“.zip” file).  You need to extract the folders from the zip and configure virtual directories on your own.  I developed these applications using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft Access 2003.

Connecting to a Microsoft Access database directly using ASP.NET

Try to create your own web application using Visual Studio.NET (I named mine “ConnectAccessDirect” for this demonstration).  Design your web form to look something like the following (Fig1).

After designing the form, switch to the code and add the following line at the top.

Imports System.Data.OleDb

I am importing the namespace “System.Data.Oledb” because I would like to connect to the Access database using the OLEDB provider for .NET.  Add the following code to your “show/refresh” button:

Private Sub btnList_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnList.Click
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\AccessDB\MyDB.mdb;User Id=admin;Password=;")
        Dim da As New OleDbDataAdapter("select * from emp", cn)
        Dim dt As New DataTable
        da.Fill(dt)
        da.Dispose()
        cn.Dispose()
        Me.DataGrid1.DataSource = dt
        Me.DataGrid1.DataBind()
    End Sub

Once you complete the above, you execute your application (by pressing F5) and click on the button “Show/Refresh.”  You should be able to see all the rows on the screen.  Till now, you have not needed to configure anything.  Now, let us proceed to the next section.

Manipulating a Microsoft Access database directly using ASP.NET

Once you complete everything as suggested in the previous section, you need to work with one more button, “Add,” within the same application.  Add the following code to the “Add” button available.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnAdd.Click
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\AccessDB\MyDB.mdb;User Id=admin;Password=;")
        Dim cmd As New OleDbCommand
        With cmd
            .CommandText = "insert into emp (empno,ename,sal,deptno) values (" & Me.txtEmpno.Text & ",'" &
Me.txtEname.Text & "'," & Me.txtSal.Text & "," &
Me.txtDeptno.Text & ")"
            .Connection = cn
            .Connection.Open()
            .ExecuteNonQuery()
            .Connection.Close()
            .Dispose()
        End With
        cn.Dispose()
        'refresh the list
        btnList_Click(Nothing, Nothing)
    End Sub

Once you complete the above, you execute your application (by pressing F5), provide some reasonable values in the textboxes and finally click on the button “Add.”  If you are lucky, you should be able to see the newly added row in the grid. 

Not everybody will be lucky. Some of you may receive an error, “Operation must use an updateable query.”  That single statement (or error) has several meanings, according to Microsoft.  If you really want to know about the error, you simply copy and paste the error message into the “search” box of the Microsoft website and you will see plenty of explanations focusing on that error. 

To solve that error in this scenario, you need to change your “web.config” file by providing the necessary “impersonation” configuration.  Open the “web.config” file of your web application and insert the following line just below the  “<system.web>” tag:

<identity impersonate="true"
 userName="computername\administrator" password="password" />

Replace the “computername” with your system name and “password” with the password of the “administrator” account.  You can also replace “administrator” with any other customized user account with respective privileges.  I simply demonstrated this using the “administrator” user account.

Now, you should be able to run without any errors (hopefully).

Connecting to a Microsoft Access database through ODBC using ASP.NET

I need to warn you before you use this method.  Working with ODBC really slows down the performance of your application.  In general, ODBC is used to connect to non-Microsoft data sources.  In this scenario, I just wanted to show you how to connect through ODBC.  For the sake of simplicity, I'm using Microsoft Access 2003 as an example.

Try to create a new web application using Visual Studio.NET (I named mine “ConnectAccessODBC” for this demonstration).  Design your web form with the same layout as shown in the first section of this article.

After designing the form, switch to the code and add the following line at the top.

Imports System.Data.Odbc

I am importing the namespace “System.Data.Odbc” because I would like to connect to the Access database using the ODBC provider for .NET.  You can connect to any data source supported by ODBC.  Add the following code to your “show/refresh” button:

 Private Sub btnList_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnList.Click

        Dim cn As New OdbcConnection("Driver={Microsoft Access
Driver (*.mdb)};DBQ=c:\AccessDB\MyDB.mdb")
        Dim da As New OdbcDataAdapter("select * from emp", cn)
        Dim dt As New DataTable
        da.Fill(dt)
        da.Dispose()
        cn.Dispose()
        Me.DataGrid1.DataSource = dt
        Me.DataGrid1.DataBind()
    End Sub

You need to modify the connection string with the proper “database driver” registered in ODBC, according to your data source.  Once you complete the above, you execute your application (by pressing F5) and click on the button “Show/Refresh.”  You should be able to see all the rows on the screen.  Now, let us proceed to the next section.

Manipulating a Microsoft Access database through ODBC using ASP.NET

Once you complete everything as suggested in the previous section, you need to work with one more button, “Add,” within the same application.  Add the following code to the “Add” button available.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnAdd.Click
        Dim cn As New OdbcConnection("Driver={Microsoft Access
Driver (*.mdb)};DBQ=c:\AccessDB\MyDB.mdb")
        Dim cmd As New OdbcCommand
        With cmd
            .CommandText = "insert into emp
(empno,ename,sal,deptno) values (" & Me.txtEmpno.Text & ",'" &
Me.txtEname.Text & "'," & Me.txtSal.Text & "," &
Me.txtDeptno.Text & ")"
            .Connection = cn
            .Connection.Open()
            .ExecuteNonQuery()
            .Connection.Close()
            .Dispose()
        End With
        cn.Dispose()
        'refresh the list
        btnList_Click(Nothing, Nothing)
    End Sub

Once you complete the above, you execute your application (by pressing F5), provide some reasonable values in the textboxes and finally click on the button “Add.”  If you are lucky, you should be able to see the newly added row in the grid. 

If you run into the same error, “Operation must use an updateable query,” you need to modify your file “web.config” by inserting the following line just below “<system.web>” tag:

<identity impersonate="true"
 userName="computername\administrator" password="password" />

Replace the “computername” with your system name and “password” with the password of the “administrator” account.  You can also replace “administrator” with any other customized user account with respective privileges.  I simply demonstrated using the “administrator” user account.

Now, you should be able to run without any errors (hopefully).

Connecting to a password protected Microsoft Access database directly using ASP.NET

For this demonstration, I created another database named “MyDBwithPwd.mdb.”  It was protected with the password “admin.”  Now, we need to open a password protected Access database using ASP.NET.

Try to create your own web application using Visual Studio.NET (I named mine “ConnectAccessWithPassword” for this demonstration).  Design your web form with the same layout as shown in the first section of this article.

After designing the form, switch to the code and add the following line at the top.

Imports System.Data.OleDb

Add the following code to your “show/refresh” button:

Private Sub btnList_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnList.Click
        Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\AccessDB\MyDBwithPwd.mdb;User Id=admin;Jet
OLEDB:Database Password=admin")
        Dim da As New OleDbDataAdapter("select * from emp", cn)
        Dim dt As New DataTable
        da.Fill(dt)
        da.Dispose()
        cn.Dispose()
        Me.DataGrid1.DataSource = dt
        Me.DataGrid1.DataBind()
    End Sub

The most important specification from the above code is the following line:

Dim cn As New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\AccessDB\MyDBwithPwd.mdb;User Id=admin;Jet
OLEDB:Database Password=admin")

I specified the password by adding “Jet OLEDB:Database Password=admin” to the connection string.  Don’t forget to specify “impersonation” in the “web.config” file when you want to manipulate the data.

Sometimes, you may receive another error: “The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\WebAppSample\SampleDB.mdb'. It is already opened exclusively by another user, or you need permission to view its data.”  The error really makes a programmer’s life miserable.

That error generally occurs due to lack of permissions.  Try to give all rights (Read, Write, Modify etc.) to the folder having the Access database.  The best way to keep away from that error is to just keep the database out of any virtual directory and follow the rules according to the first two sections.  It should be perfectly all right.

If you are storing an Access database on network storage (especially shared folders on a network), you still need to provide proper permissions to connect and modify the database information.

Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

blog comments powered by Disqus
MICROSOFT ACCESS ARTICLES

- Link Data from Excel to Access
- Import Excel Data into Microsoft Access
- How to Create a Relational Database in Access
- Improving Construction of Statistical Proces...
- How to Monitor Website Traffic using Statist...
- Chi Square Test of Independence with MS Excel
- Two-Way ANOVA (Analysis of Variance) in Micr...
- Converting a MySQL Database to an Excel Work...
- Linking SQL Express 2005 Tables to MS Access...
- Working with Access Projects in Access 2007
- Exploring Access 2007
- Working with Stored Procedures in an MS Acce...
- Creating and Using Action Queries
- Creating Data Access Pages with Charts using...
- Advanced Ideas using VBA

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 9 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials