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.
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.
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=;")
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:
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).
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.
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")
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:
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).
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
Microsoft Access 
/ 111 
ASP.NET Export to Excel and Word using VB.NET and C#
