OLE object field(Object Linking and Embedding) - the last field type selected from the list. It has only two properties: a signature and a "required field" parameter. fields of this type do not store information as such, but contain references to objects that can be included in the database using the OLE data exchange protocol. Such objects can be, for example, graphic files. Even the best DBMS is not able to provide storage of all types of information representation that exist today. Yes, this is not necessary. It is enough to communicate with an application that works with one or another type of file. That's what the OLE protocol is for.
In order to embed an OLE object into a field cell, place the cursor on it and, by clicking right button mouse, call context menu. In it, select the command Insert object. The program will open a dialog box to select the object type. Object in this case is identified with the application in which it can be created or edited. In the case when the switch is set in this window Create new, the corresponding application opens, which is slightly different from the usual one. Thus, the user does not have the ability to open an existing file in order to make it an object. For example, if selected word document And finished document already exists on disk, you will have to open it in an editor separately, copy it to the clipboard, and then go to the document window associated with Access and paste the contents of the clipboard. If the object is created, as they say, "from scratch", then work in the selected application occurs in the usual way. The command to exit the application is also modified. In this case, it is no longer possible to close the application and end up in Windows, but you can only return to Access.
The second option for inserting an object - selecting a radio button Create from file in the window Inserting an object. In this case, it is assumed that the file already exists, and you need to link to it. Access will open a window for opening a document, and after selecting the desired file, it will determine which application must be opened to work with it. Keep in mind that more than one application can correspond to the same file type. For example, files with the BMP extension are used by various graphic editors. Selected access application starts, and it opens the file selected by the user. Object injection ends when you close the program.
Entering and editing data in the "Table" mode
The DBMS enters the Table mode when the button is pressed. The table window uses special characters to display the new and currently edited record.
Fig.5. Table window in Table view
Each table contains an empty entry that follows the last one. an existing record and is intended for entering new data. To enter, place the cursor in the table field and type data on the keyboard. To move to the next field use the TAB key, to the previous field - SHIFT+TAB. The record is automatically saved when switching to another record (the record marker along the left border of the table changes shape: a triangle appears instead of a pencil), that is, no special saving of data in the table is required. You can use to navigate between entries. on-screen buttons at the bottom of the table window (to the first, next, last record).
Some fields cannot be changed: these are "Counters", calculated fields and fields locked by another user when collaborating with one table. If the database was opened in read-only mode, then data entry is also not available.
data editing includes the following basic operations: replacing data values, deleting records, adding records. To correct data in the "Table" mode, "manual" methods are used: the mouse cursor is brought to the place to be changed in the table, a fragment is selected (a separate field, record or column) and new data is entered, data is inserted from the buffer or data is deleted using the DELETE key.
Ways to select fragments:
Field stands out double mouse click,
Recording(line) is selected by clicking on the empty gray cell near the left border of the window,
Column highlighted by clicking on the column heading,
Rectangular fragment: select a field in one corner, press the SHIFT key and, without releasing, select a field in the opposite corner (or by dragging the mouse).
Whole table is selected by clicking on the gray rectangle in the upper left corner of the table.
To copy data from one table to another (or to the same table) the Windows buffer is used:
Select the required data in one table;
· click the "Copy" button on the toolbar;
go to the destination field (by pointing to it with the mouse or by hovering the cursor with the TAB keys),
· Click the "Insert" button on the toolbar.
The result of filling in the table, where the type of object is indicated in the corresponding field - bitmap. To view an embedded object, just place the cursor in the appropriate field and double-click.
To display the contents of the field as an icon representing a file with a document, in the window (Fig. 3.20), select the checkbox as an icon(Display As Icon). An icon can be used to represent related object.
Entering Logically Related Records
Let's introduce several logically interconnected records in the group and STUDENT tables.
The GROUP and STUDENT objects are linked by one-to-one relationships, but until a data schema is created that establishes relationships between tables, the system cannot control the logical relationship of the input data. Therefore, in order to obtain a complete database in which all records of a subordinate table have a master record logically associated with it, the user needs to track the logical relationships of the records himself. When entering a subordinate record into the table, the student needs to check for the presence of a record in the main group table with a key whose value matches the value of the link field (foreign key) of the entered subordinate record. That is, when adding a record about a student of group 221, it is necessary that the group with this number is already represented in the GROUP table.
When directly entering records into a table that are logically related to records in another table, it is useful to display both tables on the screen (Fig. 3.21).
To simultaneously display open tables, you can use the command Top down or From left to right on the menu Window.
One way to enter sub-records only when there is a related record in the main table would be to use a combo box in the sub-table that includes the values of the main table field. Let's create in the student table for the field NG (group number) a field with a list built on the basis of the group table key. To do this, open the table I student in design mode. Place the cursor in the NG field on the Data Type column, open the list and select the row Substitution Wizard(see figure 3.5). In the wizard window that opens Create a substitution check the box The "lookup column" object will use the values from the table or query. This will build a list based on the values from the table.
In the next window of the wizard, select the GROUP table, on the values from which the field list will be built. Next, select the column from whose data the list will be formed.
In the following windows of the wizard, select the desired width of the list column and its caption. This completes the creation of the combo box wizard. The list options prepared by the wizard are displayed in the properties window of the NG field on the tab Substitution.
Now, when entering data into the student table, you can use the list, which will display all the values of the NG field contained in the group table. Entering a value in the NG field is done by selecting the desired value from the list. The presence of a combo box does not prohibit the entry in the NG field of values that are not specified in the list. However, if, when defining list options in the design window, in the section Field properties tab Substitution select for property Limit to list If set to Yes, then entering values not provided for in the list will become impossible. The system will track this situation and, in case of deviation from the values of the list, will issue a message, shown in Fig.
Thus, the user will be forced to adhere only to the values presented in the list, and only related records will be entered into the subordinate table. Thus, the use of lists not only provides convenient input, but also avoids many mistakes.
Let's look at another example of using a combo box. Let's transform the study table field KP - subject code into a combo box. To do this, open the table in design mode, place the cursor in the KP field on the column Data type, open the list of this column and select the row Substitution Wizard. In the dialogue with the wizard, we select the main table in relation to the study table, the subject on the basis of which the list is created, and the fields included in the list: CP (subject code) and NP (subject name). In addition, let's leave, as suggested by the wizard, marked with the checkbox. The result of the wizard is shown on.
If you check the box Hide key column (recommended), then the width of the first column of the list is set to zero. At the same time, the property Limit to list can take only one value - Yes.
Defining a combo box with such parameters leads to displaying in the field of the table CP instead of the values of the key field of the values of the second field of the list - the field TM (name of the subject). On fig. 3.30 shows how the STUDY table displays the combo boxes Subject Code and Personnel № teacher with zero-width attached column.
Thus, the use of combo boxes will help to avoid errors when entering related records into tables, both due to the fact that semantic values can be displayed in tables instead of keys, and due to the fact that the input is limited to a set of values in the list.
Attention!
When you create combo boxes, the lookup wizard automatically creates a relationship between the table in which the combo box is created and the table that is the source of the list values. This relationship can be viewed in the Data Schema window by clicking the Show All Relationships toolbar button.
Obviously, in a database of complex structure, when data is entered directly into tables, reliable and correct data maintenance is not guaranteed. In the considered example of the database Educational process, when entering data into tables of lower levels, it is necessary | track multiple upstreams. With a large amount of data, this is very difficult.
IN DBMS Access the process of creating a relational database involves creating a data schema. Data Schema visually displays the logical structure of the database: tables and relationships between them, and also ensures the use of the relationships established in it when processing data.
For a normalized database based on one-to-one and one-to-one relationships between tables, in the data schema for the relationships of such tables by primary key of the main table or by a unique index, provisioning parameters can be set cohesive integrity.
When maintaining the integrity of related data, it is not allowed to have a record in a child table if there is no record associated with it in the main table. Accordingly, during the initial loading of the database, as well as updating, adding and deleting records, the system allows the operation to be performed if it does not lead to integrity violations.
Relationships defined in the data schema are automatically used to combine tables when developing multi-table forms, queries, reports, greatly simplifying the process of their construction.
The data schema in Access is not only a tool graphic display logical structure of the database (see Fig. 2.16), it is actively used by the system in the process of working with the database. In any data processing from several interrelated tables, the system uses the links between the tables stored in the data schema, freeing the user from the need to specifically inform the system about the presence of a particular link when constructing multi-table objects. Once the links specified in the data schema are used by the system automatically.
A relational database created according to the design of the canonical data model consists of normalized tables. In such a database, there is no duplication of data in interrelated tables and, accordingly, the amount of stored data is minimized. In the process of loading and updating the database, obtaining information on queries and outputting reports, as well as solving most tasks, simultaneous access to several interconnected tables is required. Creating a data schema simplifies the design of multi-table forms, queries, reports, and data access pages and ensures that integrity related data when adjusting tables.
When a user creates an Access data schema, it defines and remembers relationships between tables. Based on interrelated tables, the system automatically combines data to create forms, queries, reports, data access pages. The database schema is displayed graphically in its own window, where tables are represented by lists of fields, and links are lines between fields of different tables.
One-to-many (1:M) or one-to-one (1:1) links
The data schema is primarily focused on working with tables that meet the requirements of normalization, between which one-to-many (1:M) or one-to-one (1:1) relationships can be established. For such tables, cohesive integrity can be automatically maintained. Therefore, it is advisable to build a data schema in accordance with the information-logical model.
When building a data schema, Access automatically determines the type of relationship between tables based on the selected link field. If the field on which you want to establish a relationship is a unique key in both one and the other table, Access detects the relationship one to one. If a link field is a unique key in one table (the master link table) and in another table (the link child table) it is not a key or is part of a composite key, Access detects the relationship one-to-many between the records of the main and subordinate tables. Only in this case it is possible to set the automatic maintenance of the integrity of links.
Comment
If a unique index is used instead of a key field to link tables, the system also states that there is a 1:M relationship between the tables and allows you to set the parameters for ensuring integrity.
Association-associations
Between two tables with the same field, a join relationship can be established on this field, called communication key. For a join relationship, one of three ways to join table records can be selected:
To establish a link, any of the listed methods of joining can be selected, regardless of the relationship between the linked tables: 1:1, 1:M, or the type of relationship cannot be determined by the system. For example, when you select a non-key field or a field in a composite key as a link field in the main table, Access reports that the relationship type cannot be determined. But in this case, it is also possible to establish a connection-join of any type between the tables. When a relationship is defined between two tables in a data schema, a join relationship of the first type is established between those tables by default, regardless of the type of relationship.
A union relationship provides a union of table records that have the same values in the relationship field. Moreover, provided that the values in the link field are equal, each record from one table is combined with each record from another table. In addition, if the second or third join option is selected, then it also includes records from the subordinate table for which there are no logically related records in the main table. The last two options are often necessary in solving practical problems.
When creating a data schema, the user includes tables in it and establishes relationships between them. If the linked tables are in a 1:1 and 1:M relationship, you can select the check box to enforce linked data integrity and automatically cascade updates and delete related records to establish the link.
Ensuring linked data integrity means that when you adjust a database, Access makes sure that related tables meet the following conditions:
If the user tries to violate these conditions in adding or deleting records or updating key data in related tables, Access displays an appropriate message and prevents the operation from being performed.
Table CHAIR provided PHOTO, which should contain a photo of the head, stored in the format graphic editor Paint in a *.bmp file. The data type of such a field must be defined as An OLE object field. This object is introduced into the field at the stage of filling the table fields.
Table 2. Description of the properties of the fields of the DEPARTMENT table
Table 3. Description of the properties of the fields of the TEACHER table
Using data type - Memo field
Table ITEM provided field PROG, which will contain a long text - the course program. For such a field, the data type is selected - Field MEMO . You can enter data in this field directly in Datasheet or Form view.
OLE object field and when entering values into it, establish a connection with the files where the program texts are stored.
Table 4. Description of properties of the SUBJECT table
| Field name | Indexed | Obligatory field | Data type | Size | Field label | Value condition | Error message |
| KP | Yes, match. not a permit. | Yes | Text | Item Code | |||
| NP | Yes | Text | Item name | ||||
| WATCH | No | Numerical | Whole | Total hours | >0 And<=300 | Erroneous number of hours | |
| LEK | No | Numerical | Whole | Lectures | |||
| ETC | No | Numerical | Whole | Practice | |||
| emergency | No | Numerical | Whole | Semester | |||
| PROG | No | Memo field | Program |
Defining a Composite Primary Key
Table STUDENT to composite primary (indexed) key includes fields NG and NS. To define this key in table design mode, select both of these fields by clicking on the marking area with the key pressed
Table 5. Description of properties of fields in the STUDENT table
Create a table structure STUDY, PROGRESS. When creating tables, use their properties, which are presented in tables 6, 7.
Table 6. Description of the properties of the STUDY table
| Field name | Indexed, field | Obligatory field | Data type | Size | Field label |
| NG | Yes | Text | Group number | ||
| KP | Yes | Text | Item Code | ||
| TABN | Yes | Text | Tab. prep number | ||
| VIDZ | Yes | Text | Type of occupation | ||
| WATCH | No | Numerical | Whole | Watch |
Table 7. Description of the properties of the PROGRESS table
| Field name | Indexed | Obligatory field | Data type | Size | Field label |
| NG | Yes, matches are allowed per field | Yes | Text | Group number | |
| NS | Yes | Text | Student number | ||
| KP | Yes | Text | Item Code | ||
| TABN | Yes | Text | Tab. nom. teacher | ||
| VIDZ | Yes | Text | Class type | ||
| GRADE | No | Numerical | Whole | Grade |
TASK N 3
ENTERING DATA INTO DB TABLES
After defining the structure of the table, you can proceed to the second stage of creating a table - data entry. To complete the task, follow these steps:
1. Run access.
2. Download your database that you created in the second task.
3. Entering records in table view:
Let's add some records to the table CHAIR. To do this, in the database window, select the CHAIR table and press the button Open.
Fill in the rows (records) of the opened table in accordance with the names of the columns (fields), the data for which are presented in Table 8.
Table 8
As you enter, the data is automatically checked against the specified field type, size, and value condition. The uniqueness of key field values is tracked.
The transition from one field to another can be done with the key
OLE Object Placement
Consider object placement OLE on the example of the field Photo of the head of the department in the table CHAIR. Let the photos be stored in a graphic editor format Paint(in files with extension *. bmp on drive C).
Place the cursor in the corresponding field of the table. Execute the command Insert - Object. In the window Inserting an object it should be noted Create from file. Window Inserting an object converted to a format that will allow you to enter the name of the photo file. You can see the contents of the field through the form, report or in a graphical editor paint, by double clicking on the type box OLE with content. Further changes to the file will not be reflected in the embedded object. After entering, the type of object will be indicated in the cell - Bitmap VMR.
Using the data given in table. 9 to 14, enter entries in the STUDENT, TEACHER, GROUP, STUDY, SUBJECT, and PROGRESS tables.
Table 9. Data from the STUDENT table
Table 10. Data of the table TEACHER
| Tab. number | Name.teacher | Academic degree | Academic title | Department code |
| Andreev A.P. | Dr. tech. Science | Professor | ||
| Apukhtin I.S. | Cand. technical sciences | Assistant professor | ||
| Glukhov I.L. | Cand. technical sciences | Assistant professor | ||
| Sechenov Yu.B. | Cand. tech. Science | Assistant professor | ||
| Chernov L,K. | Cand. technical sciences | Assistant professor | ||
| Blyumkina I.P. | Doctor of Physics and Mathematics | Professor | ||
| Lvova P.R. | Assistant | |||
| Shaposhnikov S. I. | Dr. tech. Science | Professor | ||
| Novikov P.N. | Assistant | |||
| Ilyasov I.T. | Cand. Phil. Science | Assistant professor | ||
| Pustyntsev A.P. | Cand. ist. Sciences | Assistant professor |
Table 11. GROUP table data
Table 12. Table data STUDY
| Group number | Item Code | Teacher's table number | Type of occupation | WATCH |
| Lek | ||||
| Etc | ||||
| Lek | ||||
| Etc | ||||
| Lek | ||||
| Etc |
Table 13. Table data PROGRESS
| Group number | Student number | Item Code | Teacher's table number | Class type | GRADE |
| Lek | |||||
| Etc | |||||
| Lek | |||||
| Etc |
Table 14. Table data SUBJECT
| Pre-Meta Code | Item name | Total hours | Lectures | Practice | Semester | Program |
| Computer science | ||||||
| Mathematics | ||||||
| Story |
Entering data in the field PROGRAM(field type MEMO
) can be executed directly in the table or through the input area called by pressing the keys
If program texts have already been prepared in some text editor, for example, Microsoft Word, and are stored in separate files, it is convenient to set the type for this field. OLE object field and when entering values into it, establish a connection with the files. Enter your own topics for each subject.
TECHNOLOGY OF CREATING A DATA SCHEME ON THE EXAMPLE OF A DATABASE LEARNING PROCESS
OLE input
The field of an OLE object called Foto can be populated even without the object (image) itself being displayed on the screen. The following objects can be stored in an OLE type field.
Raster images.
Sound files
Graphs and (diagrams)
Word or Excel
in a way that they can be seen, heard or used. When you place an OLE object in a table, you will see text describing the object (for example, in the OLE type field, you can see the inscription Paintbrush Picture). There are two ways to enter an object.
Paste from clipboard
Insert from the dialog box that appears after executing the command Insert->Object (Insert->Object)
Entering MEMO data
The Review field contains data of type MEMO. This type allows you to enter up to 64000 bytes of text for each entry. The table displays only part of the entered text. pressing
Move through records in a table
As a rule, after entering the data, it often becomes necessary to make some changes. This may be due to the following reasons
New information received
Errors found
Need to add new entries
To change the data, first open the table In the database window, open the table in datasheet mode by double-clicking on the line with its name in the list of tables. AAAAAAAAAAAAAAAAAAAAAAAAAAA
If you are in Table Design mode, click the Views button to switch to Table View to make changes to the data.
Move through records
To go to any record, you can simply place the cursor on it or click on it with the mouse. But if the tables are very large, then the problem of quickly moving to the desired record becomes especially acute
To move through the records, you can use vertical stripe scroll. The arrow buttons on the scroll bar allow you to move the record marker only one position per click. Therefore, for faster movement (through several records at once), it is better to use the scroll bar slider. You can also click the mouse in the area between the slider and the button on the scroll bar to move to many positions ahead.
The Edit->Go command provides several options for quickly moving around the table.
The five navigation buttons at the bottom of the table view window can also be used to move through the records By clicking these buttons, you can jump to any record If you know the record number (line number for a given record), click in the record number field, enter entry number and press the key
As you move through the table, pay attention to the scroll bar hints. Access does not update the record number field until you click in any of the record fields.
Setpoint search
Although, knowing the record number, you can go to it and find a specific field, in most cases you will need to find in the record certain value This can be done in three ways:
Select command Edit->Find (Edrt->Find)
Click on the Find Specified Text button located on the toolbar (it shows binoculars).
Use keyboard shortcut
When using any of these methods, a dialog box will appear. To search only for a specific field, place the cursor in it (and do this before opening the dialog box). Select the Search Only Current Field check box in the dialog box and Access will only search the specified field.
This dialog box allows you to set various search options. Enter the value you are looking for in the Find What text box special characters:
* -- Matches any number of characters
Matches one character
# -- Matches one digit
To understand how these characters work, suppose you want to find all values that start with AB To do this, enter AB*
Now, suppose you want to find values that end in 001. In this case, enter *001. To search for any value that starts with AB, ends with 001, and contains only two characters between them, type AB??001. If you need to find all surnames ending in “ko”, then to search for values like Brodsky and Tchaikovsky, enter *ko.
The Match drop-down list contains three options:
With any part of the field (Any Part of Field)
Whole Field
From the beginning of the field (Start of Field)
The standard option is Whole Field. For example, the value Pet will be found if the value is Pet. If the Any Part of Field option is selected, then the search results will retrieve the values Petrov, Petrovsky. Shpetny, etc. As a result of the search with the setting From the beginning of the field (Start of O), the values of Petrov, Petrovsky will be found.
In the drop-down list View (Search) you can select one or more options from the set All, Up, Down (Up, Down, All)
If you select the Search Only Current Field option button, only one field will be searched for a value. The Match Case check box determines whether uppercase and lowercase letters are distinguished. They are the same by default. Searching for Pet will find Pet, Petr, and Petrov. If you select the Match Case check box, you must enter a search string that takes into account uppercase and lowercase letters
Clearly, case-sensitivity does not make sense for Number, Currency, and Date/Time data types.) When you select the Match Case check box, Access ignores the Match Case check box. Fields As Formatted) (If you formatted the fields of the table, check this box) For example, you need to search in the Date of Birth field for records of everyone born in April 1982. To do this, select the Search Fields as Formatted option and enter Apr 92. If you do not check this box, you will have to search by the exact date of birth, for example, look for 4/8/92
Using the Search Fields as Formatted checkbox can significantly slow down the search.
The search will begin when you click the Find First or Find Next button. When Access finds a value, it highlights it To find the first matching value, click the Find button. To search for the next value, click the Find Next button. The dialog box remains open throughout the search process. Therefore, when you find the value you want, click the Close button to close the dialog box.
MS Access allows you to store images and other binary data in tables (for example, an MS Excel spreadsheet, an MS Word document, a sound recording drawing). For this purpose, the data type Field object OLE. The actual amount of data you can enter in this type of field is determined by the amount hard drive Your computer (up to 1 Gigabyte).
Selecting this data type launches a lookup wizard that creates a field that offers a choice of values from a drop-down list containing a set of constant values or values from another table.
The size of this field is the same as the size of the key field used as a substitution (usually 4 bytes).
After we have looked at the data types in Access and the individual properties of the table fields, we can begin to create the structure of the table. Let's look at creating a table structure using the example of creating a table Orders the Northwind database that comes with Access. This table is already in the Borealis database, but a look at the process of creating this table would be very helpful. In order not to break the structure of the Northwind database, first create a sample database and open its window.
In the table design window in the column Name fields enter OrderCode .
Press key Tab or Enter , to go to column Type data. At the same time, note that information appears in the "Field Properties" section at the bottom of the dialog box.
In column Type data value appeared Text. Click on the expand button on the right side of the rectangle and you will see a list containing all data types. From this list, use the mouse or use the up and down keys to select a value. Counter and press the key Tab to move to a column Description. Column Description represents the explanation you give to your fields. When you work with this table in the future, this description will appear at the bottom of the MS Access screen whenever you are in the field. OrderCode , and remind you of the purpose of this field.
Enter explanatory text in a column Description and press the key Tab or Enter , to move on to entering information about the next field.
Enter a description for all fields in the table in the same way.
When you have finished entering the table structure, save it by running the command File/Save.
