Condividi tramite

Microsoft JET Basics

  • Articolo

Access and Jet

Jet is the ubiquitous term for the access database engine. The Microsoft Jet OLE DB Provider and the Access ODBC driver both use the Jet database engine to connect to a variety of Data Sources. Access to non-access database types such as dBase, Paradox and text are though installable ISAM drivers that connect to the Jet Engine.

Internally Access uses Jet, and you might say that Access is a GUI for the Jet engine.

Also, DAO (Data Access Objects) is a COM layer over the Jet Engine.

JET Provider 3.51 vs. 4.0

The Microsoft JET OLEDB Provider can be used to connect to an Access database when using ADO. The 3.51 version of the provider is a very basic provider meant to only give connectivity to an Access 97 or earlier database. MDAC 2.0 is the last version of MDAC to install this provider.

An issue involving an application which uses the 3.51 version of the Microsoft JET OLEDB Provider returning the error “3706: ADO could not find the specified provider" is addressed in the following KB article:

https://support.microsoft.com/support/kb/articles/q197/9/02.asp

The Microsoft JET OLEDB Provider version 4.0 is a much more flexible provider, which is required to connect to an Access 2000 or later database, and also gives the ability to use ADOX. ADOX is essentially an extension to ActiveX Data Objects, and allows for the manipulation of the database schema. The following links on ADOX can be found in the MSDN at:

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbpj99/html/jm0699.asp

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrlistingtablesinaccessdatabase.asp

The last version of MDAC to install the JET OLEDB Provider was MDAC 2.5 SP2. Additional details on this MDAC release are available at:

https://www.microsoft.com/data/MDAC25info/MDAC25SP2Manifest.htm

Also, if you are installing MDAC 2.6 and need to install the JET components, these are available at:

https://www.microsoft.com/data/download.htm#Jet4SP3info

Unrecognized Database Format with Data Control

One common issue that is encountered by user’s who have upgraded to an Access 2000 database, and are using the intrinsic Data Control, is the following error when attempting to open the database:

Run-time error 3343 Unrecognized Database Format XXX

This is due to the fact that Access 2000 and 2002 database files use the JET 4.0 engine. The intrinsic Data Control is set up to work with JET 3.51 or earlier. However, by obtaining Visual Basic Service Pack 4, you can correct this problem by simply modifying the Connect Property to use ‘Access 2000’. There are some additional workarounds that can be viewed at:

https://support.microsoft.com/support/kb/articles/q238/4/01.asp

Access Database and ASP – Just say No!

There are a number of developers who use Access databases with ASP pages. This is not the best option in the first place, as Access was not intended as an Enterprise database. However, if the developer insists upon using Access and ASP, it is a very bad idea to use the Access ODBC driver.

Using Microsoft Jet with IIS - https://support.microsoft.com/?id=299974

When you need unlimited users, 24x7 support, and ACID transactions, we recommend 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.

Note "ACID" is an acronym for the four properties of transaction-processing systems: Atomicity, Consistency, Isolation, Durability.
ASP also supports using the Microsoft Jet database engine as a valid data source. The Access ODBC Driver and Microsoft OLE DB Provider for Microsoft Jet are not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as web, commerce, transactional, messaging servers, and so on.

When you run Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur. The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling (including calling a thread-safe version of Visual Basic for Applications).

Both the ODBC driver and OLE DB Provider provide a wrapper around the Microsoft Jet database engine, which provides the core functionality of both the Access ODBC driver and Jet OLE DB Provider. While Microsoft Jet is consciously (and continually) updated with many quality, functional, and performance improvements, it was not intended (or architected) for the high-stress performance required by 24x7 scenarios, ACID transactions, or unlimited users, that is, scenarios where there has to be absolute data integrity or very high concurrency. Microsoft has performed very robust Multi-User/Stress scenarios that are designed to verify functionality in the appropriate usage space of Microsoft Jet, and addresses any issues that may be revealed.

The Microsoft Access ODBC driver can have issues with stability due to the version of Visual Basic for Applications that is invoked, because the version is not thread safe. The best option is to use the Microsoft Jet OLE DB Provider to connect to the Access database. This has many fixes and enhancements implemented in it, and also provides for calling a thread-safe version of VBA. Additional information on this issue can be found at:

https://support.microsoft.com/support/kb/articles/q299/9/73.asp

JET Installation

The Microsoft JET Database engine has a number of files associated with it, and also may be used by many different applications. There are times when particular files may be removed, or mismatched versions of files may be installed, which can cause for problems with VB applications that use JET. The following articles deal with JET installations as it relates to DAO, ADO, and ODBC:

https://support.microsoft.com/support/kb/articles/q240/3/77.asp

https://support.microsoft.com/support/kb/articles/q245/5/24.asp

Lab – Creating VB Script to open an Access database

Create a VB Script file that opens a Connection to a JET database, and creates a Recordset. Display a message box to show the connection is opened, and also display a message box with a value from the recordset.

Solution:

set Conn = CreateObject("ADODB.Connection")

set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\Nwind2000.mdb;"

if conn.state = 1 then

msgbox "Connection successful"

end if

sSQL = "SELECT * FROM Customers"

Set rs = Conn.Execute(sSQL)

msgbox rs.fields(0).value

rs.close

conn.close

set rs=Nothing

set conn=Nothing

Additional Reference points for JET:

Using ADO with the Microsoft Jet Provider

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

Q&A on Microsoft JET

https://www.microsoft.com/ACCESSDEV/Articles/QAJET30.HTM

Comments