2. A grouping and the description of objects of a DB
2.1. A grouping of objects
Click the right button on object of a DB (the table, the form the report, etc.) and choose in the contextual menu of "Property". In the appeared window a field "Description" at beginning developers most likely empty. Though this comment would be completely not superfluous. If to choose a kind of display of objects "Table" the column "Description" in which the characteristic of object (if "Description" of property of object is filled) is displayed will be shown. At their big quantity(amount) the brief information facilitating understanding of their purpose(assignment,destination) would turn out. Besides by pressing heading of a column there is a sorting on increase. And if descriptions are made on the certain system of a designation – would receive a corresponding GROUPING. Thus, it is possible to create complex groups of objects, using only system of their designation and the description, that considerably will simplify navigation in the project, especially, if objects there not one ten.
One more way of a grouping of objects on what or to an attribute – use of built in groupings Access (the right button on object – to add in group). Thus objects are marked as belonging to the chosen group which list is shown in the bottom part of the panel of navigation. This way, the truth, is used not so often, probably because for necessary understanding of purpose(assignment,destination) of object it is enough to set to it(him) a correct name and the description according to own system of designations.
2.2. Comments in the table
Beginning developers often do not use very useful attribute of the table – an opportunity of drawing up of the description to each field of the table. Though it in many respects facilitates understanding what for for what and what role plays this field in a database. Besides at creation of forms and an insert of fields from the list this text automatically is inserted into property of a field «StatusBarText» – the text of a line of a condition (in the bottom part of a window of the appendix or the document and serves for display of helps about commands and buttons and data on carried out operations).
2.3. Reception of the report – information on a DB
So, if to use the intelligent names of tables, fields, their descriptions, comments of fields of tables, and it is desirable for all this to do according to the certain system of designations – that as a result we shall receive volumetric enough "information" on the created database. How to look "alive", or, easier speaking, to unpack?
The basic information on objects of a database is stored(kept) in special service tables: Service – Parameters – Wkl a kind – a daw: System objects. As a result in a window of the project of "Table" we shall see new objects with names: MSysAccessObjects, MSysAccessXML, MSysACEs, MSysObjects, MSysQueries, MSysRelationships. In them the information on « history of a life » objects, their basic properties contains. That users "have not jammed" that does not follow, them have made hidden, and the some people in general have closed from direct access (MSysACEs).
For example, in table MSysRelationships it is possible to read through directly names of objects and their description, and by means of simple inquiry to receive the necessary parameters – for example: a name of object + the description.
SELECT MSysRelationships.szObject, MSysRelationships.szReferencedObject
FROM MSysRelationships;The most important among them - MSysAccessObjects. We shall consider it more in detail (some descriptions are lowered as exact documentation on this question to find difficultly)
Name of a field
The description
Connect
Field МЕМО For liks NOT Access tables in this floor value of property Connect
Database
Field МЕМО For links tables in this floor a full way and a name of a file, whence there was прилинкована a table
DateCreate
Date/time Date and time of creation of object
DateUpdate
Date/time Date and time of updating of object
Flags
Numerical
ForeignName
Text (255) For links tables in this floor a "real" name of the table
Id
Numerical (Long Int) Key field. Contains the unique identifier for each object of a DB
Lv
Field of object OLE
LvExtra
Field of object OLE
LvModule
Field of object OLE The compiled text of modules of a DB (assumption)
LvProp
Field of object OLE
Name
Text (255) Name of object of a DB
Owner
Binary (255)
ParentId
Numerical (Long Int) Prim.key Value id parental object
RmtInfoLong
Field of object OLE
RmtInfoShort
Binary (255)
Type
Numerical (Integer) Type of object of a DB
Here some values of parameter Type:
Type of object of a DB
Value of field Type
"Native" tables (including system)
1
Global "families" of objects (Relationships, Databases, Tables, Modules, SysRel, Scripts, Forms, Reports)
3
Inquiries (including what actually are SQL-expressions in sources of the given forms, reports, elements of management, etc.)
5
Liks tables
6
Forms
-32768
Reports
-32764
Macros
-32766
Modules (including modules of classes)
-32761
As a result it is possible to obtain following data:
That we can receive
SQL
The list of "native" tables
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"USys"));The list of the attached tables
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=6) AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"USys"));The list of the "native" and attached tables
SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=6) AND ((Left([Name],4))<>"MSys" And (Left([Name],4))<>"USys"));The list of inquiries
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],4))<>"~sq_") AND ((MSysObjects.Type)=5));The list of forms
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768));The list of reports
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764));The list of macroowls
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32766));The list of modules
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32761));The list of the forms having expression SQL in a source
SELECT MSysObjects_1.Id AS [Id Форм], Right([MSysObjects]![Name],Len([MSysObjects]![Name])-5) AS [Имя формы], MSysObjects.Id AS [Id Запроса]
FROM MSysObjects, MSysObjects AS MSysObjects_1
WHERE (((Right([MSysObjects]![Name],Len([MSysObjects]![Name])-5))= [MSysObjects_1]![Name]) AND ((Left([MSysObjects]![Name],5))="~sq_f") AND ((MSysObjects.Type)=5) AND ((MSysObjects_1.Type)=-32768));The list of the reports having expression SQL in a source
SELECT MSysObjects_1.Id AS [Id Отчета], Right([MSysObjects]![Name],Len([MSysObjects]![Name])-5) AS [Имя отчета], MSysObjects.Id AS [Id Запроса]
FROM MSysObjects, MSysObjects AS MSysObjects_1
WHERE (((Right([MSysObjects]![Name], Len([MSysObjects]![Name])-5))=[MSysObjects_1]![Name]) AND ((Left([MSysObjects]![Name],5))="~sq_r") AND ((MSysObjects.Type)=5) AND ((MSysObjects_1.Type)=-32764));The list of elements of management of the forms having expression SQL in a source
SELECT MSysObjects_1.Id, Mid([MSysObjects]![Name],6,InStr(2,[MSysObjects]![Name],"~sq_c")-6) AS [Имя формы], Mid([MSysObjects]![Name],InStr(2,[MSysObjects]![Name],"~sq_c")+5) AS [Имя элемента]
FROM MSysObjects AS MSysObjects_1, MSysObjects
WHERE (((Mid([MSysObjects]![Name],6,InStr(2,[MSysObjects]![Name], "~sq_c")-6))=[MSysObjects_1]![Name]) AND ((Left([MSysObjects]![Name],5))="~sq_c") AND ((MSysObjects.Type)=5) AND ((MSysObjects_1.Type)=-32768));The list of elements of management of the reports having expression SQL in a source
SELECT MSysObjects_1.Id, Mid([MSysObjects]![Name],6,InStr(2,[MSysObjects]![Name],"~sq_d")-6) AS [Имя отчета], Mid([MSysObjects]![Name],InStr(2,[MSysObjects]![Name],"~sq_d")+5) AS [Имя элемента]
FROM MSysObjects, MSysObjects AS MSysObjects_1
WHERE (((Mid([MSysObjects]![Name],6,InStr(2,[MSysObjects]![Name], "~sq_d")-6))=[MSysObjects_1]![Name]) AND ((Left([MSysObjects]![Name],5))="~sq_d") AND ((MSysObjects.Type)=5) AND ((MSysObjects_1.Type)=-32764));So, if to rummage in structure of tables and to make the necessary inquiries – we shall receive sources of data necessary for reports. However, some data are stored in a binary kind, and it is necessary to use special procedures, for their reading. And some parameters are not present in tables – they can be read through, only having addressed to property of object. But it is possible to take advantage of one of ways:
1. Service – the analysis – the registrar
As a result the report on a database is automatically created. But it, unfortunately, is not legible and contains much not the information necessary in most cases. Therefore, it is better to take advantage of the special procedure reading properties of objects of a DB and inserting them in specially created service tables.
2. Special procedure of reading of properties of objects of a DB
Here an example of procedure with Hiprog.com.At its(her) performance there is a following:
- Creates the table ~TBL with the list of tables of base (except for MSys *) with their basic properties
- Creates the table ~FLD with the list of fields of tables of base
- Creates the table ~PRP with the list of properties of fields of tables of base
- Establishes communications between tables ~TBL, ~FLD, ~PRP
As a result we shall receive the whole base of properties of our DB, whence without effort it is possible to deduce all necessary information in the form of reports or summary tables. Received "information" becomes rather useful appendix considerably facilitating understanding structures of base, purpose of objects.
The author: Admin
It is added: 14.04.2008