What is an ole object field. OLE (Object Linking and Embedding) - Databases: basic concepts. Show date picker
Every table in Access is made up of fields. Field properties 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 the field. This article describes the data types and other field properties available in Access and provides additional information in the Data Types reference topic.
In this article
General information
Sometimes the data types may not be obvious, for example, a field with the Text data type may store data consisting of text and numbers. But a field with a Number data type can only store numeric data. So you need to know which properties are used for each data type.
The data type of a field determines many others important characteristics fields, in particular:
formats that can be used in the field;
the maximum size of the value in the field;
how the field is used in expressions;
possibility of field indexing.
Depending on how you create a new field, the field's data type may be predefined or selectable. For example, if when creating a field in Datasheet view you:
you use an existing field from another table, the data types are already defined in it or in the template;
enter data in a blank 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;
on the tab Changing fields in 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's 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 field may only contain letters, numbers, and some punctuation. Additionally, the text field can contain a maximum of 255 characters.
Advice: Sometimes it looks like the data in a field is one type, but in fact it is data of a different type. For example, a field appears to contain numeric values, but is actually text values representing room numbers. Often to compare or convert values with different types expressions are used in the data.
The tables below show the formats available for each data type and describe the formatting results.
Main types
Format | Displayed data |
|---|---|
|
Text |
Short alphanumeric values, such as last name or postal address. Remember that starting from Access versions 2013, text data type renamed to Short text. |
|
Numeric, Large number |
Numeric values, such as distances. Remember that there is a separate data type for monetary values. |
|
Monetary |
Monetary values. |
|
Logical |
Values "Yes" and "No", as well as fields containing only one of the two values. |
|
date and time |
Date and time values from 100 to 9999 years. |
|
Rich text |
Text, or a combination of text and numbers, that is formatted with color and font controls. |
|
Calculated field |
Calculation results. A calculation can reference other fields in the same table. Calculations are created using the Expression Builder. Calculated fields first appeared in Access 2010. |
|
The attachment |
Attached images, spreadsheet files, documents, charts, and other supported file types in database records (as in messages Email). |
|
Text or a combination of text and numbers saved as text and used as a hyperlink address. |
|
|
MEMO field |
Long blocks of text. A typical example of using a MEMO field is detailed description product. Please note that as of Access 2013, the MEMO data type has been renamed to Long Text. |
|
Substitution |
A list of values that are returned from a table or query, or a set of values that you specified when you created the field. The Lookup Wizard starts, allowing you to create a lookup field. Depending on the selections you make in the wizard, the data in the lookup field can be either text or numeric. Lookup fields have an additional set of properties that are found on the Substitution in area Field properties. |
Note: Attachments and calculated data are not available in MDB files.
Numerical
Format | Displayed data |
|---|---|
|
Are common |
Numbers without additional formatting (exactly as they are stored). |
|
Monetary |
Regular monetary values. |
|
Euro |
Regular monetary values in EU format. |
|
Fixed |
Numeric data. |
|
Standard |
Numeric data with decimal places. |
|
Percent |
Values are in percentages. |
|
Exponential |
Computing. |
date and time
Format | Displayed data |
|---|---|
|
Short date format |
Date in short format. Depends on regional date and time settings. For example, 03/14/2001 for Russia. |
|
Medium date format |
|
|
Long date format |
Date in long format. Depends on regional date and time settings. For example, March 14, 2001 for Russia. |
|
Time (12 hours) |
Time is in 12-hour format only, which will accommodate changes in regional date and time settings. |
|
Medium time format |
Time in 12-hour format, followed by AM (am) or PM (after noon). |
|
Time (24 hours) |
Time is in 24-hour format only, which will accommodate changes in regional date and time settings. |
Logical
OLE object OLE objects, such as Word documents.
Field Size property
After you create a field and specify a data type for it, you can configure additional field properties. The available additional properties depend on the field's data type. For example, you can adjust the size of a text field using the property Field size.
For numeric and currency fields, the property Field size is especially important because it determines the range of field values. For example, a one-digit 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 field size, a number can occupy 1, 2, 4, 8, 12 or 16 bytes.
Note: Variable size values are possible in MEMO fields 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 relationships and joins
A table relationship 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 fields being joined must be the same or compatible. For example, you won't be able to create a join between a number field and a text field, even if the values in those fields are the same.
When using a link or join, fields with the Counter data type are compatible with fields of the numeric type if the property Field size last set value Long integer.
A field that is involved in a relationship between tables cannot change its data type or property Field size. To change a property Field size, temporarily remove the connection. But after you change the data type, you won't be able to create the relationship again until you change the data type of the associated field. For more information about tables, see the article Understanding tables.
Data Type Reference
The data type applied to a field contains a set of properties that you can select. For more information, click data types below.
The attachment
Purpose. 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 use the attachment field to attach resumes or photos of contacts. For some file types, Access compresses attachments that you add. The Attachment data type is only available in ACCDB format databases.
Types of attachments compressed in Access
When you attach any of the following file types to a database, Access compresses it.
-
TIFF files
Bitmaps, such as BMP files
Windows metafiles, including EMF files
EXIF files
You can put the most into a recording different files. But some types of files may pose a threat and are therefore blocked. Typically, you can attach any file created in one of the applications Microsoft Office, as well as log files (LOG), text files(TEXT, TXT) and compressed ZIP files. For a list of supported image file formats, see the table later in this section.
List of blocked file types
Access blocks the following types of attached files:
CounterPurpose. The counter field allows you to specify a unique value, that is, it is used for only one purpose - to make each record unique. The most common use of this field is as a primary key, especially if there is no suitable natural key (a key based on the data field). The value in the counter field takes from 4 to 16 bytes depending on the property value 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 to handle two contacts with the same names? Names are not suitable as primary keys because they are often not unique. By using the counter field, each record is guaranteed to have a unique identifier. Note: Do not use the counter field to store the number of records in a table. Counter values are not reused, so when records are deleted, there are gaps in the count. And the exact number of records can be easily obtained using the totals line in table view. Supported Field Properties
ComputablePurpose. Used to store calculation results. A calculation can reference other fields in the same table. Calculations are created using the Expression Builder. Computed data types were first introduced in Access 2010. The calculated data type is only available in ACCDB format databases. Supported Field Properties
MonetaryPurpose. Used to store monetary data. Data in the currency field 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 currency field value takes 8 bytes. Supported Field Properties
date and timePurpose. Used to store date and time values. Supported Field Properties
HyperlinkPurpose. Used to store hyperlinks such as an email address or a website URL. Supported Field Properties
MEMOPurpose. Used to store a block of formatted text longer than 255 characters. Please note that as of Access 2013, the MEMO data type has been renamed to Long text. Supported Field Properties
NumericalPurpose. Used to store a numeric value that is not monetary. If field values can be used in calculations, choose a numeric data type. Supported Field Properties
Big numberPurpose. Used to store a large numeric value that is not monetary. If field values can be used in calculations, select the Large Number data type. Supported Field Properties
OLE objectPurpose. Used to attach to a post OLE object, such as a Microsoft spreadsheet Office Excel. 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. OLE fields support fewer file types than attachment fields. Additionally, OLE object fields do not allow multiple files to be attached to a single record. Supported Field PropertiesTextPurpose. Used to store text up to 255 characters long. Please note that as of Access 2013, the text data type has been renamed to Short text. Supported Field Properties
LogicalPurpose. Used to store boolean values. Supported Field Properties
|
Logical format
Currency 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 amounts in dollars. 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 type "counter" perform 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.
Cell in a field boolean type 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 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 open window and after selecting the desired file will determine which application needs to be opened to work with it.
OLE Object Fields
MS Access allows you to store images and other binary data (for example, spreadsheet MS Excel, MS Word document, drawing, sound recording). The data type is used for these purposes Field object OLE. The actual amount of data you can enter in a given field type is determined by the amount hard drive Your computer (up to 1 Gigabyte).
Lookup wizard data type
Selecting this data type launches the Lookup Wizard, which 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).
Creating a table structure
Now that we've looked at the data types in Access and the individual table field properties, we can begin creating the table structure. 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 Northwind database, but going through the process of creating this table will be very helpful. In order not to disrupt the structure of the Borei database, first create a training database and open its window.
In the table designer window in the column Name fields enter Order Code .
Press the key Tab or Enter , to go to column Type data. Please note that information appears in the “Field Properties” section at the bottom of the dialog window.
In column Type data meaning appeared Text. Click on the expand list 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 up and down keys to select a value Counter and press the key Tab to go to column Description. Column Description represents the explanation you give for 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 a 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 move on to entering information about the next field.
Similarly, enter a description of all table fields.
Once you have finished entering the table structure, save it by running the command File/Save.
Using OLE Object Field Data
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 files (.WAV), music in MIDI format (musical 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. Double click Clicking on a cell containing an embedded object opens the ability to 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. Last 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 for ease of searching the right 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 (as a rule, this is 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 on 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/boyar). 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 separate window browser. A view of an Access database table with inserted hyperlinks is shown in Fig. 14.10.
Boolean fields
Boolean fields are used to store data that can take one of two possible values. The Boolean field Field Format property allows you to use custom formats or one of three built-in formats: True/False, Yes/No, or On/Off. In this case, the values True, Yes and On are equivalent to the logical value True, and the values False, No and Off are equivalent to the logical value False.
If you select a built-in format and then enter an equivalent Boolean value, the entered value will be displayed in the selected format. For example, if the value True or On is entered into a Field control that has the Field Format property set to Yes/No, the entered value is immediately converted to Yes.
When creating a custom format, remove the current value from the Format property and enter your own format.
Free-length text fields
Free-length text fields (MEMO fields) can contain the same data types as simple text fields. The difference between these fields is that the length of MEMO fields can be very large compared to the size of text fields. May contain long text or a combination of text and numbers.
OLE Object Fields
MS Access allows you to store in tables images and other binary data (for example, an MS Excel spreadsheet, an MS Word document, a picture, a sound recording) linked or embedded in a table Microsoft Access. For these purposes, the OLE Object Field data type is used. The actual amount of data that you can enter in a field of this type is determined by the size of your computer's hard drive (up to 1 Gigabyte).
Lookup wizard data type
Selecting this data type launches the Lookup Wizard, which 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. Selecting this option from the list in a cell launches the Lookup Wizard, which determines the field type.
