First Published 27 Nov 2023 Difficulty level : Moderate
Section Links: (this page)
Long Value Fields
The MSysObjects Table
The MSysNameMap Table
The MSysAccessStorage Table
Background Information Return To Top
Some time ago, I published the first two articles in this series about the Purpose of the System Tables automatically created by Access.
As I wrote before, with a few exceptions, there is very little information available online about most system tables.
One of the main exceptions is the MSysObjects table which is both well known and extremely useful for experienced developers.
It is important to stress one of the main reasons for the lack of information.
System tables are used by Access to make databases function correctly.
Some system tables can be viewed and a few can be edited..
But that doesn't mean you should do so . . . UNLESS YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING..
Altering one table may have 'knock on' effects on other tables
Incorrectly editing system tables may corrupt your database or prevent you opening it
Anyway, having made that point, I'll continue . . .
This article was partly prompted by a recent thread by Nautical Gent (John Clark) at Access World Forums:
What System or Hidden table(s) (if any) holds the properties for the current db?
The article expands upon my response in post #11 of that thread and provides additional and background information.
Long Value Fields Return To Top
Several system tables contain one or more fields of OLE Object datatype usually with names starting Lv (Long Value).
Their purpose is even more obscure as the data in those fields is very difficult to read.
The following is a summary of what I've been able to deduce based on my research into the various tables & fields.
The fields are often empty but where they do contain data, this is displayed as Long binary data.
Access typically displays the text Long Binary Data in a field when it contains a BLOB (Binary Large Object).
A BLOB can be any type of binary data, and is often a copy of a file, such as a JPEG photo or other image file.
The BLOB field contains a Byte-for-Byte copy of the file, so it can easily be extracted back to the file system resulting in an identical copy of the original file.
However, in system tables, the Long binary data is text, not all of which is human-readable.
The MSysObjects table Return To Top
This table contains a list of database objects and their properties.
The screenshot below is taken from the MSysObjects table and shows the Lv fields for a typical query after conversion into a partly readable format:
Slightly more useful is the result after exporting the modified Lv field to a text file
Although much of the data is incomprehensible, the text file clearly shows the fields used in the query:
SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags, MSysObjects.Lv,
MSysObjects.LvExtra, MSysObjects.LvModule, MSysObjects.LvProp
This is the LvExtra field data for the same record and, apart from the table name, contains no legible information.
The LvModule field is empty but the LvProp field includes a list of the query properties
Similar results are obtained by examining the Lv fields output for other database objects.
However, there is nothing here of use to developers that cannot be found more easily by other means.
As well as database objects, the MSysObjects table contains 9 object containers, all with Type = 3.
All the Lv fields are empty for each object container record
Finally, the MSysObjects table contains several items which define properties of the database itself
Taking these in turn:
a) MSysDb has an LvProp field
This stores the database properties and values as shown in the Database Documenter
b) Admin has an LvExtra field - this stores the contents of the Relationships window
NOTE: This record is only created once the Relationships window has been opened
c) SummaryInfo has a LvProp field
This contains the Database Info as entered in the File . . . Backstage view
d) UserDefined has a LvProp field. This data appears to be used for replication.
e) AccessLayout is hardly ever present but, when it does exist, it has data in two Lv fields (LvExtra / LvProp).
So far, with only limited evidence available to me, I have been unable to determine its purpose.
Although the contents of these database properties are of more interest, once again there are better ways of viewing this data. For example:
i) The properties of the database (and the database objects) can be viewed using the built-in Database Documenter tool
ii) The Relationships window data can also be viewed by examining the MSysRelationships system table. See my article Detailed Relationships Analysis
Each of these items can also be obtained using code and are amongst the many items available using my Database Analyzer Pro application.
Below is a brief summary of info from two of the other system tables with Long binary data
The MSysNameMap table Return To Top
The NameMap field contains data used by the Name AutoCorrect feature to track object dependencies
Once again, the long binary data is unreadable apart from object & field names.
It is far easier to track this feature using the Object Dependencies tool. It can also be done in code as in my Database Analyzer Pro app.
The MSysAccessStorage table Return To Top
The contents of the MSysAccessStorage system table are generally rather obscure to most developers
It is unsurprising that its Lv field is usually largely unintelligible.
This may well be the sole exception . . .
Summary / Conclusions Return To Top
The long binary data in the various 'Lv fields' contains a lot of important data used by Access to manage the database and its objects.
The information is not officially documented by Microsoft and I was unable to find much of the above anywhere else online.
I originally researched this topic as I hoped to be able to make use of it directly.
However, I found that the data is difficult to read or use.
In a few cases, Access provides tools to allow you to view the data in a readable format.
It is also possible to view much of this data using code though in some cases the code is also rather obscure.
See my Database Analyzer Pro app for many examples of using code to obtain database information.
Download Return To Top
If you want to know how the Long binary data can be viewed, you can download the example database from which the above screenshots were obtained.
Click to download: ViewLvFields_v1.2 0.8 MB ACCDB file (zipped)
Feedback Return To Top
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 27 Nov 2023
Return to Access Articles Page Return to Top Page 3 of 3 1 2 3