Chinese - Simplified   Dutch   English - United States   French   German   Portuguese - Iberian   Spanish  


Usenet Posting #17 - Trigeminal Software, Inc. (English)

Subject: INFO: Can you rely on system tables? How about DAO?
(Originally posted 10/6/99)


This is just a simple info post, but I thought I should address a question that is asked all the time. Should you rely on system tables in Microsoft Access when you do your work. This question is especially important in replication since outside of the TSI Synchronizer, the system tables are the only way to get some info and I am actually writing a series of articles for Access/Office/VB Advisor on using the replication system tables, but it can also be important for other needs (Ken Getz and Mike Gilbert wrote an excellent 3-part series of articles for Informant's Microsoft Office Developer on parsing MSysQueries, for example).

Well, in an important conversation with Kevin Collins, a program manager for Microsoft Jet, he unequivocally that the Jet team takes the TCO (total cost of ownership) initiatives of Microsoft very seriously, even going so far as to state that they would not have changed the file format at all if they had not been required to do so for the move to Unicode (which was important for another of Microsoft's initiatives, that of international support, especially with all of the new "Unicode-only" languages).

Therefore, in order to meet the TCO guidelines, all previous versions of the Jet engine must be able to read all future versions. In simple terms, this means that Jet is not allowed to change the file format, or make any changes at all that would cause any Microsoft app written against a feature in an older version of Jet to be broken when using a newer version of Jet. This means that as long as *somebody* in Microsoft relies on a feature in a system table, they cannot change the system table!

This also means that while it is clear that ADO and the Jet OLE DB provider are what they intend to use for the future of access to Jet databases, and while it is unlikely that there will ever be more than a "fix crashing bugs" type point release of DAO, it is now set in stone that DAO 3.6 will work against any database created in any future version of Jet, major or minor. You won't get new features added elsewhere, but you don't need to worry about converting existing code running against Jet databases using DAO 3.6/Jet 4.0. As long as there is a Jet engine, DAO 3.6 will run against it.

What does this mean? Well, in practical terms the information and wrapper classes Ken and Mike wrote for Informant is now set in stone as a way to parse queries that will not break in future versions, because if you gave someone a Jet 8.0 (hypothetical version number to prove a point) database to use and all you have is Jet 4.0, that Jet 4.0 database must be able to read the Jet 8.0 database. And if they changed the format of MSysQueries it would not work.

Similarly for replication, the Conflict Viewer and the code in msrepl40.dll depends on the schema of the replication system tables. So they cannot be changed in any way that could possibly break the prior version of Jet when trying to read databases created in Jet 6.0 or whatever.

So, where will we see a Jet 6.0 or Jet 8.0? Who knows? I am not a fortune teller, I doubt Microsoft even has an answer for *that* question. The point of this post is that all previous advice about "not guaranteed to work in future versions" in relation to the Jet system tables is now null and void.

But be careful to make sure of the following: would the change break the old version? I'll run through a few simple cases to help you determine if your dependency is one that would be allowed to break or not.

  1. Lets say that you have an application that depends on how many columns are in the MSysQueries table. Can you rely on this? The answer is NO, because Jet could add a column and it would not break the existing Jet 4.0.
  2. What if your application depends on the datatype and value in a particular column? Can you rely on this? The answer is YES, because Jet 4.0 changing such a thing would break Jet 4.0 trying to open this database.
  3. *What if your application depends on features that are NYI (not yet implemented) such as the Null value of the "InvolvedObjects" column in MSysConflicts? Can you rely on this? The answer is NO, because no one in Jet or elsewhere currently depends on this value. Hopefully they will make this do something in future versions.

So, if you are using such a feature in a Jet system table, run it through this simple test and determine whether or not it is something safe to use in future versions. The new situation is best put as saying that using Jet system tables is still unsupported, but that for unrelated (TCO) reasons will continue to work against future versions of Jet.

Back to Usenet Musings

Problems with this site? Please contact the
with your comments, questions, or suggestions.