Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How are objects stored in Access database

Posted on 2006-07-02
Medium Priority
Last Modified: 2008-01-09
How are forms, reports, pages, macros, and modules stored in Access Database
Question by:chcw
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
  • +5

Expert Comment

ID: 17028590
As database objects.


Accepted Solution

dancebert earned 672 total points
ID: 17029117
Access stores forms, reports, etc in places one can't get to.  It stores high level info about such objects in MSYSObjects.    More at:



Author Comment

ID: 17029883
What's the meaing of those fields and records in MSysObjects?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 58
ID: 17030113
<<What's the meaing of those fields and records in MSysObjects?>>

  MSysObjects has never been documented.  Also, since there are other ways to properly create objects (and as a result create the proper MSysObjects entires), you really don't want to fool around with MSysObjects unless you really need to.

LVL 85
ID: 17030201
Objects are stored in a proprietary format in the database file (i.e. the .mde/.mdb file) ... I have no clue as to exactly which format (since it's never been documented, as Jim points out). The MSysObjects file stores information about those objects, but you couldn't re-create an object based solely on what's in the MSysObjects table. I STRONGLY echo Jim's advice to not fool with MSysObjects - I hate to even query this table, and only do so if absolutely necessary.

You could export an object as a plain Text file, and Access can use this text file to rebuild that object exactly. To do this, use the undocumented SaveAsText and LoadFromText commands:

(from the Immediate Window)
?SaveAsText(acForm, "FormName", "C:\SomePathToSaveTheFile\FormName.frm"

You can then review that file with Notepad, etc ... although this gives little information as to the structure. Instead, my (admittedly limited) understanding is that Access takes this "blueprint" and, using internal (and undocumented) commands, builds a new object.


Expert Comment

ID: 17030639
Be careful using undocumented functions.  Microsoft has openly stated that undocumented functions can change without notice and this can cause your software to crash unexpectedly.
LVL 85
ID: 17030680
True regarding undocumented functions ... I'd never use these in a production environment, but for testing and exploration purposes, I've yet to see any trouble with them.

Author Comment

ID: 17034354
According to our findings on Internet, the objects are saved in MSysObjects, as well as other supplemental system tables, such as MSysQueries. We must know how objects are saved in these system tables. We know they are not documented by Microsoft. But there are still some books, clues, and articles discussing on it, like the one provided by dancebert. We just need to know more.
LVL 58
ID: 17036721
<<We just need to know more>>

  There really is not all that much more.  There is some info in the JET programmers guide, but that's about it.
  You'll have to reverse engineer it yourself.  I would suggest creating a blank database, then one with a table, one with a query, etc with each type of object and then look at the databases with a hex editor.  Then build up from there.

Jim D.
LVL 85
ID: 17036902
<the objects are saved in MSysObjects>

That's not correct - information about the objects is stored in MSysObjects, but not the object itself. Objects are stored in binary format in the database file (the .mdb/.mde file). The System tables (i.e. the "MSys" tables) store information specific to that database about certian objects. Note that these tables are ReadOnly.

Also, the internal structure of Access, and how it stores objects, changes between versions (and sometimes even in SPs of versions). Thus, information you may gather for Access97 wouldn't necessarily be true for Access 2000, etc etc.

You could, of course, use VBA to iterate through the object collections. For example, if you want to loop through the AllForms collection:

Dim frm As Form
Dim i As Integer

For i = 0 To CurrentProject.AllForms.Count - 1
  Debug.Print CurrentProject.AllForms(i).name
  Debug.Print CurrentProject.AllForms(i).Type
Next i

or to loop through the tables:

Dim tdf As DAO.TableDef

For each tdf in Currentdb.TableDefs
  Debug.Print tdf.Name
Next tdf

LVL 58
ID: 17041658
<<That's not correct - information about the objects is stored in MSysObjects, but not the object itself. Objects are stored in binary format in the database file (the .mdb/.mde file). The System tables (i.e. the "MSys" tables) store information specific to that database about certian objects. Note that these tables are ReadOnly.>>

  Hate to disagree with you, but the objects are stored as data within Msysobjects as long binary data.  The tables are read only to anyone other then dbengine.

  Every Access object is stored in the structure of a table one way or another.  Remember that JET only knows how to handle data, not Access objects.  


Author Comment

ID: 17047797

Is " JET programmers guide" a book or a help document?

LVL 58
ID: 17050112

<<Is " JET programmers guide" a book or a help document?>>

  A book.  But it does not cover what your looking for.

LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 total points
ID: 17050611
< Hate to disagree with you, but the objects are stored as data within Msysobjects as long binary data.  The tables are read only to anyone other then dbengine.>

I'm not sure I entirely agree with that, but I'm also quite sure my earlier comment is inaccurate, as you pointed out. You're right - Jet has no idea how to handle objects, and can only handle Data, therefore the objects (or some representation of the object) must somehow be stored in a table - otherwise how could we pickup and move an Access db simply be copying a single file?

According to my copy of the Jet Programmers Handbook (chcw: it's a book put out by Microsoft Press), the MSysObjects table "stores additional information about objects in the database, including tables and queries". After checking the MSysObjects table in a current project, I find several OLE Object and Binary fields, but very few of those fields have data stored in them. Note that this project was a "create new and import from old" type of project, with very few new objects actually added via the database interface.

I DID find, however, that the MSysAccessObjects tables, which has two fields (ID and Data, with ID being a numeric field and Data being a binary field) has information in all Binary fields (but the ID's don't correspond to anything in other System tables). According to Mitch Kaplan, MSysAccessObjects is data "stored in a huge BLOB file in proprietary format." While he never actually states this, the indication he gives is that Access Objects are stored here ... but again, no definitive answers on that.

Just for fun (and since it's raining like crazy here), I decided to see how MSysObjects and MSysAccessObjects react when new objects are added to the db. This was done in Access 2002.

If you build a brand new db, and immediately check the MSysAccessObjects table, 4 records exist. If you check the MSysObjects table, there are several entries in the table, but very few with any data in the OLE or Binary fields. Most of those entries are pretty straightforward, like listings for Forms, Reports, Moudles, Scripts, SysRel, etc. I'd suspect those would be the system collections, but that's pure speculation on my part. There's also listings for each System table. At the initial creation, no values exists in any of the OLE or Binary fields for any of the system tables. However, at some point in my testing, data did appear in the LVProp column for both the MSysOjbects and the MSysAccessObjects row. Further testing revealed that as soon as a query was saved in the db, a value appeared in the LVProp column for MSysObjects ... I couldn't really determine when the value appeared in MSysAccessObjects.

I started adding objects.

If I link a table, I find a new entry in MSysOjbects, with a value in the Connect field, of course, but still no data in the OLE or Binary fields, which would be understandable. I linked appx 30 tables, and no values ever appeared in MSysAccessObjects. I'd suspect that is because the objects themselves don't actually exist in the current database.

If I build a new table, I find a new entry in MSysObjects with values in the LVProp field. A new record sometimes appears in MSysAccessObjects, and in some cases several rows are added. Adding columns doesn't seem to affect the number of rows in MSysAccessObjects. I added 200 rows to one table (a text column with a 255 character string added to every row) and MSysAccessOjbects never added rows, and no changes were made to MSysObjects. Of course, the filesize as reported by Windows did increase as I added the data. This would lead me to believe that either (a) MSysAccessObjects stores on the "definition" of a table, and the table handles the pointers to the data on the disc or (b) Access was "filling in the blanks" with the data. I'd be more inclined to believe (a), since the test db in this case held only 4 rows in MSysAccessObjects. Again, pure guesswork <g>.

If I build a new query, I find a new entry in MSysOjbects with values in the LVProp column, as well as values in the LvExtra column (no other row in MSysObjects ever contained data in this column with one exception - the row named "AccessLayout"). No new records appeared in the MSysAccessObjects table after adding 10+ queries. One oddity - when I added the first query to a database, whether by import or not, a value appeared in the lvProp column of MSysObjects for the row housing info about MSysobjects. At no other time did I see any information in that column, but my testing was decidedly limited.

If I build a new Form, I again find an entry in MSysObjects, with a value in the LVProp field. Sometimes a record appears in MSysAccessObjects. I tried adding various controls to the form, adding a Module to the form, etc etc. In most cases, no additional values appear in MSysAccessObjects. However, when I added a form with an Image control and embedded a 39k .png picture, there were over 1350 rows ADDED to MSysAccessObjects (and there's the bloat we always talk about with embedded images) ...

If I IMPORT a form, however, no values appear in the LVProp field (or any other Binary or OLE field) in MSysObjects. In some cases, a value appears in MSysAccessObjects. In one case, when I imported a form with an embedded image, over 1500 rows were added to MSysAccessObjects (the same form, when added to it's Parent db, added only 1350 rows - why the extra 150+ rows when importing?).

If I add a report, a value always appears in MSysObjects (no values in the OLE or Binary fields, however) but a new row does NOT always appear in MSysAccessObjects. When I imported a Report to the db, several new rows appeared in the MSysAccessObjects table (different amounts each time I imported a report), but only one in MSysObjects (with no values in the OLE or Binary fields). The reports I imported contained no subreports, and were composed only of Textboxes and Labels. In one case, a very small image was embedded on one report, and that report added 13 rows to MSysAccessObjects.

Everytime I add a new Standard or Class Module, a new row appears in MSysAccessObjects, as well as MSysObjects. In no case did any values appear in the OLE or Binary fields in MSysObjects, but my modules were extremely lightweight (just a few test functions with no real code in them). Perhaps a large number of functions/subs would result in some changes to MSysObjects? Perhaps this wouldn't occur as the db grew? Not sure ...

Perhaps the multiple records added to MSysAccessObjects are the 2k pages Access uses to store info? In other words, perhaps a Report I imported needed about 10k of space, so Access allocated 5 of those pages and stored info about them (or the pages themselves) in the MSysAccessObjects table? In one interesting newsgroup posting, Peter Miller of PKSolutions mentions the MSysDB (which is referenced in MSysObjects, but is not accessible through the db window, or via a query, in my testing) as being the "parent" for many of the other system records ... perhaps this table actually stores the Parent/Child relationships of all the bits and pieces needed to recreate an object? Again, speculation on my part ... Perhaps the MSysObjects table stores information about the Object itself, and MSysDB uses that table when gathering the info needed to create the object? The addition of the graphics image resulting in 1300+ rows being added to MSysAccessObjects while only one record was added to MSysObjects would lead me to believe the actual data is stored in MSysAccessObjects, while MSysObjects and MSysDB are the ones doing the "heavy lifting" of recreating the object... again, as always, this is pure speculation.

One thing - as the number of records increase in MSysAccessObjects, it appears that at times adding objects don't always add rows. For example, when starting with a blank slate, adding a form with one textbox always adds a row to MSysAccessObjects ... add a second identical form, and a new row appears ... however, adding a 3rd form doesn't always add a row to MSysAccessObjects. I'd assume this is because Access is "filling in the blanks" with existing pages (again, assuming my concept of the 2k page is correct) and making use of existing space, rather than simply adding on more pages as needed.

Some interesting findings:

At one point, I had 3609 rows in MSysAccessObjects. After a Compact and Repair, I had 3607 rows. In another case, after adding only a few queries and tables, and one module, I had 6 rows in MSysAccessObjects ... after a compact and repair, I was back down to the original 4.

Compiling sometimes adds rows to MSysAccessObjects, but not always. IN one case, I had 1365 rows. After an unsuccessful compile, I had 1372. During the compile, Access found an error on a form (a subform was using a non-existant form as a .SourceObject). After editing that form (removing the subform entirely) and re-compiling, I had 1375. Perhaps the errors had something to do with this? Not sure, but Mitch Kaplan has stated that Access can be in "several states" of compilation, and the only way to really strip this out is by importing to a new db (or using decompile and then an immediate compile).

Decompiling sometimes adds rows to MSysAccessObjects, but not always. In one case, I had 1372 rows. After decompiling, I had 1373 rows. After a compact/repair, however, I ended up with 1365.

In no case of C&R or Compile/decompile was MSysObjects affected, either in number of rows or in values appearing in columns. Of course, the binary data stored in some of the MSysObjects columns could have been affected, but we'll never know that.

Of course you may get completely different results on your own machine, so take this for what it is - an exercise in learning.
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 664 total points
ID: 17051018
<<I'm not sure I entirely agree with that, but I'm also quite sure my earlier comment is inaccurate, as you pointed out.>>

  I'm not entirely sure what the mix is between MSysObjects and MSysAccessObjects or what MSysAccessObjects actually stores, but I'm sure some of the object is stored in MSysObjects.  MSysAccessObjects is a latter addition, which was added starting with A2000.

  Prior to that, it was not part of Access.  Access relied on MsysObjects and MSysModules to contain the majority of the object data (there are other system tables used for things such as import/export specs, but for forms and reports, which we’ve been mainly discussing that was it).


  BTW, nice footwork.  I want to read through that a few more times to make sure I understand everything you did.
LVL 85
ID: 17051501
Right, I should have specified this was A2000.

In A97, when you add a form to a new, blank db, values appear in the MSysObjects table in the Lv, LvExtra, and LvModule columns. Those columns are present in A2000, but I saw no data appear in the Lv and LvModule columns. Of course, at some point in the apps lifecycle these may be used; the tests I did were with very small databases with little data and objects stored in them.

Perhaps this is why you could modify objects in a 97 db while it was in use, but not in 2000? Not sure ...
LVL 58
ID: 17052199
<<Those columns are present in A2000, but I saw no data appear in the Lv and LvModule columns. Of course, at some point in the apps lifecycle these may be used; >>

  Sounds like they moved where the data was stored.  MSysObjects may at this point be nothing more then a pointer table, in which case I would be wrong.

  I think my premise of that thinking that it must remain the way it was in older versions is probably invalid.  The thinking in the Access community at one point was that you could rely on what was in MSysObjects because if MSFT changed it, it would break backwards compatibility.  But after considering that again, that doesn't imply that newer versions can't do things differently, especially at the level where talking about.

  The most extensive use of MSysObjects I've seen is to get a list of objects within the database like the DBC does and that has remained unchanged.

LVL 58
ID: 17052202

  BTW, one thing I think we have shown is that it is not going  to be a trival task to understand the inner workings of Access objects.

LVL 85
ID: 17053313
<BTW, one thing I think we have shown is that it is not going  to be a trival task to understand the inner workings of Access objects.>

Very true ...

My research has indicated that the internal structure changes with each version, but that the most striking changes (as we all know) came with Access 95 and then again with Access 2000. The basic structure of the MSysObjects table appears to be unchanged since Access 2000, even when using an Access 2002 or 2003 format, however how Access handles/stores those objects changes with each version - which would be logical, since they're always adding new features to Forms, Reports, etc.

All in all an interesting bit of discovery ... and what's even more interesting is to perform the actions I describe in my lengthy comment, and then reviewing the db with a hex editor after each action. You can see what's happening, and - at least to my way of thinking - this is the only way you'll ever really get in-depth knowledge about the inner workings. Each "page" appears to have a header which contains info about the objects stored within it (seems the first 256 bytes are the most relevant in that regard) ... I haven't sorted it out, nor do I want to take the time to do so, but if I were looking to find out about the inner workings of Access that's where I'd start anyway.
LVL 85
ID: 17053346
Sorry, I meant to say that the basic structure of the MSysObjects table has remained unchanged since Access 97 (or possibly even 95 - I don't have a copy laying around, so can't examine an MSysObjects table from that vintage)  ... I believe what changed is the way the Access team uses it.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17069883

<We must know how objects are saved in these system tables>
<We just need to know more>

Ummm... Why?

Just for you own curiosity, I hope?
I, Like LSMConsulting, played around with this table, "Just for fun", but like everyone has stated, the info you are searching for is largely undocumentated.

I don't think anyone here would reccomend "tooling" around with these tables/Objects.

However it seems like LSMConsulting has just provided us with more info on this subject than many of us could find online!

(Thanks LSMConsulting)

I guess if you need to know more than this, you should contact Microsoft directly.

But again....Why?
LVL 27

Expert Comment

ID: 17256378
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: dancebert {http:#17029117} & LSMConsulting {http:#17050611} & JDettman {http:#17051018}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question