Jet vs. SQL Server: Why Move?

team lib

First off let's be clear on what both SQL Server and SQL Server Desktop are. SQL Server 2000 is the flagship database product from Microsoft. It is suitable for use in installations holding massive amounts of data and with up to thousands of users. SQL Server Desktop , supplied free of charge on the Office XP CDs, is also SQL Server 2000, but with two very serious limitations:

  • There is no front-end user management interface. SQL Server is the Database Management System, just like JET. The full version of SQL Server is supplied with Enterprise Manager, SQL Query Analyzer, and other tools used to both manipulate data and manage the installation. In order to manage an instance of SQL Server Desktop, you must either use command-line tools or work via code. You could, of course, purchase some of the third-party management tools available via the Internet. The following table lists some tools you may find useful, but, of course, we endorse none of them.




    MSDE Query

    Free Download


    VB 6 Free Download

    MSDE Manager

    30 day evaluation

    MSDE TinyAdmin


    Another option available to you is to build your own management interface to the Database server using Microsoft Access. However, this requires a fairly high level of knowledge of how SQL Server operates and of its objects. Using the Access interface you can manage tables, relationships, and associated properties, but you cannot manage areas such as security.

  • SQL Server Desktop is rigged so that performance falls after a number of concurrent threads are running on the server. Once the limit is reached (more than 5 processes) performance slows until a thread is freed to bring you back within the limits. This process is called Target Benchmark Users or TBU. There is no indication given to the user that processing has slowed down. Therefore, there is no limit to the number of users who can work with SQL Server Desktop. A common mistake made when talking about TBU and SQL Server Desktop is that the limit refers to users. It does not - it refers to concurrent threads of execution.


    It has been strongly suggested that the sole reason for SQL Server Desktop is to get Access developers to move up to the full version of SQL Server 2000. One thing it does provide, however, is a free training tool for developers to use before laying out their cash on a full server license. Rumor also has it that there will be no further development of the Jet database engine used by Access.

Advantages of SQL Server

There are many reasons to move your Jet database up to SQL Server, including:

  • Movement of all or most of your data processing to the database server itself as opposed to processing on each client machine.

  • Improved security with SQL Sever through the use of roles and groups, which are far in excess of the security features used by Microsoft Access.

  • The Access 2002 interface makes working with SQL Server 2000 objects very easy. Graphical tools are provided which allow you to create Stored Procedures, Views, User Defined Functions, and Database Diagrams.

  • Management of multiple databases via Enterprise Manager.

  • Improved backup - can cope with massive databases as opposed to the 2GB limits of MS Access. There are SQL Server databases being used with terrabytes of data.

  • Scalable for use on the Internet. Microsoft SQL Server 2000 will handle hundreds of users with no problems, has built in support for XML, and is a true client-server database engine, unlike Access which is a file-based system.

  • Improved data manipulation language, Transact SQL (T-SQL is SQL Server's version of the Microsoft SQL language, and is discussed briefly later in this chapter).

  • Use of stored procedures and views to secure access to tables by users. Using views we can restrict the user to just the data they require and "hide" sensitive data.

  • Users have no access to the database file, unlike an MDB file. In this way it is impossible for the user to mess with the table or the table structure as they are safely tucked away and protected by the database server.


    On one occasion a client tried to open the MDB file in Microsoft Word. Why? I have no idea. I do know it cost close to $2,000 to have the database fixed.

Disadvantages of SQL Server

Of course, things are not all rosy, and there are several disadvantages to moving your database, including:

  • New skills must be learned, including T-SQL, Stored Procedures, SQL Server Management and ADO, particularly if you're working with Access Data Projects

  • SQL Server license costs are not cheap, and you may also be required to purchase additional hardware, such as dedicated servers, to run the database.

  • Management of the database server is not as easy as with Microsoft Access. You could have hundreds of users and multiple databases to manage when using SQL Server.

  • Your existing applications may need to be rewritten, or, at least, changed in some ways in order to be fully compatible with SQL Server and the client server model in general.

  • Not all Access features are supported by SQL Server, for example, functions such as format need to be changed before they will run on SQL Server.


    We will be looking at the issue of Access functions shortly when we upsize the Ice Cream database to SQL Server 2000.

team lib

Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256 © 2008-2017.
If you may any questions please contact us: