Tony's Microsoft Access Blog : Jet

Jackcess is a pure Java library for reading from and writing to MS Access databases.

Tony — Sat, 24 Oct 2009 21:51:45 GMT

I just came across this interesting Open Source project - Jackcess is a pure Java library for reading from and writing to MS Access databases. Also read the FAQ. Also note the link to MDB Tools which hasn’t been updated since 2004.

Jet Engine History

Tony — Sat, 24 Oct 2009 21:01:48 GMT

A question started a bit of digging and poking about. I’ve always been interested in the history of Access and Jet and the code names used way back when such as Cirrus. The Wiki Microsoft Jet Database Engine article had a footnote referencing a page titled Jet Engine: History

Regretfully all the links mentioned on that page no longer work. Although some of the MS white papers mentioned might still be available by digging.

Which reminds me. There are two versions of the Microsoft Jet Database Engine Programmer's Guide. I have the first edition which is the one with the light blue cloud cover which was sort of the theme of Windows 95. I should purchase the second edition which has a white cover just to ensure it’s part of my collection.

And I should spend a few minutes rereading the table of contents just to refresh my memory so if I ever need some information I’ll at least know that it’s available.

Internals viewer for SQL Server

Tony — Fri, 02 Jan 2009 02:32:57 GMT

Internals viewer for SQL Server I always enjoy peeking under the covers. Hey, computer software covers. Get your mind out of the gutter. This blog posting mentions how to poke about under the covers of SQL Server.

Similar to MDB Tools - A set of libraries and utilities for reading Microsoft Access database (MDB) files.

Download the file and open the HACKING file.

The case of the form which took 30 seconds to open

Tony — Tue, 23 Dec 2008 22:36:16 GMT

The client had two transaction file forms which took 30 seconds to open while a similar form took 3 or 4 seconds to open. I went through many of the items on my Microsoft Access Performance FAQ page and none made a significant difference.

The list of items Included "Subdatasheet Name property set to [Auto] should be [None]. " so I'm forced to conclude that while that tip may make a difference when using datasheet view it isn't helpful when using forms.

There are 740K records in the largest two tables of the app. The MDB is about 250 Mb in size.

To simulate their environment I placed the BE on a spare older laptop . I also opened the BE in another instance of Access as performance significantly decreases with the second user into the database. I was then able to get performance times to approximate the client reports.

Note that just opening the form wirelessly caused performance to decrease by a factor of four or five. Although Access frequently crashed. Note that I'm running the 54 Mbps wireless networking version so this number quite surprised me. I'm thinking that the encryption might be the bottle neck there. And is the bottleneck at the consumer grade WAP that I own or in the wireless networking device or the CPU doing the decryption?

In this case the problem was in the form load event somewhere as I determined by putting debug.print at beginning and end of form load. For example:

Debug.Print "BuildAndApplyFilter - " & strFilter & vbTab & Now

Note that you must put the debug.print at the very end of the routines you want to test as well. Otherwise you don't realize that the last line in the routine is the one that's causing the slowdown. <sigh> Ironically that line was commented a year or three ago "'Note: This sub also calls BuildAndApplyFilter - a small performance hit." Yeah, well, it's a small performance hit when the BE is local. A significant performance hit when on the network.

I then sprinkled debug.prints in the VBA code as appropriate to narrow things down.

Turns out the problem was due to a form filter based on an Inactive boolean field on a master Job table which had 792 records. Indexing that boolean field fixed this particular performance problem.

Now given that this forms recordset automatically opens only the last two weeks worth of transactions that field really isn't necessary as a filter. The users may not be that likely to close a job within two weeks of the last time transaction against it. And even if it is closed well, so what. Maybe it's a bit misleading to have some transactions entered yesterday, the job is closed today and now those transactions don't appear on the form. You could argue this case both ways. <smile>

Only the first 12,000 tables appear in the Link Tables dialog box ...

Tony — Wed, 15 Oct 2008 00:18:11 GMT

Only the first 12,000 tables appear in the Link Tables dialog box in Microsoft Access 2000, Microsoft Access 2002, or Microsoft Office Access 2003. Now who the heck would have an application that large? Clearly some organization with a lot of pull at Microsoft to get this problem fixed.

That KB article references an updated version of Jet 4.0 - 4.0.9514.0. Description of the Jet 4.0 Database Engine cumulative hotfix package for Windows XP SP2 and SP3, Windows Server 2003 SP1 and SP2, Windows Vista, Windows Vista SP1, and Windows Server 2008: July 2, 2008. This KB article mentions Access 1.0 and 2.0 problems and ADO records from an Excel spreadsheet as well as the above mentioned problem.

Microsoft Office Binary (doc, xls, ppt) File Formats

Tony — Sun, 17 Feb 2008 02:31:31 GMT

Now isn't this interesting. The Microsoft Office Binary (doc, xls, ppt) File Formats specifications are available for download. Although I suspect the Office 2007 XML based files would be much easier to work with.

Will the MDB/ACCDB format ever be published? All kinds of interesting speculation there but I have no idea

Note that there is a group working on reverse engineering the MDB format. MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data. Or, to put it another way, they are reverse engineering the layout of the MDB file. Download the file and look at the hacking file.

What is annoying to me are the Access data recovery outfits which have copied that hacking file onto their web site with attributing the original creators of that document. See http://www.fdrlab.com/files/accessfileformat.txt and http://www.e-tech.ca/001-AccessFileFormat.asp. This web site copied part of it http://www.filerepair.net/bb/viewtopic.php?p=3&sid=096e617bca1533e1bdf7849e4fc70a69 but then referenced another web site http://www.etechrecovery.com/001-AccessFileFormat.asp.

Resetting autonumber seed to the highest used value

Tony — Tue, 11 Dec 2007 06:42:15 GMT

Executing an append query which has a value in the autonumber field can set the autonumber seed to a unwanted high value. Or maybe you had some code running amok, or amuck if you prefer that spelling, which accidentally inserted a lot of records.

In Access 2000 and newer, or rather Jet 4.0, as well as Access 2007 and ACE, removing the unwanted records and compacting a database doesn't reset the autonumber seed back to the highest used number. The autonumber see is only reset to 0 when you remove all the records in a table. in Access 97, Jet 3.5, the seed was reset back to the highest used value.

(Of course you're going to have to believe me when I say I did a compact in between those two screen shots.)

Of course the purists will state that an autonumber has no real meaning and the users will never see it anyhow. Yes, they have a point but I like being neat and tidy, thank you very much.

See Fixing AutoNumbers when Access assigns negatives or duplicates for ADO code to reset the seed. Note that this is about the only feature in ADO or ADOX that DAO doesn't have when it comes to Access databases. There might be one or two other things but I've never needed them or they might only apply to SQL Server and other databases.

Jet 4.0 hotfix

Tony — Sun, 09 Dec 2007 21:40:31 GMT

Jet 4.0 Database Engine hotfix package - 943509 fixes a number of crashes in Microsoft Access 2002/XP and 2003 as well as other products that use Jet 4.0. (I assume it fixes problems in Access 2000 as well but that article doesn't specifically state that.)

Note though you should carefully examine the event logs of the system(s) which are experiencing problems to ensure this hotfix is applicable to your problem. A hotfix may introduce more problems that it will solve.

You can now request the hotfix from within the KB article by clicking on the links in the Resolution section of the KB article.

Interesting KB article on DAO and Jet DLLs

Tony — Fri, 30 Nov 2007 23:56:24 GMT

There's been an interesting discussion on Jet and DAO dlls in the newsgroups. James A. Fortune, long time newsgroup resident, located some interesting KB articles in the thread titled Jet Database Engine Questions He did located other articles but I thought the following one was the most interesting.

How To: Redistributing DAO with Your Visual C++ 5.0 Application (Note: This includes descriptions of some of the DAO and Jet core files. These are also for Jet 3.5 and DAO 3.5 but still gives you some insight.)

Jet Red vs Jet Blue

Tony — Thu, 29 Nov 2007 01:11:49 GMT

Jet Blue came up as a topic in a newsgroup question today. I felt this was worth mentioning as a useless bit of trivia. <smile>

Very occasionally you will encounter the terms Jet Red and Jet Blue. Jet Red is the version used in Microsoft Access. Jet Blue is used in other Microsoft products such as Microsoft Exchange. For example Extensible Storage Engine and go down to the last paragraph. Also see the Wikipedia entries Microsoft Jet Database Engine and Extensible Storage Engine. Doing a Google search on "jet blue" "Jet red" microsoft brings up some interesting results.

How to get at Jet warnings that are not errors

Tony — Fri, 28 Sep 2007 01:16:55 GMT

How to get at Jet warnings that are not errors by Michael Kaplan. Note the discussion about "There is the Jet 4.0 feature of record level locking that people love ...".

Interested in the bowels of the MDB file layout?

Tony — Thu, 06 Sep 2007 00:39:50 GMT

MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data. Or, to put it another way, they are reverse engineering the layout of the MDB file. Download the file and open the hacking file.

We know that a number of outfits, such as Peter Miller's PK Solutions and other Access repair facilities do understand the layout to some or a great extent. Peter has stated that having an older uncorrupted copy of the MDB, without the data but with the tables if I recall correctly, does help him with retrieving the tables.

If an index is a duplicate of another index then ...

Tony — Fri, 31 Aug 2007 01:00:00 GMT

Visit If an index is a duplicate of another index then Access/Jet does not take any extra space to read the article and download a zip file containing the sample MDB to see for yourself.

I do have a tool available to find duplicate indexes but it needs a few hours of polishing. This is particularly useful if you hit the maximum 32 indexes per Access table and get the following message. "The operation failed. There are too many indexes on table '<table name>'. Delete some of the index on the table and try the operation again." If you are interested in the tool email me and I'll do the polishing.

One feature that DAO is missing - resetting autonumber seed

Tony — Tue, 17 Jul 2007 20:56:33 GMT

Using queries you can insert and delete a dummy record thus resetting the autonumber seed higher. I don't know why you'd want to do this of course as the user should never see an autonumber value.

In ADOX you can reset the autonumber seed value to be anything you want. In Jet 4.0, used by Access 2000, 2002/XP and 2003 this can get occasionally screwed up, especially if you're not up to date in the service packs. You can't get to this value in DAO. But ADOX does allow you to change this value.

Allen Browne has some scenarios and sample code at his page Fixing AutoNumbers when Access assigns negatives or duplicates

When is an autonumber value allocated?

Tony — Tue, 17 Jul 2007 00:59:00 GMT

An autonumber value is allocated as soon as a user hits one character or digit in any field in a new record. If, a millisecond, later another user or program creates another record, even if no data has yet been saved by the first user or program, the next autonumber value will be used.

Create an MDB with one table with an autonumber field and another field such as a text field. Open that MDB from two instances of Access.

In one instance of Access put any character in that field and do not tab or enter in the field. You will see the autonumber set to 1. Notice the triangle in the record selector area on the left hand side of the screen. It will change to a pencil indicating that "You are editing this record; changes to the record aren't yet saved." (from Access 97 help.)

In the other instance of Access against the same MDB also enter a character in the text field. You will note that the autonumber is now 2. And also that the triangle in the record selector has changed to a pencil.

The appearance of the 1 and 2 of course assume that the NewValues property of the autonumber field is set to Increment and not Random.

This is not true when using SQL Server to store your data. SQL Server only assigns an autonumber value when the record is saved. I have no idea about other data storage mechanisms such as Oracle, DB2 or MySQL.

What does DAO have that ADO/ADOx/JRO do not?

Tony — Sat, 14 Jul 2007 23:25:00 GMT

An interesting and still very relevant seven year old article by Michael Kaplan aka michka.
What does DAO have that ADO/ADOx/JRO do not?