Microsoft Access as a backend database

I was recently tasked with making a Microsoft Access database more accessible by converting the current data entry screen into a web accessible interface. The data would still be store in the MS Access database but anyone can enter/search for information via the web interface.

The application will only be used on our intranet with the maximum # of concurrent users being 10 or less and hosted on a Windows 2008 server running IIS 7.

I was able to successfully read and store information to the Access database by following the tutorial by CodeFixer @ http://www.codefixer.com/asp-net/tutorials/read-records-access-database-1.asp

Data entry screen

 

Searching the database presented more of a challenge but luckily I ran into an excellent article by Mikesdotnetting.com detailing on just how to put together such a search engine with support for multiple search criteria

http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

This is what the SQL for my Select statement look like

SELECT [ticketNumber], [technician], [partNumber], [pcbSerialNumber], [problemDescription], [problemVerified], [noTroubleFound], [repairComplete], [boardUnrepairable], [badFromStock],[techCoreID], [techTime], [QCATech], [qcDate], [ynRepairable], [partsUsed], [notes] FROM [circuitBoardRepair] 

 WHERE 

 (@ticketNumber IS NULL OR ticketNumber LIKE '%' + @ticketNumber + '%') AND 
 (@technician IS NULL OR technician LIKE '%' + @technician + '%') AND 
 (@partNumber IS NULL OR partNumber LIKE '%' + @partNumber + '%') AND 
 (@pcbSerialNumber IS NULL OR pcbSerialNumber LIKE '%' + @pcbSerialNumber + '%') AND
 (@badFromStock IS NULL OR badFromStock LIKE '%' + @badFromStock + '%');

Once the data have been entered the application also fully support editing and updating each record via the search page.

Search screen

I actually had a harder time getting the application to work on the server under IIS7 than developing it. There were so many specific nuances/settings that need to be set just right for the application to work the way its designed. Listed below are a list of things that I needed to configured on IIS7 for my application to work correctly.

1. Configure the application folder under wwwroot into an application

Convert folder to application

2. Change the default application pool identity to Network Service

Default application pool identity

3. Give Network Service “modify” rights to the folder where the database is stored

Folder permission for Network Service

4. and finally enable Windows Authentication

enable windows authentication

After a lot of trial and errors I was elated to get the application to work on the server the way I had intended during development.

Advertisements

One thought on “Microsoft Access as a backend database

  1. The other benefit that comes with pixza coupons is
    that locating them is very easy. >>Here you just need to discuss your preferences and design oof the cards.
    uk Promo code to a specific print advertisement, commercial,
    radio spot, or partner website.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s