The points that you should think again and again using JET Database Engine in a multi-threaded application (like a classic ASP or ASP.Net web application)
Microsoft JET (Joint Engine Techonology) Database Engine is an implementation of JET Red and it should not be mixed with the JET Blue ESE (Extensible Storage Engine) which is the core of Microsoft Exchange and Active Directory.
We had released the first versions of the JET OLE DB Provider and JET ODBC Drivers in 90s and it had become the part of MDAC (Microsoft Data Access Components).
JET was helping Database developers to reach Access, Excel, CSV file based data sources. Think about the old non-managed VB times. There was no web, no intranets etc. So the apps were mostly running on top of one machine and the locking, concurrency, ACID were not the things that you should consider while developing your app. Because there was just one user out there playing with those datasources through your app.
When the NT4 Option Pack released, classic ASP became very popular in web programming world and the “old database programmers but new web programmers” still wanted to stay in their old “database-like” things and thought that they can go with these old products on the web. But those “database-like ” things were staying behind of the RDBMS (Relational Database Management Systems) products like SQL Server 6.5, 7.0 because of its designs.
When we’re talking about web applications, we’re starting to talk about “multi-threaded” applications because of their nature. You’re putting your code on a server and every dynamic web page request which is feeding the page from a database was doing something with this data source/database.
The some of the web developers noticed the underlying problems of this structure and started moving their database backend to SQL Server or another RDBMS. Because it was handling lockings, queuing, caching etc. all the things in its engine but the JET family data sources were not ready for this new web age.
.NET appeared into the scene and ADO.Net supports OLE DB and ODBC through System.Data.OLEDB* , System.Data.ODBC* namespaces. But if your OLE DB Provider or ODBC Driver is not thread-safe like JET you’re still in trouble.
You still can use JET OLEDB Provider or JET ODBC Driver in your ASP.NET code but you’re alone because our KB article titled “Using Microsoft Jet with IIS” in http://support.microsoft.com/kb/299973 says that :
When you need unlimited users, 24×7 support, and ACID transactions, Microsoft strongly recommends that you use Microsoft SQL Server with Internet Information Server (IIS). Although Active Server Pages (ASP) works with any OLE DB and ODBC-compliant database, IIS has been extensively tested and is designed to work with Microsoft SQL Server with high transaction traffic and unlimited users that can occur in an Internet scenario.
The similar explanations and details can also be found in a similar KB article titled “ACC97: Using Microsoft Jet with IIS” in http://support.microsoft.com/kb/222135 .
Also, if your machine is a Windows 2003 on a x64 CPU or Windows 2008 on a x64 you’ll notice that there’s no JET available for your OS as 64bit. This means that if your application is compiled as 64bit and you’re trying to run on your Windows 2003 x64 OS, it will faill. If it’s the case and if it’s possible, you’ll need to compile the app as 32bit and go with 32bit version of JET because it’s still available as 32bit only on Win2003 or Windows 2008 x64 OS. (You may refer to http://blogs.msdn.com/farukcelik/archive/2007/12/31/udl-test-on-a-64-bit-machine.aspx for the details if you’re developing with OLE DB).
Happy coding 🙂