Solved

How are objects stored in Access database

Posted on 2006-07-02
23
2,148 Views
Last Modified: 2008-01-09
How are forms, reports, pages, macros, and modules stored in Access Database
0
Comment
Question by:chcw
  • 7
  • 7
  • 3
  • +5
23 Comments
 
LVL 8

Expert Comment

by:infolurk
ID: 17028590
As database objects.

Cheers
Steve
0
 
LVL 9

Accepted Solution

by:
dancebert earned 168 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:

http://www.perfectparadigm.com/tip001.html



0
 
LVL 8

Author Comment

by:chcw
ID: 17029883
What's the meaing of those fields and records in MSysObjects?
0
 
LVL 57
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.

 JimD
0
 
LVL 84
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.

0
 
LVL 5

Expert Comment

by:MageDribble
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.
0
 
LVL 84
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.
0
 
LVL 8

Author Comment

by:chcw
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.
0
 
LVL 57
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.
0
 
LVL 84
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



0
 
LVL 57
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.  

JimD.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 8

Author Comment

by:chcw
ID: 17047797
JDettman:

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



0
 
LVL 57
ID: 17050112

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

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

JimD
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 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.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 166 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).

JimD.

  BTW, nice footwork.  I want to read through that a few more times to make sure I understand everything you did.
0
 
LVL 84
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 ...
0
 
LVL 57
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.

JimD.
0
 
LVL 57
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.

JimD.
0
 
LVL 84
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.
0
 
LVL 84
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17069883
chcw,

<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?
0
 
LVL 27

Expert Comment

by:jjafferr
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.

jjafferr
EE Cleanup Volunteer
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now