RE: Jet vs. Access

But can you tell me what SQL is terms relative to Jet. I always wondered what advantages there are to SQL over Jet.

Okay, SQL vs Jet -- another favorite topic.

First, understand that Microsoft likes to name their products using generic terms. For example, calling a database "Access" and their OS "Windows" leads to sentences such as:

"The application uses Access to access the data in multiple windows on Windows". (sigh...)

"Microsoft SQL Server" is an example of this. SQL Server is an database product from Microsoft. It is similar to other "big iron" database engines like Oracle.

SQL stands for Structured Query Language. SQL is an ISO standard computer language used to perform operations on a database. Jet, SQL Server, and Oracle, are all "SQL compliant", meaning they use SQL to access their database.

And don't tell me they are too numerous to list, just give the simple highlights.

They are too numerous to list :-). There is a white paper on this topic on the MS site. Their summary is:

Use Jet if…
You want the highest compatibility with Access 97 or earlier.
Your environment has a small number of simultaneous users.
You have very low resources, such as memory or disk.
Ease of use is a premium.

Use MSDE [aka SQL Server] if…
You want to develop from a single code base from single user to thousands of users.
You expect a future need for greater scalability.
You require easy merge replication with the central server.
You need the best security.
You need greater reliability, such as transaction logging.
Your system is online 24 hours a day, 7 days a week.
You need stored procedures and triggers.

Needless to say, MS would rather people use SQL Server. Jet is free; SQL Server is not. The white paper is somewhat amusing in that most of it says that SQL Server is superior to Jet, but then goes on to say that Jet is the default database for Office 2000. One would think that if SQL were so great, it would be the standard.

The reason, quite simply, is the "ease of use" point. Election administrators are not in general known for their database administration prowess. This has so far been paramount.

What is GEMS written in?

GEMS is written in my office.

Also, GEMS is written in C++ using the Microsoft Foundation Classes (MFC). MFC in turn uses ODBC (mentioned last mail) to communicate with the database using SQL.

Does GEMS require a database engine or is it embedded?

GEMS requires a database engine of some kind that can speak ODBC. Some of those engines, like SQL Server, require a separate server process to run (sometimes on a network). Others, like Jet, are just a library that is linked with the application directly. In other words, it is a part of GEMS (you might consider that embedded -- I don't know).

We had at one point a long time ago (NEXTSTEP/Apogee days) considered writing our own database engine. We dropped that idea when we moved to Window NT since Jet is free.

If it requires one, is SQL a better engine to run GEMS under

That depends on your definition of "better".

or is JET sufficiently capable of handling any of the tasks we throw at it?

I think so, but that is one man's opinion, and subject to change. We have recently demonstrated that Jet can handle King County. Whether that means "any of the tasks we [ever] throw at it" is a matter for debate.

There are non-technical reasons (ie marketing reasons) to support SQL Server however. I suspect we will have to add support eventually whether or not it is a good idea.

The main reason for not supporting SQL doesn't have to do with any particular difficulty in doing it. The main reason is that keeping all accounts on one database platform means everyone shares the same problems (and solutions). If only one county runs SQL Server, they will almost certainly have problems our support staff is not familiar with, or worse are not found until election night. If we move everyone to SQL Server, then everyone must pay the "ease of use" penalty, even though SQL offers nothing to our current accounts.

Ken