Jet Database Engine

The Jet database engine underlies MicrosoftAccess and other Microsoft products.

The Jet Database Engine is well described in a book by Dan Haught and Jim Ferguson. -- AnandaSim

Microsoft is slowly phasing out Jet in favor of the SqlServerDesktopEngine (aka MSDE). But the whole thing breaking a lot of inherent Access functionality. In a recent rant on Access NG

http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&selm=ObBtfrvTCHA.1648%40tkmsftngp08

Ananda tries to evangelise that Access needs urgent and good attention from the MS Product Team.

KlausOberdalhoff? picked up the effort and got some response from a private forum - a little understated but it's a start.

Click on MicrosoftAccessWithoutJet -- AnandaSim

On Access' transaction support:

The JetDatabaseEngine (what Access databases are built on, usually) supports transactions, but you can't do it with just SQL. You have to use a DataAccessObjects? (DAO) Workspace object, and use methods such as .BeginTrans? , .CommitTrans?, and .Rollback (from memory, these may not be the right method names). Suffice to say, it's rather tedious...

On books:

Out of all the books I've seen, anything in the "Developers' Handbook" series by Sybex is the best. In a nutshell, you'll only find stuff you don't usually find in the documentation, help, or other books.

The most dog-eared book in my collection is the "Access 97 Developers' Handbook" by PaulLitwin?, KenGetz? and MikeGilbert?. ISBN 0782119417 If you do any work with Access, I highly reccommend this as it covers most everything you could possibly need. I've only glanced at the 2000 edition, but with 2 volumes each over 1000 pages, you'll probably never need anything else <grin>.

(by the way, how does one properly cite books/authors in Wiki?)

On Access in multiuser environments:

You can, but as I'm slowly finding out - it is even less fun than dealing with transactions. The JetDatabaseEngine is file-based, which means all the work must be done on the client. There's nothing running on your server that provides the data except the file server itself.

What I've been trying to find out is 'what' is being sent over the wire when this happens. Is it the full database, or is Access smart enough to request the indexes and what it needs, 'then' retrieve the database if it needs it?

If you're going to have a very small scale, in-house thing, or if it's something where performance is not important (is there such a thing), then Access is OK.

The MicroSoftDataEngine (MSDE) is basically a scaled down version of SqlServer. It comes with Access 2000 and is a better choice than Jet for multiuser apps. It still has a limit of 5-15 users from what I've heard, more than that, and you really need a RealDatabase like Oracle or SqlServer.

-- DanNovak (in a very verbose mode today for some reason)

Actually, the MicroSoftDataEngine *is* SqlServer with some PerformanceBrakes? in it to make it less than useful for more than a few users. And it doesn't come with any of the SqlServer ClientSideTools. --CurtHagenlocher?

I've used Access as a database for VB apps, using Active X servers running on the same machine as the database files and putting all the data acess through them. Never had corruption problems as their is only one conection to the database. -- AndyMorris Still, this can't be terribly scalable. Why not try something like PostgreSql? In short we used Access because it was either that or Oracle, with a small army of DBA's, accessed thru tuxedo via Cobol transactions.

For me, the problems with multi users access and performance over a network, combined with the desirability of keeping persistence logic separate from other layers leads to a data-access layer that runs on the same machine that has the disk with the .mdb file.

You then make business objects that have a state property so you can pass the ObjectByValue in and out of Get and Save methods in the persistence layer.

You then have to tackle marshalling the objects state in the state property. For simple classes with no sub (child) classes you can store the state in a user defined state and Lset it to a string. For more complex classes the property bag class comes in handy, or to make the ‘messages?more readable XML works well. --AndyMorris