What is special about a field like ole. Lookup master data type. OLE Object Placement

The DEPARTMENT table has a PHOTO field, which must contain a photograph of the head, stored in the format graphic editor Paint in a file with the extension .gif. The data type of such a field must be defined as an OLE Object Field. This object is placed in the field at the stage of filling out the table fields. An object can be embedded or linked.

COMMENT

OLE (Object Linking and Embedding) is a method of transferring information in the form of objects between Windows applications. An OLE object field is a means that allows you to link to objects in another application or embed an object in a database. Objects can be plain and formatted texts, drawings, diagrams, audio files (.WAV), music in MIDI format (Music Instrumental Digital Interface), animation files (.FLI, .MMM), video clips (.AVI), spreadsheets from other applications that support this tool. Access, supporting OLE, is fully integrated with other applications Microsoft package Office.

The embedded object is stored in a database file and is always accessible. By double-clicking on a cell containing an embedded object, you can edit the object using the application in which the object was created.

The associated object is saved in an object file. The object file can be updated independently of the database. Last changes will be displayed the next time you open the form or report. When working with a database, you can also view and edit an object.

The edited linked object will be saved in the object file rather than in the database file. Object linking is useful when working with large objects that you do not want to include in a database file, as well as with objects that are used in multiple forms and reports. If linked file object has been moved, the connection must be re-established.

COMMENT

To display an OLE object in a form or report, you must create an “Attached Object Frame” control (see Chapters 4, 6).

Using MEMO Field data type

The SUBJECT table provides a PROGRAM field, which will contain long text data - a short course program. For such a field, the data type is selected – Field MEMO (Memo). Data entry into this field can be done directly in the table, or through the input area called up by pressing the + keys.

If the texts of the programs in the subjects are prepared in some text editor, For example, Microsoft Word, and are stored in separate files, it is convenient to set the type of this field to OLE Object Field (OLE Object) and, when entering values ​​into it, establish a connection with the files.

Fields with the Hyperlink type are used to navigate to objects of the same or another database Access data, to documents created in various applications Microsoft Office, and located on local or network drives, to pages of Web servers and documents of other resources in Internet networks or intranet. Clicking on a hyperlink takes you to the document, which is opened by the application that created it. The creation of hyperlinks in tables, forms and reports will be discussed in detail below. The creation of a hyperlink in the TEACHER table field is also discussed there.

Defining a Composite Primary Key

In the STUDENT table, the composite primary (unique) key includes the fields NG and NS. To determine this in table design mode, you need to select both of these fields by clicking on the marking area while holding down the key. Then click the Primary Key toolbar button.

Composite keys are defined similarly in the STUDY PERFORMANCE tables.

Exercise

Create a table structure STUDYING, PERFORMANCE. When creating tables, use the parameters that are presented in Appendix A.

Table A.1. Description of the field properties of the STUDENT table

Field name	Key field	Unique field	Obligatory field	Data type	Size	Number of decimal places	Field signature
NG	Yes		Yes	Text			Group
NS	Yes		Yes	Text			Student number in the group
Full name			Yes	Text			Full name
GODR			No	Numerical	Whole		Year of birth
ADDRESS			No	Text
PBALL			No	Numerical	With floating point 4 bytes		Passing score

Table A.2. Description of the properties of the GROUP table fields

Table A.3. Description of the field properties of the DEPARTMENT table

Field name	Key field	Unique field	Obligatory field	Data type	Size	Number of decimal places	Field signature
CCAF	Yes	Yes	Yes	Text			Code
NKAF			No	Text			Name
TEL			No	Text
ZAV			No	Text			Full name of manager department
PHOTO			No	OLE Object Field			Photo of the manager

Table A.4. Description of the field properties of the TEACHER table

Field name	Key field	Unique field	Obligatory field	Data type	Size	Field signature
TABN	Yes	Yes	Yes	Text		Tab. number
Full name			Yes	Text		Full name of the teacher
ST			No	Text		Uch. degree
ZV			No	Text		Uch. rank
CCAF			Yes	Text		Department code

Table A.5. Description of the field properties of the SUBJECT table

Field name	Key field	Unique field	Obligatory field	Data type	Size	Field signature	Condition on value	Error message
KP	Yes	Yes	Yes	Text		Item code
NP			No	Text		Item name
WATCH			No	Numerical	Whole	Total hours	>0 And<=300	The number of hours should not exceed 300
LEK			No	Numerical	Whole	Lectures
ETC			No	Numerical	Whole	Practice
Emergency			No	Numerical	Whole	semesters
PROG				MEMO field		Program

Table A.6. Description of the field properties of the STUDY table

Field name	Key field	Unique field	Obligatory field	Data type	Size	Number of decimal places	Field signature
NG	Yes		Yes	Text			Nom. groups
KP	Yes		Yes	Text			Code. subject
TABN	Yes		Yes	Text			Tab. n. Rev.
VIDZ	Yes		Yes	Text			Type of occupation
WATCH			No	Numerical	Whole		Wed. subject score
SCORE. GR			No	Numerical	From pl. point 4 bytes

Table A.7. Description of the properties of the fields of the ACHIEVEMENT table

Field name	Key field	Unique field	Obligatory field	Data type	Size	Number of decimal places	Field signature
NG	Yes		Yes	Text			Group number
NS	Yes		Yes	Text			Nom. student
KP	Yes		Yes	Text			Item code
TABN	Yes		Yes	Text			Tab. n. teacher
VIDZ	Yes		Yes	Text			Type of activity
GRADE			No	Numerical	Whole

Entering data into database tables

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 links 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. This is why the OLE protocol exists.

In order to embed an OLE object in a field cell, you need to place the cursor on it and, by clicking the right mouse button, call up the context menu. Select the command there Insert object. The program will open a dialog box to select the object type. The object in this case is identified with the application in which it can be created or edited. In the case when the switch is selected in this window Create new, the corresponding application opens, the work with which is somewhat 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 a Word document is selected and the finished document is already on disk, then you will have to open it separately in the editor, 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; you can only return to Access.

The second option for inserting an object is 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 establish a connection with it. Access will open a document opening window and, after selecting the desired file, determine which application needs to be opened to work with it. Please be aware that multiple applications can match the same file type. For example, files with the BMP extension are used by various graphic editors. The selected Access application starts and opens the user-selected file. Object injection ends after you close the program.

Entering and editing data in Table mode

The DBMS switches to Table mode when the button is pressed. The table window uses special characters to display the new and currently edited entry.

Fig.5. Table window in Table mode

Each table contains an empty record that follows the last existing record and is intended for entering new data. To enter, place the cursor in the table field and enter data on the keyboard. To move to the next field use the TAB key, to move to the previous field - SHIFT+TAB. The record is automatically saved when moving to another record (the record marker along the left border of the table changes shape: a triangle appears instead of a pencil), that is, special saving of data in the table is not required. To move between records, you can use the 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 that are locked by another user when working collectively 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 moved to the place to be changed in the table, a fragment (a separate field, record or column) is selected and new data is entered, data is inserted from the buffer, or data is deleted using the DELETE key.

Methods for selecting fragments:

Field highlighted by double clicking the mouse,

Record(line) is selected by clicking on the empty gray cell near the left border of the window,

Column highlighted by clicking on the column header,

Rectangular fragment: select a field in one corner, press the SHIFT key and, without releasing it, select the field in the opposite corner (or drag the mouse).

Entire table highlighted 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 necessary data in one table;

· click the "Copy" button on the toolbar;

· go to the destination field (by pointing at it with the mouse or by moving the cursor with the TAB keys),

· Click the "Insert" button on the toolbar.

Logical format

Currency format

Number format

Text format

Data types

The text field size can range from 1 to 255 characters.

The numeric field type can accept values ​​from the following list:

byte- integers ranging from 0 to 4,255;

whole- integers from - 32768 to 32767;

long integer - integers from - 2147483648 to 2147483647;

floating point (4 bytes)- numbers ranging from - 3.402823E38 to 3.402823E38;

floating point (8 bytes) - numbers ranging from minus 1.79769313486232E308 to plus 1.79769313486232E308.

Date/Time Format

For data type Date Time There is a set of field formats given below along with examples:

Full format (set by default) 04/15/94 05:30:10 RM

Short date format 04/15/94

Long time format 17:30:10

Average time format 05:30 RM

Short time format 17:30

By default, the currency format is numbers written with two decimal places, separated by digit groups and notated "R." at the end. The user can write in the line Field Format template for your format. Let's say you need to enter dollar amounts. Then you need to place the cursor on the word Monetary and replace it with a format template, for example, like this: # ###$ .

Counter format

Fields of the “counter” type perform a specific function - automatic identification of table records. Therefore, such a field has few properties that could be changed. First of all, a counter is always a number. Secondly, its value increases automatically. Access offers two options for changing the counter: consistent And random. The first option is convenient because it allows you to number entries. The second option can be used if you need to create a system for encoding records in a table that is protected from careless operator actions.

A cell in a Boolean field can only contain one of two values: Yes or No. Access offers two more options for naming Boolean types: True/False And On/Off. Regardless of which field format you choose, the logical field will be represented in the table as a set of checkboxes. When you click a checkbox with your mouse, it causes a checkmark image to appear in the square, which corresponds to a boolean value Yes. Resetting a checkbox means assigning a Boolean value to a cell field No. The use of boolean fields can be different, for example in questionnaires or to create controls.

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 links to objects that can be included in the database using the OLE data exchange protocol. Such objects can be, for example, graphic files.

In order to embed an OLE object in a field cell, you need to place the cursor on it and, by clicking the right mouse button, call up the context menu. Select the command there Insert object. The program will open a dialog box to select the object type. The object in this case is identified with the application in which it can be created or edited.

The second option for inserting an object is selecting the radio button Create from file in the window Inserting an object. IN In this case, it is assumed that the file already exists and you need to establish a connection with it. Access will open a document opening window and, after selecting the desired file, determine which application needs to be opened to work with it.

Using OLE Object Field Data Type

An OLE object field is a means that allows you to establish a connection with objects in another application or embed an object in a database. Objects can be simple and formatted texts, pictures, diagrams, sound recording files (.WAV), music in MIDI format (Music Instrumental Digital Interface), animation files (.FLI, .MMM), video clips (.AVI), spreadsheets from other applications that support this tool. Software application Access supporting OLE, fully integrates with other applications in the suite Microsoft Office .

Embedded object is saved in the database file and is always available. By double-clicking the cell containing the embedded object, you can edit the object using the application in which the object was created.

Linked object is saved in the object file. The object file can be updated independently of the database. The latest changes will be displayed the next time you open the table. When working with a database, you can also view and edit an object. To display an OLE object in a form or report, you must create an Attached Object Frame control.

For example, in the LEMM FREQUENCY table we will add a SOUND field, which should contain a record of the pronunciation of the word lemma in format (.WAV). If you have sound files on your disk with the pronunciation of individual words from the FREQUENCY_LEMM table, you can associate each such sound file with the SOUND field of the table. To do this, open the FREQUENCY_LEMMA table and sort it by the lemma field in ascending order (click the arrow next to the LEMMA field name and then select Sort from A to Z). Sorting is done to make it easier to find the desired word. Then we will select a word for which there is an audio file, for example “grandmother”. Next, you should place the cursor on the SOUND field of the recording with LEMMA = grandmother and press the right mouse button. A drop-down menu will open in which you need to select Insert Object. A window will open in which you need to click the Create from file button and then select the sound file on the disk with the pronunciation of the word “grandmother”. The text appears in the SOUND field Package. Now if you double-click on this inscription, the program for playing sound files will be called (usually a player Windows Media) and the word “grandmother” will be heard. The same actions should be performed with all words for which there are sound files. As a result of this operation, the words in the table for which you can listen to their sound will be marked accordingly (Fig. 14.9).

Rice. 14.9.

Using Hyperlink Data

Special data type Hyperlink Hyperlink ) allows you to store hyperlinks in database table fields Access. Fields with the Hyperlink type are used to navigate to objects of the same or another database Access to documents created in various applications Microsoft Office and located on local or network drives, to pages of Web servers and documents of other resources on the Internet. Clicking a hyperlink takes you to the document, which is opened by the application that created it.

To attach hyperlinks to database data, you need to perform the following sequence of actions. Let's add a DICTIONARY field to the FREQUENCY_LEMM table, which will contain hyperlinks to dictionary entries on the Internet with definitions for this lexeme.

In Design mode, set the type of this field = Hyperlink.

We will search on the Internet for the meanings of outdated words from the fairy tale that are not entirely clear to the modern reader, for example, “millstone,” “boyar,” “kochetok.”

For the first word (“millstone”) on the Internet we were able to find only a drawing depicting parts of a hand mill, among which there are two small millstones, let’s assume that these are “millstones” (club-kaup.narod.ru/rec/arcussr/tablell2 .jpg). The word "zhernovtsy" is formed as a diminutive from the word millstone according to the tradition of Russian word formation. For the second word - "boyar" there is a dictionary entry in Wiktionary (ru.wiktionary.org/wiki/boyarin). The third word, kochetok, was found on the website "Dictionaries and Encyclopedia Academician" in the electronic version of the Ushakov Dictionary (dic.academic.ru/dic.nsf/ushakov/842550).

Each link found must be copied into the DICTIONARY field of the FREQUENCY LEMM table. Subsequently, when you click on a link in this field, your Internet browser program will launch and open the corresponding web page in a separate browser window. A view of an Access database table with inserted hyperlinks is shown in Fig. 14.10.

Access 2010 has the ability to add objects to a field with OLE data type. In the case where the data type is specified as OLE (Object Linking and Embedding– object binding and embedding), then the Access DBMS retains the external an object in the general database file, allocating for its storage as much space as this object occupies in the form of a separate file. Under objects You should understand files created as applications in graphic editors, video clips, MS Office applications, etc. When a table is filled with data that is an object, a message about the program that can be used to open this object is generated in the corresponding position of the field with the OLE data type. Displaying an object will be carried out only in forms and reports.

For embedding an object in the field with OLE data type you need to open the table in "Constructor" mode. Add a new field, for example “Product photo.” Select data type "OLE Object Field", and then save the table.

Then in "Table" mode in the line for entering data, click the right mouse button, in the menu that opens, select the line with the command, which will open a dialog box (Fig. 3.38, Fig. 3.39).

Rice. 3.38 Inserting an object

Rice. 3.39 Dialog box for adding a new object to an Access table

Remember that there are two options for embedding objects as data. The first option involves using standard applications to create a file that Access supports, the second option is to insert an object, which allows you to use any file as a data source (Fig. 3.40).

Rice. 3.40 Adding a new object to an Access table from a file

1. Creating an object from a file.

When it comes to data that is an object for the database and is an external file, it is required to have these files. Therefore, you will need to create several files in order to see how they are displayed in the database in the future, and also try to change these files. For example, if you have a photograph of an object in the file Monitor.jpg(jpg is a universal photo format). In order for this file to be saved in the database, you should check the box (Fig. 3.40) "Create from file", after which the window shown in Figure 3.41 will appear. Using a button "Review" you should select the required file.

Rice. 3.41 Finding a file when creating an object in the database

In the corresponding line for the field, for example, "Product photo" the word will appear "Package", this means that the file is tied to the application that created it. Therefore, when viewing a database, be it a query, form or report, the user will see an image of the file as an icon, and when double-clicking on this image, the system will first find the application and then display the contents of the file (in this example, a photo) in it. . The exception is image files saved with the extension .bmp(bit map format). However, it should be recalled that files created in Microsoft applications will be displayed immediately in forms and reports. The conclusion is simple, photographs can be inserted into applications such as Paint, Word, Power Point, saved as separate files, and then connected as an object to the database. Try creating multiple photo and text files using different Windows editors. For example, you can open a Word document, insert a photo from the file, add text, and then save as: Monitor.docx(Fig. 3.42).

Rice. 3.42 Photo file created in Word

2. Creation of new objects.

By creating new objects in the database, you should mean using an application in which the file is created and then included in the field describing the type of this OLE. To start the mode for creating new objects, you need to open the table in "Table" mode, select option "Create new" in the dialog box (Fig. 3.39), and then select in the list "Object type"(required application). The list of applications that Access supports for creating an object is in the list (Fig. 3.43), which will open after running the command "Insert object" .

Rice. 3.43 List of Access object types that can be used to create files

The convenience of using applications to create objects in the form of files is that in the future these files can be used autonomously or corrected in the database.

Let's consider object creation option for the database, using paint applications

For example, it is necessary to create an object with an image of a company and text, which the database user can later replace or correct. To do this you need to select from the list Bitmap Image object, further technology for creating an object is shown in Figure 3.44.

1. Open the Paint application

2. Insert a photo from a file.

3. Add text (if necessary).

4. Save as a file.

5. Close the application.

Rice. 3.44 Technology for creating an object in the Paint application

Exercise 3.11

1. In the table " Goods» in mode "Constructor" add a new column named "Product photo", data type " OLE Object Field", save changes.

2. In mode "Table" in field "Product photo" select a line "Insert object from file", select (put a dot) "Create new" and from the drop-down list specify Microsoft Word document. This will open a text editor MS Word, here you need to insert a picture corresponding to the product in this line. Select pictures from the list that is stored directly in the editor (the “Insert” tab, the “Picture” icon, the “Start” button in the dialog box on the right), or insert a picture from other programs. You can use the Paint application (Bitmap Image object), where you can draw the product yourself. Close the created file, it will automatically be linked to your database, to the line in which it was inserted.

3. Follow this procedure for all lines yours "Products" tables.

4. Save your changes.

Control questions

1. What objects can be inserted into a database with an OLE data type?

2. How can I insert a photo into a table in Table mode?

3. Where will the photo appear in the database?

4. What is the difference between the “Create a new object” method and the “Create from file” method?

5. How to place a product photo file in the database if it has the extension .png?

6. Is it possible to edit a file that is inserted as an object into a database directly in the computer’s file system?

7. How to insert a presentation into the database?

8. Which application objects can be inserted into the database?