Access (JET) and SQL Server (MSDE) Databases

Overview
There have been some questions and confusion over the past few months regarding claims that software using SQL Server is better than other programs out that use Access. This document will attempt to explain the differences between the two and outline The Bridge Network’s future direction.

Microsoft Access
Access is a program sold by Microsoft that includes database functionality, which can be accessed by other programs using the “Joint Engine Technology,” or JET for short. You may hear people referring to Access and JET interchangeably. We and most other programs use JET for our database functionality. JET uses a file-based database, which means that each workstation accesses the database file across the network and reads and/or writes the information that it needs.

Microsoft SQL
Conversely, Microsoft SQL Server is server-based database technology and accesses the data via the “Microsoft Data Engine” or MSDE. When using MSDE, each workstation sends a request to a “Database Server” that reads/writes the data from the database file and sends the response back to the workstation. This is the fundamental difference between the two databases.

The Advantages & Disadvantages of Access and SQL
Each database technology has advantages and drawbacks. JET is a good choice for small-medium sized offices with 1 to 15 workstations because it is easy to deploy and easy to support. The main drawback for JET is that it is not good for very large office and is more prone to database corruptions caused by hardware problems. On the other hand, MSDE is also good for offices with 1 to 20 computers and has the added advantage of easily scaling to hundreds of workstations.

Despite the apparent advantages of SQL Server, there are still issues that need to be considered. First, upgrading to the full version of SQL Server is an expensive option but may be necessary in some very large offices. Additionally, MSDE has fewer problems with database corruptions but can still become corrupted. Nothing is perfect, and in either case, regular backups should be done. Additionally, the database server for MSDE MUST be a windows-based computer. The current version of MSDE is only Microsoft’s second release and as with many early Microsoft products is not perfect. Currently, the biggest problem is that the installation process is very difficult.

Where The Bridge Network Stands on SQL
Microsoft is still supporting JET.
In fact, the recently released version of Office 2003 contains a new version of Access 2003 with the new JET database engine. Based on their standard support policy Microsoft will be supporting this engine for at least seven years after the release of Office 2003. That being said, Microsoft has admitted that JET is a deprecated product and eventually we are going to have to move to MSDE. As with all changes that we make to Tracker, we will be doing this in stages to minimize the number of problems at our clients’ offices. Of course, there will no additional cost to our clients who want to upgrade to SQL, assuming they don’t want the full version of SQL Server.

Tracker and Technology