The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

What is a Jet Engine?

OK, I know this could be very simple question for you guys specially in Microsoft development world but I really need to know about it and I could not find a preciese definition even in MS-Access help.
 So far I want to know:

 1. Is JET a component,technology or some special kind of old-fashioned DBMS(Maybe most near to File management systems)?

 2. Is it available on all win installments or it is given by for example .Net framework?

 3. Is this true that MS-Access a Jet engine and not a true DBMS? (in contrast with Oracle,SS2K, MySQL...).

 4. When do you prefer to use Jet? and when not?

 -Thanks for your valuable time friends. :)
Ach Send private email
Monday, April 17, 2006
 
 
Jet is the database engine used with Access.

Many have had alot of success running it for both small and large databases.

However, there are instances (quite a few in my experience) where the database can get corrupted, fail to create auto-increment columns etc.
D in PHX Send private email
Monday, April 17, 2006
 
 
Let me respond to your last question...I prefer not to use Access as I am typically working in enterprise level stuff and use SQL server.

I could see using Access for a relatively small application, but IMHO, spending a great deal of time on an Access based solution isn't very effective (unless you are UBER good at Access).

Many folks are 'hand-held' thru the process of created a database, entry forms, etc..then they try to do something complex and hit a brick wall.

Then again though, I could be crazy, as I believe that 80-90% of developers (FE, UI) that touch T-sql should keep their hands off of it.

Let me know if you have more specific questions, I'd be happy to help out.
D in PHX Send private email
Monday, April 17, 2006
 
 
"1. Is JET a component,technology or some special kind of old-fashioned DBMS(Maybe most near to File management systems)?
"

Jet is a fileserver database.  Clients to a Jet database (i.e., mdb file) access the file through the filesystem but do all of the processing locally on the client's cpu.  This means unless the app is very carefully designed you may be passing lots of unnecessary data over the network to the client machines, only for it to be filtered out by local processing on the client.  Obviously not as scalable as a true db server where only the result sets are transferred over the net to the client.

I believe the "Jet" moniker originally referred to the query optimization technology that MS bought or developed. Or maybe they just ported over the Rushmore query optimization technology they bought with Foxpro. . . .
Herbert Sitz Send private email
Monday, April 17, 2006
 
 
> I believe the "Jet" moniker originally referred to the query optimization technology that MS bought or developed

When something is slow, marketing guys will often choose a name to make it *sound* fast.

I won't say whether I think that is the case here.
S. Tanna
Monday, April 17, 2006
 
 
Access is a front-end that by default is used with Jet databases.  Jet makes some special accomodations for Access, like allowing Access forms and such to be stored in the MDB.

Jet is basically a DLL (actually a package of DLLs), the "access routines" that a program employs to make use of a Jet database.  Programs can use any of several ways to couple to Jet - most don't use Jet directly at all, instead using DAO, ADO, etc.

There is nothing "true" about client/server databases like SQL Server et al.  It is a difference in the way the programs are coupled to the database and where different parts of the crunching take place.  Both approaches have their places, and their tradeoffs.  Jet is probably weakest when used by multiple clients accessing a common MDB on a file share.  Sadly this is the application space most Access use targets, and thus its Achille's Heel.

I can't think of a reason why FoxPro would not be as vulnerable, as it parallels Access in so many ways.  But it seems to be politically correct to bash Access/Jet and not Fox or other xBase technologies.
Lutz
Monday, April 17, 2006
 
 
It's not that people haven't bashed xBase for the same reasons as bashing Access, and wouldn't be equally justified in doing so, it's just that developing new xBase apps has become very much a marginal activity in the last few years... so few people feel the need to bash xBase.

In a similar vein, you might also say that you don't see too many new articles slagging off Algol-60 any more.
S. Tanna
Tuesday, April 18, 2006
 
 
There are two Jet engines. The one that most people associate with the name Jet is "Jet Red", the engine that used to be available as part of the Microsoft Data Access Components, and as such is considered a subsystem of the Windows OS itself. Through its COM API (called DAO), this engine is available to any COM-supporting compiled or scripting language. All of us probably have this engine living on our computers.
The Jet engine supports SQL (its own dialect, close to SQL-90, with some very useful additions), referential integrity and transactions. It also allows passthrough linking to other DB engines. There is no Jet "server" as such; each application using Jet would load their own copy of the engine, and operate directly on the database. The database itself is stored in a file with the extension .MDB.
Microsoft Access is a superb front-end tool that happens to use the Jet engine as a back-end. It can also use the SQL Server engine *instead of* Jet.
"Jet Blue", or the Extensible Storage Engine, is a totally different beast. It provides ISAM data manipulation with transactions. No SQL, no COM-based API, only a C API. This engine exists in all versions of Windows post 2000, and is used by applications with very high data loads, such as Microsoft Exchange and Microsoft Active Directory.
Raj Chaudhuri Send private email
Tuesday, April 18, 2006
 
 
Thanks all guys,
 As I could understand: JET 'database' is not anything more than a bunch of dumb mdb files + some DDLs in client site (e.g. a VB application running on remote node) that facilitates the conversation routines to some fron-end at server side. Am I right?
 <comment for Raj: I will refer to 'Jet Red' in all of this post as Jet>.
 So another question rises: If many users want to use the same piece of data (reside in ONE mdb file at server side) then what will be the locking mechanism?
 Is this can be considered as the main reason of maximum 120 cuncurrent users in Access databases? As I could see this is not the fault of MS-Access (as a mere dumb visual front-end) but the architecture of JET engine. (I know there are 2 seperate beasts now!). Am I right?
 -Thanks again
Ach Send private email
Tuesday, April 18, 2006
 
 
> If many users want to use the same piece of data (reside in ONE mdb file at server side) then what will be the locking mechanism?

It uses a combination of the windows file locking, and also creates a jet locking file (it uses the .ldb extension). In fact, if Jet can’t create this locking file, then the mdb file is opened in non multi-user mode (exclusive).

Windows does have support built in for sharing and file locking…and jet uses this.

> Is this can be considered as the main reason of maximum 120 cuncurrent users in Access databases?

No, not really. Actually, the limit is 255. However, that limit is likely somewhat arbitrary (well…ok..255 = 256 = 1 byte – so, perhaps some issues of the size of the locking table, and other issues is at stake).

Each copy of windows does ship with the DAO jet engine.  Lots of software out there uses JET…they just don’t often admit it due to its association with ms-access. If you don’t have ms-access on your computer…you can still read mdb jet files with virtually any software environment you have…including windows scripts…

It should be well noted that the next version of ms-access gets it own version of the JET engine called ACE. This has the downside of that files created in access 2007 will not be readable on any windows box unless access is installed on the box (or one users the older format when working with 2007).

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
http://www.members.shaw.ca/AlbertKallal
Albert D. Kallal Send private email
Tuesday, April 18, 2006
 
 
JET used to stand for Joint Engine Technology, I don't know if it still does.
John Topley Send private email
Tuesday, April 18, 2006
 
 
"It should be well noted that the next version of ms-access gets it own version of the JET engine called ACE. This has the downside of that files created in access 2007 will not be readable on any windows box unless access is installed on the box (or one users the older format when working with 2007)."

Thanks Albert, didn't know that. Must keep up with current trends. (What are you doing over here anyway, I thought you lived at CDMA).

Jet is a good file based db engine.

It's perfect for RAD with Access as it's client, with user audiences in the 2-20 range. We hear reports of use with 100+ users (by which I mean simulataneous), but that assumes a perfect network, and very well designed software.

The standard architecture for Access/JET applications used in a workgroup setting is to have 2 .mdb files. One contains data and lives on a server (or workstation acting as a server) the other contains forms/screens etc. and there is a copy of it on each users machine. It's like the 'client'. An Access/JET application designed along these lines can work just hunky dory.

But.... JET is a file based db, so the risk of file corruption is always there, and security is poor. True transactions, triggers, stored procs, complex constraints and the rest we haven't got with JET.

Access and JET are often maligned unfairly. They can be used for fantastically fast development. For rough and ready protyping of a CRUD app nothing beats it. The tight tie in between Access and JET make them a super combination. But if your data is super secret, or there's a lot of it, or you need 1000 people to all access it at the same time then you probably need to look elsewhere
Alvin Rhodes
Tuesday, April 18, 2006
 
 
I've been engaged in providing small-scale database solutions for companies for the last 5 years and now I am a FTE for a small company - exclusively creating database solutions for them....I can tell you that ANYONE who disparages MS Access as an application design tool that "doesn't work," "isn't scaleable," "breaks easily," "lacks reliability," "easily corrupts," and the like, I will say to them that they do NOT know how to program effectively against the JET engine to optimize its functionality – if they even know who to program in VBA at all….

...in short, people who disparage Access as anything other than a robust database solution have no clue about the power that resides within this extraordinary product.

...I always say you are "only as productive as your options" but many naysayers have no idea what options exist within the world of MS Access...

I have developed ALOT of database solutions over the years using MS Access and it has ALWAYS provided me with a solid infrastructure on which to design database solutions....in fact, I've made a pretty good living developing nothing more than MS Access database solutions....

P.S.: I am not affiliated with Microsoft in ANY economic way...I just enjoy using their MS Access product....
Brice Richard Send private email
Wednesday, April 19, 2006
 
 
One more simple question. Do the users need to have Miscrosoft Access installed in their machines in order to use Jet.
Accesser
Wednesday, April 19, 2006
 
 
You don't need MS Access to program a front end for a Jet database.  Jet used to be installed as part of Windows' MDAC that was included with a Windows install.  I think Jet is being left out of most recent versions of MDAC ("Microsoft Data Access Components") but you can download it for free here:
http://msdn.microsoft.com/data/downloads/updates/default.aspx#jet
Herbert Sitz Send private email
Wednesday, April 19, 2006
 
 
Accesser -- Sorry, that didn't answer your question.

Yes, users do need to have the Jet engine installed on each of their computers.  The reason for this is that all of the data processing is done locally on the client computer even though the db file may be located on another server.  The server machine with the file does not do anything other than make the file available to the client machines.  The Jet engine on each of the clients will then access the file and process data in the file locally.  This "fileserver" architecture is the main limitation of Jet and is the reason it is not as scalable or robust as a database server, in which all client communication is with a db server process on the server, and clients never share access the db file directly.

As I said in the above post, most Windows machines will have Jet installed as part of their basic Windows install.    If not then you can download and install for free.
Herbert Sitz Send private email
Wednesday, April 19, 2006
 
 
Sorry, to fully clarify:

No, users don't need MS Access on their computers.  But users do need to have the Jet engine installed on their computers.
Herbert Sitz Send private email
Wednesday, April 19, 2006
 
 
Thanks, Herbert, to clarify.

1.Clients don't need to have MS Access installed to use Jet.

2.As you claimed, all winodows come with Jet installed so that means all windows users will be able to use Jet without download and install anything.

3.If I want to embed Jet in my application.(it is not a client server structure, just a desktop application) what I need to do is just using mdb file as database. After users download and install my exe file, they should be able to go.

Correct me if I am wrong.
Accesser
Wednesday, April 19, 2006
 
 
Accesser -- Correct except for (2).  Most but not all machines will have had Jet installed as part of Windows.  The newest machines, I believe, don't have it.  But you can get the free Jet download from link in post above to install it for those machines.

If you go this route you would probably want to find out exactly when MS stopped bundling Jet as part of a standard Windows/MDAC install.  If it's a shrinkwrap type of install you'd want to build checks for presence of Jet into installer routine.

Also, there is some (small?) potential for version problems depending on how old the Jet version is that's installed on client machines.  May depend on what version of ADO/OLEDB (or DAO) drivers you use in developing your front end against Jet.
Herbert Sitz Send private email
Wednesday, April 19, 2006
 
 
Thanks, Herbert, since there are so many potential pitfalls for using Jet as an embedded db, I think I better use SQLite instead.
Accesser
Wednesday, April 19, 2006
 
 
SQL Lite has same fileserver limitations as Jet.  Plus with SQL Lite you can be next to certain that you'll have to install it on the client machines. 

I've never used SQLLite, but from comments I've heard about it on JoS and elsewhere it is not as safe or easy to use in multi-user mode as Jet is.  This would not be an issue if your app is for desktop single-user setting, but could be if you're going to have  an issue if your app is for multiple concurrent users.
Herbert Sitz Send private email
Wednesday, April 19, 2006
 
 
That is right, it is only a simple desktop application. SQLite should be good enough. I won't have installation and version issues, cause everything will be embedded in the application.
Accesser
Wednesday, April 19, 2006
 
 
+1 for Brice's comments.  I've written many xBase applications for large install base.  Large = 60-100 concurrent users.

One of my applications is a telemarketing order entry system.  I wrote it 1994 and it's still used today in a DOS window.

We occassionally had index corruption but this was a coding error not a database problem.

Today we simply maintain those xBase apps.  concentrating on server based databases (PostgreSQL, MS-SQL, Oracle).

The reason server base systems (RDBMS) are superior is for things like stored procedures, foreign key constraints, etc...  In XBase you had to manage this yourself in your app. and this makes your app more complicated.  It also takes away from development time as well.

When our call center app. got corrupt indexes, we had to write a small app that admins could run to rebuild all the indexes.  Today this rarely happens as we squashed all bugs.

We used a product called CodeBase by Squiter Software to do all our XBase work.  Good product.

RDBMS systems have greatly simplified our code and move the responsibility of data management to the server.  I can create/drop columns with server side tools rather than cobbling things into my app.  I know Access has come along way to do this now too but to do this in access requires thrashing the wire.  RDBMS, I can issue a command and let the server do all the work without tieing up the network nor end users.  Doing this kind of DB manipulation in Access or XBase usually requires all your users to exit the application using the database.

So as someone said above -- there are trade-offs.  And as Brice said, if you code XBase correctly, you can make it sing!  Key words: (code xbase correctly).  With RDBMS you don't need to worry about this.(smile)

Example:
A good example of where XBase shined was when I use to present a window in my app.  The window was a view of a particular table/view with 100,000+ records.  In XBase I can systematically fetch 10,20 or however many records I want and put them in a window to be scrolled.  As the window was scrolled I would fetch the next row or set of records depending on how the window scroll bar was moved.

In RDBMS, the only way to achieve the same thing would be to use "cursors" which is a bit ugly.  It's a complicated query and the query is not standard between RDBMSes.  (Then again, neither is XBase access) but you would think that SQL-92 would force this, no?  Anyway, the cursor feature in most RDBMS is slow compared to XBase.  But probably not slow enough to prevent someone from switching from XBase to RDBMS system.

My 2 bits...
~Eric
Friday, April 21, 2006
 
 
>Most but not all machines will have had Jet installed as part of Windows.

All windows XP machines ship with jet. So, you can actually write a windows script file to read data from a mdb file if you wish….

>The window was a view of a particular table/view with 100,000+ records.  In XBase I can systematically fetch 10, 20….

Actually, if you feed a standard sql query to continues form in ms-access, it does the above by default. So, you don’t even have to write any code…it will only fetch data to fill the screen...

As for corrupted indexes? Code bugs or not….all raise their hands as to who has had a xBase index go bad!! (this was a problem with xBasic indexes. – we always hear about jet currption, but this much due to jet is STILL so widely used today).
    
>We used a product called CodeBase by Squiter Software to do all our XBase work.  Good product.

Great product. I went to university with Ken. We also both served on the executive of the Alberta Society of Software Developers together. (he was president, and I was treasurer/membership director). He is a great guy, and is the one that built CodeBase and the company. A real success story that CodeBase is…


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
http://www.members.shaw.ca/AlbertKallal
Albert D. Kallal Send private email
Friday, April 21, 2006
 
 
One thing to conside with the Jet Engine is that it does not support mult-threaded operations which can make it very unstable in web-based environments.

I don't know if this is still the case but Jet databases never used to delete anything. So if you have a high turnover of data you will need to keep compacting the database.
Err
Tuesday, April 25, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz