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
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
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.
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
2. Change the default application pool identity to Network Service
3. Give Network Service “modify” rights to the folder where the database is stored
4. and finally 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.