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 V shared file database, allocating for its storage as much space as this object occupies in the form of a separate file. Under objects should be understood as files created as applications in graphic editors, video clips, in 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, 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 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, whether it be a query, a form, or a report, the user will see an image of the file as an icon, and when double click Using this image, the system will first find the application, and then display the contents of the file in it (in in this example this photo). 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, and saved as separate files, and then connect as an object to the database. Try creating multiple photo and text files using different Windows editors. For example, you can open 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 document Microsoft Word . This will open 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.
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. How is the “Create” method different? new object» from 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 the database directly in file system computer?
7. How to insert a presentation into the database?
8. Which application objects can be inserted into the database?
A cell in a Boolean field can only contain one of two values: Yes or No. Access offers two other options for Boolean type names: 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 the Boolean value Yes
Clearing the checkbox means setting the cell field to the Boolean value No. The use of boolean fields can be different, for example in questionnaires or to create controls.
OLE Object Field
OLE (Object Linking and Embedding) object field is the last field type to be 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. In it, select the Insert object command. 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 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 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.
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 to be opened. The path can be a path to a file stored 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-table databases. Relationships between tables
Relational databases consist of several tables, the relationship between which is established using matching fields. Each entry 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 corresponds to only one record in another table. One citizen of a country corresponds to only one passport number, while one passport number corresponds to only one person.
2. A one-to-many relationship means that for every record in one table there is a corresponding 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 associated with more than one record from the second table;
One record from the second table can be related to more than one record from the first table.
For example, communication between teachers and groups of students. Several teachers teach classes in several groups, and several groups are taught by several teachers.
In most cases, any two tables are related in a one-to-many relationship.
Creating relationships between tables
In order to consider creating connections between tables, let’s create two more tables in the “Professional Consultant” database:
1. Table of applicants:
CodeAb is the key field.
2. Table of intersection tSvyazAbVUZ:
The first two fields are key, and for the tSvyaz-Abvuz table they are foreign keys.
Figure 12.6. Data Schema
To define connections, you must use the Tools->Data Schema command (button
). If a relationship is defined for the first time in a database, Access opens an empty Data Schema window and then displays the Add Table dialog box. In this window, you need to select the required tables and click the Add button. In our case, we need to add all three tables to the data diagram window. The Close button closes the Add Table dialog box (this window can be called by clicking the button
). After this, the Data Schema window should look something like the one shown in Fig. 3 (except connection lines). To establish a connection between the tvuz and tSvyazAbvuz tables, you need to select the Code vuz field in the tvuz table (click with the left mouse button), drag and drop it onto the Code vuz field in the tSvyazAbvuz table. Similarly, a connection is established between two other tables.
When you release the mouse button, Microsoft Access will open the Connections 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 options available: Cascade Update Related Fields and Cascade Delete Related Records. When the Cascade Update 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 value of the primary key in the parent table that is on the side of the relationship changes "one" in a one-to-many relationship. If you select the Cascade delete related records check box, Microsoft Access will delete child rows (related rows in child tables) when you delete the parent row (related row in a parent table). Select the Cascading update of related fields check box so that when the university code changes, Access automatically updates the records in the ContactAbvuz table.
After you click Create, Access will create a relationship and draw a line between the two tables to indicate that they are related. Note that when you ask for data integrity, Access displays a 1 at the end of the line for the table on the one side of the relationship, and an infinity symbol at the other end of the line for the table on the many side of the relationship. To delete a link, click on its line and press the Del key. If you want to edit or change existing connection, double-click on the corresponding line to open the Links dialog box again.
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.
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 Microsoft Access table. For these purposes, the OLE Object Field data type is used. 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. Selecting this option from the list in a cell launches the Lookup Wizard, which determines the field type.
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 information about Additional Information See the data types reference section.
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 other important characteristics of the field, including:
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 can 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. Please note that as of Access 2013, the text data type has been 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 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. Set available additional properties depends on the data type of the field. 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.
You cannot change the data type or property of a field that is involved in a relationship between tables. 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 related field. additional information For 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 the 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 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. 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
|
Loading...
