OLE object placement. OLE (Object Linking and Embedding) - Databases: Basic Concepts Default Value

The CHAIR table contains a field PHOTO, which should contain a photograph of the manager, stored in the format of the graphic editor Paint in a file with the .gif extension. The data type of such a field must be defined as OLE Object. This object is placed in the field at the stage of filling in the table fields. The object can be embedded or linked.

COMMENT

OLE (Object Linking and Embedding) is a method of transferring information as objects between Windows applications. An OLE object field is a means to link to objects in another application or to embed an object in a database. Objects can be simple and formatted texts, pictures, diagrams, sound recording files (.WAV), music in MIDI format (musical instrument digital interface), animation files (.FLI, .MMM), video clips (.AVI), spreadsheets from others. applications that support this tool. Access is OLE fully integrated with other Microsoft Office applications.

The embedded object is stored in the database file and is always available. Double-clicking on a cell containing an embedded object opens the possibility of editing the object using the tools of the application in which the object was created.

The 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 a form or report. When working with a database, you can also view and edit the object.

The edited linked object will be saved in the object file, not in the database file. Linking an object is convenient when working with large objects that you do not want to include in a database file, as well as with objects used in several forms and reports. If the linked object file is moved, you must re-establish the link.

COMMENT

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

Using data type MEMO field

In the table SUBJECT there is a field PROGRAMS, which will contain a long text data - a short program of the course. For such a field, the data type is selected - Field MEMO (Memo). Data can be entered into this field directly in the table, or through the input area, invoked by pressing the + keys.

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

Hyperlink fields are used to navigate to objects in the same or another Access database, to documents created in various Microsoft Office applications and located on local or network drives, to Web server pages and documents from other resources on the network. Internet or intranet. When you click on the hyperlink, you go to the document, which is opened by the application that created it. The details of creating hyperlinks in tables, forms and reports will be discussed below. It also discusses the creation of a hyperlink in the TEACHER table field.

Defining a Composite Primary Key

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

Composite keys are defined in a similar way in LEARNING SUCCESS tables.

The exercise

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

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

Field name	Key field	Unique field	Required field	Data type	The size	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 float. dot 4 bytes		Passing score

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

Table A.3. Description of the properties of the fields of the CHAIRMAN table

Field name	Key field	Unique field	Required field	Data type	The size	Decimal places	Field signature
CCAF	Yes	Yes	Yes	Text			Code
NCAF			No	Text			Name
BODY			No	Text
ZAV			No	Text			Full name of the head department
PHOTO			No	OLE Object Field			Photo of the manager

Table A.4. Description of the properties of the fields in the TEACHER table

Field name	Key field	Unique field	Required field	Data type	The size	Field signature
TABN	Yes	Yes	Yes	Text		Tab. room
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 properties of the fields of the table SUBJECT

Field name	Key field	Unique field	Required field	Data type	The 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 must be no more than 300
LEC			No	Numerical	Whole	Lectures
NS			No	Numerical	Whole	Practice
Emergency			No	Numerical	Whole	Semesters
PROGRAM				MEMO field		Program

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

Field name	Key field	Unique field	Required field	Data type	The size	Decimal places	Field signature
NG	Yes		Yes	Text			Nom. group
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 score per item
SBALL. GR			No	Numerical	From pl. dot 4 bytes

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

Field name	Key field	Unique field	Required field	Data type	The size	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. lecturer
VIDZ	Yes		Yes	Text			Activity type
GRADE			No	Numerical	Whole

Entering data into database tables

OLE Object Fields

MS Access allows storing images and other binary data in tables (for example, MS Excel spreadsheet, MS Word document, sound recording drawing). The data type serves for these purposes. Field object OLE. The actual amount of data that you can enter in this field is determined by the size of your computer's hard disk (up to 1 Gigabyte).

Lookup Master Data Type

Selecting this data type launches a lookup wizard, which creates a field that prompts you to select 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 wildcard (usually 4 bytes).

Creating a table structure

Now that we've covered the data types in Access and the individual properties of the table fields, you can start creating the structure of the table. Let's consider creating a table structure using the example of creating a table. Orders the Northwind database that comes with Access. This table is already available in the Northwind database, but it will be very helpful to walk through the process of creating this table. 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 Order Code .

Press the key Tab or Enter , to go to column Type of data. At the same time, note that at the bottom of the dialog box, information appears in the "Field properties" section.

In the column Type of data value appeared Text. Click on the dropdown 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 the up and down keys to select a value Counter and press the key Tab to go 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 find yourself in the field Order Code , and will remind you of the purpose of this field.

Enter explanatory text in the column Description and press the key Tab or Enter , to proceed to entering information for 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.

Access 2010 has the ability to add objects to a field with data type OLE... In the case when the data type is specified as OLE (Object Linking and Embedding- linking and embedding an object), then Access keeps the external an object in a common database file, allocating as much space for storing it as this object takes up as a separate file. Under objects should be understood as files created in the form of applications in graphic editors, video clips, in MS Office applications, etc. When the table is filled with data that are objects, a message about the program with which you can open this object is generated in the corresponding position of the field with the OLE data type. Object display 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 mode "Constructor". Add a new field like "Product photo". Select data type OLE Object Field and then save the table.

Then in the "Table" mode in the line for data entry, right-click, 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 possibilities for embedding objects as data. The first option involves the use of standard applications for creating a file that Access supports, the second is the ability 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, which are objects for the database, and are external files, then you need to have these files. Therefore, you will need to create several files in order to later see them displayed in the database, as well as try to modify these files. For example, if you have a photo 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, in the window (Fig. 3.40) check the box "Create from file", after which a window will appear, which is shown in Figure 3.41. Using the button "Overview" select the required file.

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

In the corresponding line for the field e.g. "Product photo" the word will appear Package, this means that the file is associated with the application with which it was created. Therefore, when viewing the database, whether it be a query, a form or a report, the user will see the 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 it (in this example, this is a photo) ... The only exceptions are image files saved with the extension. .bmp(bit map format). However, it should be reminded that files created in Microsoft applications will be displayed immediately in forms and reports. The conclusion is simple, photos 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 to create multiple files with photos and text 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.

The creation of new objects in the database should mean the use of the application in which the file is created and then connected in the field describing the type of the given OLE. To start the mode of creating new objects, you must open the table in mode "Table", choose an 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 are in the list (Fig. 3.43), which will open after running the command "Insert Object" .

Rice. 3.43 List of Access Object Types You Can Use to Create Files

The convenience of using applications for creating objects in the form of files lies in the fact that in the future these files can be used autonomously or they can be corrected in the database.

Consider object creation option for the database using with paint applications.

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

1. Open the Paint app

2. Insert photo from file.

3. Add text (if necessary).

4. Save as file.

5. Close the application.

Rice. 3.44 Object creation technology in Paint application

Exercise 3.11

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

2. In the mode "Table" in field "Product photo" select the line "Insert object from file", select (dot) "Create new" and from the dropdown 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 stored directly in the editor ("Insert" tab, "Picture" icon, "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 draw the product yourself. Close the created file, it will automatically be linked to your database, to the line in which you inserted it.

3. Follow this procedure to all lines your "Products" tables.

4. Save your changes.

Control questions

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

2. How can you insert a photo into a table in the "Table" mode?

3. Where in the database will the photo be displayed?

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

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

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

7. How to insert a presentation into the database?

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

Every table in Access is made up of fields. The properties of a field describe the characteristics and behavior of the data added to it. The data type of a field is the most important property that determines what data can be stored in a field. This article describes the data types and other field properties that are available in Access, and provides more information in the Data Type Reference section.

In this article

General information

At times, the data types may not seem obvious, for example, a field with the "Text" data type can store data consisting of text and numbers. However, only numeric data can be stored in a field with the Number data type. Therefore, you need to know what properties are used for each data type.

The data type of a field determines many other important characteristics of a field, in particular:

formats that can be used in the field;

the maximum size of the value in the field;

way of using the field in expressions;

the ability to index the field.

Depending on how the new field is created, the data type of the field can be predefined or selected. For example, if, when creating a field in table mode, you:

you are using an existing field from another table, the data types are already defined in it or in the template;

Enter data in an empty column (or field), Access assigns a data type to the field based on the values ​​you enter, or you can assign a data type and format to the field.

in the tab Changing fields in a group Fields and Columns choose a team Add fields, Access displays a list of data types to choose from.

When to use which data type?

A field data type can be thought of as a set of characteristics that apply to all values ​​in that field. For example, values ​​in a text box can contain only letters, numbers, and some punctuation. In addition, the text box can be up to 255 characters long.

Advice: Sometimes it looks like the data in the field is of the same type, but in fact it is data of a different type. For example, the field appears to contain numeric values, but they are actually text values ​​representing room numbers. Expressions are often used to compare or convert values ​​with different data types.

The tables below show the formats available for each data type and describe the formatting results.

Basic types

Format	Displayed data
Text	Short alphanumeric values, such as a last name or postal address. Remember, starting in Access 2013, the text data type has been renamed to Short text.
Numeric, Large number	Numerical values ​​such as distances. Remember that there is a separate data type for monetary values.
Monetary	Monetary values.
Logical	Yes and No values, and fields that contain only one of the two values.
date and time	Date and time values ​​from 100 to 9999.
Formatted text	Text, or a combination of text and numbers, formatted with color and font controls.
Calculated field	Calculation results. The calculation can refer to other fields in the same table. Calculations are created using the Expression Builder. Calculated fields were first introduced in Access 2010.
The attachment	Attached images, spreadsheet files, documents, charts, and other supported file types in database records (as in e-mail messages).
	Text, or a combination of text and numbers, saved as text and used as the hyperlink address.
Memo field	Long blocks of text. A typical use case for a MEMO field is a detailed product description. Remember, starting in Access 2013, the MEMO data type has been renamed to Long Text.
Substitution	A list of values ​​that are retrieved from a table or query, or a set of values ​​that you specified when you created a field. The lookup wizard starts, which you can use to create a lookup field. Depending on your choices in the wizard, the data in the lookup field can be text or numeric. Lookup fields have an additional set of properties, which are located on the tab Substitution in the area of Field properties.

Note: Attachments and calculated data are not available in MDB files.

Numerical

Format	Displayed data
General	Numbers without additional formatting (exactly as they are stored).
Monetary	The usual monetary values.
Euro	Regular monetary values ​​in EU format.
Fixed	Numerical data.
Standard	Numeric data with decimal places.
Percent	Percentage values.
Exponential	Calculations.

date and time

Format	Displayed data
Short date format	Date in short format. Depends on the locale of the date and time. For example, 03/14/2001 for Russia.
Medium date format
Long date format	Date in long format. Depends on the locale of the date and time. For example, March 14, 2001 for Russia.
Time (12 hours)	The time is only in 12-hour format, which will reflect changes in the regional settings for the date and time.
Medium time format	Time in 12-hour format followed by AM (before noon) or PM (after noon).
Time (24 hours)	The time is only in 24-hour format, which will reflect changes in the regional settings for the date and time.

Logical

OLE Object OLE objects such as Word documents.

Field Size property

After you create a field and specify the data type for it, you can configure additional field properties. The set of additional properties available depends on the data type of the field. For example, you can adjust the size of the text box using the property Field size.

For numeric and currency fields, the property Field size especially important because it defines the range of values ​​for the field. For example, a one-bit numeric field can only contain integers in the range 0 to 255.

Property Field size also determines how much disk space each numeric field value takes up. Depending on the size of the field, the number can be 1, 2, 4, 8, 12, or 16 bytes.

Note: Variable dimension values ​​are possible in MEMO and text fields. For these data types, the property Field size specifies the maximum size of available space for a single value.

Data types in links and joins

A relationship between tables is a relationship between common fields in two tables. The relationship can be one of the following types: one-to-one, one-to-many, many-to-many.

A join is an SQL operation that combines data from two sources into a single record in a query recordset based on the values ​​of a specified common field in the sources. A join can be one of the following types: inner join, left outer join, right outer join.

When you create a relationship between tables or add a join to a query, the data types in the joined fields must be the same or compatible. For example, you will not be able to create a connection between numeric and text fields, even if the values ​​in those fields are the same.

When using a link or connection, fields with the Counter data type are compatible with fields of a numeric type if the property Field size last set to Long integer.

Data type or property cannot be changed for a field participating in a relationship between tables Field size... To change a property Field size, temporarily remove the link. However, after you change the data type, you cannot re-create the relationship until you change the data type of the associated field. For more information on tables, see the article Understanding Tables.

Data type reference

The data type applied to the field contains a set of properties that you can select. For more information, click the data types below.

The attachment

Appointment. Used in a field that allows you to attach files and images to a post. For example, if you have a database of work contacts, you can attach a resume or contact photos using the attachment field. For some file types, Access compresses the attachments it adds. The Attachment data type is only available in ACCDB databases.

Types of attachments that Access shrinks

When you attach any of the following file types to your database, Access compresses the file.

Bitmaps such as BMP files

Windows metafiles, including EMF files

EXIF files

• TIFF files

You can attach a wide variety of files to a recording. However, some types of files can pose a threat, so they are blocked. Typically, you can attach any file created in one of the Microsoft Office applications, as well as log files (LOG), text files (TEXT, TXT), and compressed ZIP files. See the table later in this section for a list of supported image file formats.

List of blocked file types

Access blocks the following types of attached files:

		Counter Appointment. The counter field allows you to specify a unique value, that is, it is used for one purpose only - to make each record unique. Most often, this field is used as the primary key, especially if there is no suitable natural key (key based on the data field). The value in the counter field occupies from 4 to 16 bytes, depending on the value of the property Field size. Let's say you have a table that stores contact data. You can use contact names as the primary key for this table. But how do you handle two contacts with the same name? Names are not suitable as primary keys because they are often not unique. By using the counter field of each record, a unique identifier is guaranteed. Note: Don't use a counter field to store the number of records in a table. Counter values ​​are not reused, and therefore gaps are generated when records are deleted in the count. And the exact number of records is easy to get using the totals row in table view. Supported field properties Property Usage Field size Determines the amount of space to allocate for each value. Only two values ​​are valid for counter fields. Long integer is used for counter fields that are not used as replication IDs. This is the default. It should only be changed if you are creating a replication ID field. Note: Replication is not supported on newer format databases such as ACCDB. This value makes the counter fields compatible with other numeric long integer fields when used in links and joins. Each field value is 4 bytes. Meaning Replication code applies to counter fields that are used as replication identifiers in a replica database. Use this value only if you are operating in or implementing a replicated database design mode. Each field value is 16 bytes. New values Determines whether to increment the counter field with each new value, or to use random numbers. Choose one of the options below. Consistent. Values ​​start at 1 and increase by 1 for each new entry. Random. A random value starts and a random value is assigned for each new record. Values ​​are Long Integer and range from –2,147,483,648 to 2,147,483,647. Format If you are using a counter field as the primary key or replication ID, this property should not be set. Otherwise, choose a suitable number format. Signature Indexed Yes (matches are allowed). No. Note: If there is no unique index, you can enter duplicate values, which can break the relationships of which the key is a part. Using the property Indexed Smart Tags Text alignment Calculated Appointment. Used to store calculation results. The calculation can refer to other fields in the same table. Calculations are created using the Expression Builder. Calculated data types were first introduced in Access 2010. Calculated data types are available only in ACCDB databases. Supported field properties Property Usage Expression The result of this calculation will be stored in a calculated column. If this column was saved, then only the saved columns can be used in this expression. Result type The data type used to display the result of the calculation. Format Most often the value Format is set according to the type of the result. Decimal places Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Text alignment Determines the default text alignment in the control. Monetary Appointment. Used to store money data. Currency data is not rounded during calculations. The value in the currency field contains up to 15 digits to the left of the decimal point and 4 digits to the right. Each value of the money field is 8 bytes. Supported field properties Property Usage Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. Any valid number format can be used. Most often for a property Format the value is set Monetary. Decimal places Determines the number of characters displayed in the fractional part of numbers. Input mask Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Default value Condition on value Error message Mandatory Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. date and time Appointment. Used to store date and time values. Supported field properties Property Usage Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Default value Automatically assigns the specified value to this field when adding a new record. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. You can use the built-in format or create your own. List of built-in formats Full date format. By default, if the value is only a date, the time is not displayed, and if the value is only a time, the date is not displayed. This value is a combination of short date format and long time format. Examples of 03.04.2007 17:34:00 Long date format. Corresponds to the long date format as defined in the regional settings in Windows. Example: Saturday, April 3, 2007 Short date format. Corresponds to the short date format defined in the regional settings in Windows. Example: 04/03/2007. A warning: If the locale specifies the short date format "dd.mm.yy", it is assumed that the dates from 01/01/00 to 12/31/29 are in the twenty-first century (that is, they are from 2000 to 2029), and the dates from 01/01/30 until 31.12.99 - by the twentieth century (that is, these are the years from 1930 to 1999). Long time format. Corresponds to the parameter on the tab Time in the regional settings of Windows. Example: 17:34:23. Medium time format. Time in hours and minutes with a time separator followed by AM or PM. Example: 5:34 PM. Short time format. Time in hours and minutes with a time separator in 24-hour format. Example: 17:34. List of items that can be used in custom formats To create a custom format, specify any combination of the following elements. For example, to display the week of the year and the day of the week, enter nn/n. Important: Custom formats that are incompatible with the date and time settings set in the Windows regional settings are skipped. For more information on Windows regional settings, see Windows Help. Separator elements Note: Separators are defined in the regional settings of Windows. : Time separator. For example, hh: mm . Divide the components of the date. For example, mm.yyyy Any short string of characters enclosed in quotation marks ( "" ) Custom separator. Quotation marks are not displayed. For example, when you enter "," a comma is displayed. Date format elements d Day of the month, consisting of one or two digits (from 1 to 31). dd A two-digit day of the month (01 to 31). ddd Abbreviated name of the day of the week (Mon - Sun). dddd Full name of the day of the week (Monday - Sunday). n The number of the day in the week (from 1 to 7). nn The week number of the year (from 1 to 53). M Month number in one or two digits (1 through 12). MM A two-digit month number (01 to 12). MMM The first three letters of the month name (Jan - Dec). MMMM The full name of the month (January - December). To Quarter number in the year (1 to 4). G Day number of the year (from 1 to 366). yy The last two digits of the year number (01 to 99). yyyy Full year number (0100 to 9999). Time format elements h The hour value, consisting of one or two digits (from 0 to 23). hh A two-digit hour value (00 to 23). m Minutes in one or two digits (0 to 59). mm A two-digit minute value (00 through 59). with The seconds value, consisting of one or two digits (0 to 59). ss A two-digit second value (00 through 59). Clock format elements AM / PM 12-hour time format with the addition of capital letters "AM" or "PM" (for example, 9:34 PM). am / pm 12-hour time format with the addition of the lowercase letters "am" or "pm" (for example, 9:34 pm). A / P 12-hour time format with an uppercase "A" or "P" appended (for example, 9:34 P). a / p 12-hour time format with the addition of lowercase letters "a" or "p" (for example, 9:34 p). AMPM 12-hour time format using the appropriate AM / PM indicator defined in the regional settings in Windows. Built-in formats c Same as built-in full date format. ddddd Same as built-in short date format. dddddd Same as built-in long date format. ttttt Same as built-in long time format. IME mode IME Suggestion Mode Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Input mask Allows you to display special characters to control data entry. For example, an input mask might display a dollar sign ($) at the beginning of a field. Mandatory Requires mandatory data entry in the field. Show date picker Indicates whether the item should be displayed Date picker. Note: If you are using an input mask for a datetime field, the element Date picker not available regardless of the value of this property. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property. Hyperlink Appointment. Used to store hyperlinks such as email address or website URL. Supported field properties Property Usage Blank lines Add only Yes. Changes are tracked. To view the history of field values, right-click the field and select Column Log Display. No. Changes are not tracked. A warning: A warning. If you set this property to No Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Default value Automatically assigns the specified value to this field when adding a new record. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. You can define a custom format for the hyperlink field. IME mode Controls character conversion in East Asian versions of Windows. IME Suggestion Mode Controls sentence conversion in East Asian versions of Windows. Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Mandatory Requires mandatory data entry in the field. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. Compression Unicode Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property. MEMO Appointment. Used to store a block of formatted text longer than 255 characters. Remember, starting in Access 2013, the MEMO data type has been renamed to Long text. Supported field properties Property Usage Blank lines If set to Yes, allows a blank line ("") to be entered in a text box, hyperlink box, or MEMO box. Add only Indicates whether to track changes to the field value. Two values ​​are possible: Yes. Changes are tracked. To view the history of field values, right-click the field and select Column log display. No. Changes are not tracked. A warning: If you set this property to No, the existing field log is deleted. Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Advice: Short signatures are most effective. Default value Automatically assigns the specified value to this field when adding a new record. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. You can define a custom format for the MEMO field. IME mode Controls character conversion in East Asian versions of Windows. IME Suggestion Mode Controls sentence conversion in East Asian versions of Windows. Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Mandatory Requires mandatory data entry in the field. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. Compression Unicode Compresses the text stored in this field (if less than 4096 characters are stored). Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property. Numerical Appointment. Used to store a numeric value that is not monetary. If the field values ​​can be used in calculations, select the numeric data type. Supported field properties Property Usage Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Decimal places Determines the number of characters displayed in the fractional part of numbers. Default value Automatically assigns the specified value to this field when adding a new record. Field size Choose one of the options below. Byte. Used for integers from 0 to 255. Storage requires 1 byte. Integer. Used for integers between –32 768 and 32 767. Requires 2 bytes for storage. Long whole. Used for integers from –2 147 483 648 to 2147 483 647. Requires 4 bytes for storage. Advice: Use field size Long integer when you create a foreign key to link a field to a primary key field in another table that is of the Counter data type. Single floating point. Used for floating point numbers from –3.4 x 1038 to 3.4 x 1038, which have up to seven significant digits. The storage requires 4 bytes. Double floating point. Used for floating point numbers from –1,797 x 10308 to 1,797 x 10308, which have up to fifteen significant digits. The storage requires 8 bytes. Replication code. Used to store the globally unique identifier required for replication. The storage requires 16 bytes. Please be aware that replication is not supported for ACCDB files. Valid. Used for numbers from –9.999 ... x 1027 to 9.999 ... x 1027. Requires 12 bytes to store. Advice: field size. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. Any valid number format can be used. Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Input mask Allows you to display special characters to control data entry. For example, an input mask might display a dollar sign ($) at the beginning of a field. Mandatory Requires mandatory data entry in the field. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property. Big number Appointment. Used to store a large numeric value that is not monetary. If the field values ​​can be used in calculations, select the Large Number data type. Supported field properties Property Usage Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Decimal places Determines the number of characters displayed in the fractional part of numbers. Default value Automatically assigns the specified value to this field when adding a new record. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. Any valid number format can be used. Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Input mask Allows you to display special characters to control data entry. For example, an input mask might display a dollar sign ($) at the beginning of a field. Mandatory Requires mandatory data entry in the field. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property. OLE Object Appointment. Used to attach an OLE object, such as a Microsoft Office Excel spreadsheet, to a record. If you are going to use OLE, you need the OLE Object data type. In most cases, you should use an attachment field instead of an OLE object field. Fewer file types are supported in OLE fields than attachment fields. In addition, OLE object fields do not allow you to attach multiple files to a single record. Supported field properties Text Appointment. Used to store text up to 255 characters long. Remember, starting in Access 2013, the text data type has been renamed to Short text. Supported field properties Property Usage Blank lines If set to Yes, allows a blank line ("") to be entered in a text box, hyperlink box, or MEMO box. Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Default value Automatically assigns the specified value to this field when adding a new record. Field size Enter a value between 1 and 255. Text fields can contain values ​​between 1 and 255 characters. For larger text fields, use the MEMO data type. Advice: To improve performance, always specify the minimum required Field size. For example, if you store zip codes of a fixed length, you would specify it in the property Field size. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. You can define a custom format for the text box. IME mode Controls character conversion in East Asian versions of Windows. IME Suggestion Mode Controls sentence conversion in East Asian versions of Windows. Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Mandatory Requires mandatory data entry in the field. Smart Tags Adds a smart tag to the field. Smart tags have been deprecated since Access 2013. Text alignment Determines the default text alignment in the control. Compression Unicode Compresses the text stored in this field (if less than 4096 characters are stored). Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property. Logical Appointment. Used to store boolean values. Supported field properties Property Usage Signature The signature text that appears by default for this field on forms, reports, and queries. If this property is not specified, the field name is used. Any text string is allowed. Short signatures are most effective. Default value Automatically assigns the specified value to this field when adding a new record. Format Determines how the field looks in tables, forms, and related reports when displayed on screen or when printed. Choose one of the options below. True / False. Displayed as True or False. Not really. The value "Yes" or "No" is displayed. On Off"On" or "Off" is displayed. Indexed Indicates whether the field is an index. There are three possible values. Yes (no matches allowed). A unique index is created for the field. Yes (matches are allowed). A non-unique index is created for the field. No. All indices are removed from the field. Note: Do not change this property for a field that is used as a primary key. Using the property Indexed it is possible to create an index on one field, but this is not possible for all types of indexes. For example, you cannot use this property to create an index on multiple fields. Text alignment Determines the default text alignment in the control. Condition on value Allows you to enter an expression that must be true when the value of this field is added or changed. Used in conjunction with the Error Message property. Error message Lets you enter a message to display if the value you enter does not match the expression in the Condition on Value property.

A cell in a boolean field can contain only one of two values: Yes or No. Access offers two more options for boolean names: True / False and Enabled / Disabled. Regardless of which field format you choose, the table will present the logical field as a set of checkboxes. When you click a checkbox with the mouse, it causes a check mark to appear in the square, which corresponds to the boolean value Yes

Clearing the checkbox means assigning the Boolean value to the field to the cell. The use of boolean fields can be different, for example, in questionnaires or to create controls.

OLE Object Field

An OLE (Object Linking and Embedding) field is the last field type to select 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.

In order to embed an OLE object into a field cell, place the cursor on it and, by pressing the right mouse button, call the context menu. In it, select the Insert Object command. The program will open a dialog box for selecting the type of object. 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 to select the Create from file radio button in the Insert object window. In this case, it is assumed that the file already exists, and you need to establish a link with it. Access will open a window for opening a document and after selecting the desired file, it will determine which application you need to open to work with it.

Text, or a combination of text and numbers, used as a hyperlink address.

Address is text that specifies the path and file name for the document being opened. The path can be the path to a file on your hard drive, a UNC address, or an Internet URL.

You can use the Hyperlink command on the Insert menu to insert an address into a field.

Multi-tabular databases. Relationships between tables

Relational databases consist of several tables, the relationship between which is established using matching fields. Each record in the tables identifies one object. The relationship between objects defines the relationship between tables. There are 4 types of relationships:

1. A one-to-one relationship means that each record in one table matches only one record in another table. Only one passport number corresponds to one citizen of the country, while one passport number corresponds to only one person.

2. A one-to-many relationship means that each record in one table corresponds to one or more records in another table. One applicant can enter many universities and at the same time many applicants can enter one university.

3. A many-to-many relationship occurs between two tables when:

One record from the first table can be linked to more than one record from the second table;

One record from the second table can be linked to more than one record from the first table.

For example, communication between teachers and student groups. Several instructors teach in several groups, and several groups teach with several instructors.

In most cases, any two tables have a one-to-many relationship.

Creating relationships between tables

In order to consider the creation of relationships between tables, we will build two more tables in the Profkonsultant database:

1. Table of Entrants:

Ab code is a key field.

2. Table of intersection of tSvyazAbVUZ:

The first two fields are key, and for the tSvyaz-Abvuz table they are foreign keys.

Figure 12.6. Data schema

To define links, use the Service-> Data Scheme command (button

). If this is the first time a relationship is being defined in the database, Access opens an empty Data Schema window and then displays the Add Table dialog box. In this window, select the required tables and click the Add button. In our case, all three tables must be added to the data schema window. The Close button closes the Add Table dialog box (this window can be called by pressing the button

). After that, the Data Schema window should look something like the one shown in Fig. 3 (except for connection lines). To establish a link between the TVUZ and tSvyazAbvuz tables, you need to select the University Code field in the TVUZ table (left-click), drag and drop it onto the University Code field in the tSvyazAbvuz table. Similarly, a relationship is established between two other tables.

When you release the mouse button, Microsoft Access will open the Links dialog box, shown in Fig. 12.7.

Figure 12.7. Setting up relationships between table fields

After you select the Ensure data integrity check box, Access makes two more check boxes available: Cascade update of linked fields and Cascade delete of linked records. When the Cascading update of related fields check box is selected, Microsoft Access updates all foreign key values ​​in child tables (that is, tables on the many side of a one-to-many relationship) when the primary key value changes in the parent table that is on the side of the relationship One in a one-to-many relationship. If you select the Cascade delete related records check box, Microsoft Access will delete the child rows (related rows in the child tables) when you delete the parent row (the related row in the parent table). Select the Cascade update of related fields check box to have Access automatically update the records in the tLinkabvuz table when you change the university code.

After you click New, Access creates a link and draws a line between the two tables to indicate that there is a link between them. Note that when you ask for data integrity, Access displays the number 1 at the end of the table on the one side of the relationship, and infinity at the other end of the table on the many side. To remove a link, click on its line and press the Del key. If you want to edit or modify an existing link, double-click on the corresponding line to reopen the Links dialog box.