How are objects stored in Access database
How are forms, reports, pages, macros, and modules stored in Access Database
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What's the meaing of those fields and records in MSysObjects?
<<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
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
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\
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.
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\
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.
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.
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.
ASKER
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.
<<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.
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.
<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.Co
Debug.Print CurrentProject.AllForms(i)
Debug.Print CurrentProject.AllForms(i)
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
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.Co
Debug.Print CurrentProject.AllForms(i)
Debug.Print CurrentProject.AllForms(i)
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
<<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.
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.
ASKER
JDettman:
Is " JET programmers guide" a book or a help document?
Is " JET programmers guide" a book or a help document?
<<Is " JET programmers guide" a book or a help document?>>
A book. But it does not cover what your looking for.
JimD
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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 ...
<<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.
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.
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.
<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.
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.
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.
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?
<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?
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
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
Cheers
Steve