First Published 27 Nov 2023                                                                   Difficulty level :   Moderate

Section Links: (this page)
          Background Information
          Long Value Fields
          The MSysObjects Table
          The MSysNameMap Table
          The MSysAccessStorage Table
          Summary/Conclusions
          Download
          Feedback


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.


NOTE:
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
FROM MSysObjects
WHERE (((MSysObjects.Name)="Admin"));

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