A cell in a Boolean field can only contain one of two values: Yes or No. Access offers two more options for boolean type names: True/False and On/Off. Regardless of which field format you choose, a logical field will be represented in the table as a set of checkboxes. When you click on a checkbox with the mouse, it causes a checkmark image to appear in the box, which corresponds to the boolean Yes
Resetting the checkbox means assigning the boolean value No to the cell. The use of logical 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 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, you need to 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 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 link to it. Access will open a window for opening a document, and after selecting the desired file, it will determine which application must be opened to work with it.
The text or combination of text and numbers used as the hyperlink address.
The 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 a 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 are made up of multiple tables that are linked 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 the country corresponds to only one passport number, 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 entry from the first table can be associated with more than one entry from the second table;
One record from the second table can be associated with more than one record from the first table.
For example, communication between teachers and groups of students. Several teachers lead classes in several groups, and several groups study with several teachers.
In most cases, any two tables are linked in a one-to-many relationship.
Creating relationships between tables
In order to consider the creation of links between tables, we will build two more tables in the "Professional Consultant" database:
1. Table applicants:
CodeAb is the key field.
2. Intersection table tSvyazAbVUZ:
The first two fields are key, and for the table tConnection-Abvuz they are foreign keys.
Figure 12.6. Data Schema
To define relationships, use the Service->Data Schema command (button
). If the relationship is defined for the first time in the database, then 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 opened by pressing the button
). After that, the Data Schemas window should look something like the one shown in Fig. 3 (except connection lines). To establish a link between the tables tvuz and tConnectionAbvuz, select the field Codevuz in the table tvuzy (click the left mouse button), drag and drop it on the field Codevuz in the table tConnectionAbvuz. Similarly, a relationship is established between the other two tables.
When you release the mouse button, Microsoft Access will open the Links dialog box shown in Figure 1. 12.7.
Figure 12.7. Setting relationships between table fields
After you select the Enforce Data Integrity check box, Access makes two more check boxes available: Cascade Update Related Fields and Cascade Delete Related Records. When the Cascading 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 primary key value 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 the parent row (related row in parent table) is deleted. Select the Cascade update related fields check box to have Access automatically update the records in the tCommunicationAbvUz table when the university code changes.
When you click Create, Access creates a relationship and draws a line between the two tables, indicating that there is a relationship between them. Note that when you ask for data integrity, Access draws the number 1 at the end of the line on the table on the one side of the relationship, and the infinity symbol on the other end of 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 an existing link, double-click on the corresponding line to open the Links dialog box again.
OLE (Object Linking and Embedding)
OLE (Object Linking and Embedding) is a protocol for linking and embedding objects. Allows you to insert into a document or object of one application (OLE client) objects (OLE objects) of other applications that support the OLE protocol (OLE servers). In Access, OLE objects can be inserted, for example, into a field, form, or report.
OLE (Object Linking and Embedding) is a protocol for linking and embedding objects. Allows you to use data in various formats in forms and reports - for example, graphics or documents created in other programs.
OLE (eng. Object Linking and Embedding, pronounced oh-lay [olei]) is a technology for linking and embedding objects into other documents and objects developed by Microsoft.
OLE allows you to transfer part of the work from one editing program to another and return the results back. For example, a publishing system installed on a personal computer can send some text for processing to a text editor, or some image to an image editor using OLE technology.
The main advantage of using OLE (other than reducing the file size) is that it allows you to create a master file, a file cabinet of functions that the program accesses. This file can operate on data from the source program, which, after processing, is returned to the source document.
OLE is used in the processing of compound documents (eng. compound documents), can be used when transferring data between different unrelated systems through a transfer interface (eng. drag-and-drop), as well as when performing operations with the clipboard. The idea of embedding is widely used when working with multimedia content on web pages (for example, Web TV), which uses the transmission of images, sound, video, animation in HTML pages (hypertext markup language) or in other files that also use text markup (for example, , XML and SGML). However, OLE technology uses a "thick client" architecture, that is, a network PC with redundant computing resources. This means that the type of file or program being tried to inject must be present on the client's machine. For example, if OLE operates on Microsoft Excel spreadsheets, then Excel must be installed on the user's machine.
OLE 1.*
OLE 1.0 was released in 1990 based on the DDE (Dynamic Data Exchange) technology used in earlier versions of the Microsoft Windows operating system. While DDE technology was severely limited in the amount and methods of transferring data between two running programs, OLE was able to operate on active connections between two documents, or even embed a document of one type within a document of another type.
OLE servers and clients interact with system libraries using virtual function tables (VTBL). These tables contain pointers to functions that the system library can use to interact with the server or client. The OLESVR.DLL (on the server) and OLECLI.DLL (on the client) libraries were originally designed to communicate with each other using the WM_DDE_EXECUTE message provided by the operating system.
OLE 1.1 later evolved into the COM (component object model) architecture for working with software components. Later, the COM architecture was transformed and became known as DCOM.
When an OLE object is placed on the clipboard, it is saved in native Windows formats (such as bitmap or metafile) and also saved in its native format. The native format allows an OLE-enabled program to embed a chunk of another document copied to the clipboard and store it in the user's document.
OLE 2.0
The next evolutionary step was OLE 2.0, which retained the same goals and objectives as the previous version. But OLE 2.0 was built on top of the COM architecture instead of using VTBL. New features include automation of drag-and-drop technology, in-place activation and structured storage.
Keywords: access 2010 database; db access; subd access; access databases; access example; access programming; ready database; creating a database; DBMS database; access coursework; database example; access program; access description; access abstract; access requests; access examples; download database access; access objects; db in access; download subd access; ms access 2003 database; subd access abstract; subd ms access; access benefits; database; download database on access; Database; relational database; database management systems; course database; download database; access database download; access database download; abstract database; creation of a DBMS; download passport database; database; data normalization; DBMS examples; database examples; term papers on DBMS; normalization; database; database structure; database example; query base; training database; database design; data; description of the database; subd abstract; create a database; database for; database usage; course work database; finished; use of a DBMS; database table; database 2008 download; base
OLE input
The field of an OLE object called Foto can be populated even without the object (image) itself being displayed on the screen. The following objects can be stored in an OLE type field.
Raster images.
Sound files
Graphs and (diagrams)
Word or Excel
in a way that they can be seen, heard or used. When you place an OLE object in a table, you will see text describing the object (for example, in the OLE type field, you can see the inscription Paintbrush Picture). There are two ways to enter an object.
Paste from clipboard
Insert from the dialog box that appears after executing the command Insert->Object (Insert->Object)
Entering MEMO data
The Review field contains data of type MEMO. This type allows you to enter up to 64000 bytes of text for each entry. The table displays only part of the entered text. pressing
Move through records in a table
As a rule, after entering the data, it often becomes necessary to make some changes. This may be due to the following reasons
New information received
Errors found
Need to add new entries
To change the data, first open the table In the database window, open the table in datasheet mode by double-clicking on the line with its name in the list of tables. AAAAAAAAAAAAAAAAAAAAAAAAAAA
If you are in Table Design mode, click the Views button to switch to Table View to make changes to the data.
Move through records
To go to any record, you can simply place the cursor on it or click on it with the mouse. But if the tables are very large, then the problem of quickly moving to the desired record becomes especially acute
You can use the vertical scroll bar to navigate through the records. The arrow buttons on the scroll bar allow you to move the record marker only one position per click. Therefore, for faster movement (through several records at once), it is better to use the scroll bar slider. You can also click the mouse in the area between the slider and the button on the scroll bar to move to many positions ahead.
The Edit->Go command provides several options for quickly moving around the table.
The five navigation buttons at the bottom of the table view window can also be used to move through the records By clicking on these buttons, you can jump to any record If you know the record number (line number for a given record), click in the record number field, enter entry number and press the key
As you move through the table, pay attention to the scroll bar hints. Access does not update the record number field until you click in any of the record fields.
Setpoint search
While knowing the record number, you can navigate to it and find a specific field, in most cases you will need to find a specific value in the record. This can be done in three ways:
Select command Edit->Find (Edrt->Find)
Click on the Find Specified Text button located on the toolbar (it shows binoculars).
Use keyboard shortcut
When using any of these methods, a dialog box will appear. To search only for a specific field, place the cursor in it (and do this before opening the dialog box). Select the Search Only Current Field check box in the dialog box and Access will only search the specified field.
This dialog box allows you to set various search options. Enter the value you are looking for in the Find What text box You can enter the value as it appears in the box, or by using the following special characters:
* -- Matches any number of characters
Matches one character
# -- Matches one digit
To understand how these characters work, suppose you want to find all values that start with AB To do this, enter AB*
Now, suppose you want to find values that end in 001. In this case, enter *001. To search for any value that starts with AB, ends with 001, and contains only two characters between them, type AB??001. If you need to find all surnames ending in “ko”, then to search for values like Brodsky and Tchaikovsky, enter *ko.
The Match drop-down list contains three options:
With any part of the field (Any Part of Field)
Whole Field
From the beginning of the field (Start of Field)
The standard option is Whole Field. For example, the value Pet will be found if the value is Pet. If the Any Part of Field option is selected, then the search will retrieve the values Petrov, Petrovsky. Shpetny, etc. As a result of the search with the setting From the beginning of the field (Start of O), the values of Petrov, Petrovsky will be found.
In the drop-down list View (Search) you can select one or more options from the set All, Up, Down (Up, Down, All)
If you select the Search Only Current Field option button, only one field will be searched for a value. The Match Case check box determines whether uppercase and lowercase letters are distinguished. They are the same by default. Searching for Pet will find Pet, Petr, and Petrov. If you select the Match Case check box, you must enter a search string that takes into account uppercase and lowercase letters
Clearly, case-sensitivity doesn't make sense for Number, Currency, and Date/Time data types.) When you select the Match Case check box, Access ignores the Match Case check box. Fields As Formatted) (If you formatted the fields of the table, check this box) For example, you need to find in the Date of Birth field records for everyone born in April 1982. To do this, select the Search Fields as Formatted option and enter Apr 92. If you do not check this box, you will have to search by the exact date of birth, for example, look for 4/8/92
Using the Search Fields as Formatted checkbox can significantly slow down the search.
The search will begin when you click the Find First or Find Next button. When Access finds a value, it highlights it To find the first matching value, click the Find button. To search for the next value, click the Find Next button. The dialog box remains open throughout the search process. Therefore, when you find the value you want, click the Close button to close the dialog box.
Access 2010 has the ability to add objects to a field with OLE data type. When the data type is given as OLE (Object Linking and Embedding)- linking and embedding an object), then the Access DBMS saves the external an object in a common database file, allocating as much space for its storage as this object takes up as a separate file. Under objects should be understood as files created as applications in graphic editors, video clips, in MS Office applications, etc. When filling a table with data that are objects, a message is generated in the corresponding position of the field with the OLE data type about the program with which you can open this object. Object Display will be carried out only in forms and reports.
For object embedding in the field with OLE data type you need to open the table in constructor mode. Add a new field like "Product photo". Select data type "OLE Object Field" and then save the table.
Then in table view 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
Keep in mind that there are two possibilities to embed objects as data. The first possibility involves the use of standard applications for creating a file that Access supports, the second possibility of inserting an object, which allows you to use any file as a data source (Fig. 3.40).
Rice. 3.40 Adding a new object to an Access table from a file
1. Creating an object from a file.
When it comes to data that is objects to the database and is external files, it is required to have these files. Therefore, you will need to create several files in order to see their display in the database in the future, and also try to modify these files. For example, if you have a photo of an object in the file Monitor.jpg(jpg - 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 a window will appear, which is shown in Figure 3.41. With button "Review" 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" (Package), this means that the file is tied to the application with which it was created. Therefore, when browsing the database, whether it is 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 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 output 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 creating multiple photo and text files using various Windows editors. For example, you can open a Word document, paste 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 connected in a field describing the type of this OLE. To start the mode of creating new objects, you need to open the table in table mode, select an option "Create new" in the dialog box (Fig. 3.39), and then in the list select "Object type"(required application). The list of applications that Access supports to create 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 that can be used to create files
The convenience of using applications to create objects in the form of files lies in the fact that in the future these files can be used independently or their correction can be carried out in the database.
Consider object creation option for the database, with paint applications.
For example, you need to create an object with an image of the company and text that the database user 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 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 " Products» in mode "Constructor" add a new column named "Product photo", data type " OLE object field", save your changes.
2. In mode "Table" in field "Product photo" select line "Insert object from file", select (dot) "Create new" and select from the dropdown list 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. Choose pictures from the list that is 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 attached to your database, to the line in which it was inserted.
3. Follow this procedure to all lines your tables "Products".
4. Save your changes.
test questions
1. What objects can be inserted into a database with an OLE data type?
2. How can I insert a photo into a table in the "Table" mode?
3. Where in the database will the photo appear?
4. What is the difference between the "Create 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 a database?
8. Which application objects can be inserted into the database?
Every table in Access is made up of fields. Field properties describe the characteristics and behavior of the data added to it. A field's data type 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 available in Access, and provides more information in the data types reference section.
In this article
General information
Sometimes the data types may not seem obvious, for example, a field with a "Text" data type can store data that consists of both text and numbers. However, a field with data type "Number" can store only numeric data. Therefore, you need to know which 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;
how the field is used in expressions;
the ability to index the field.
Depending on how the new field is created, the field's data type can be predefined or can be selected. For example, if when creating a field in Datasheet view you:
use 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;
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 what type of data?
You can think of a field's data type as a set of characteristics that apply to all values in that field. For example, values in a text box can only contain letters, numbers, and some punctuation. In addition, the text field can contain a maximum of 255 characters.
Advice: Sometimes it looks like the data in a field is of one type, when 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 expressions are 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.
Main types
Format | Displayed data |
---|---|
Text |
Short alphanumeric values, such as last name or postal address. Be aware that starting with 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 |
"Yes" and "No" values, and fields containing 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 that is 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 first appeared in Access 2010. |
The attachment |
Attached images, spreadsheet files, documents, charts, and other supported file types in database records (as in email 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 the MEMO field is a detailed description of a product. Be aware that beginning with Access 2013, the Memo data type has been renamed to Long Text. |
Substitution |
A list of values that came from a table or query, or a set of values that you specified when you created the field. The lookup wizard starts and you can use it to create a lookup field. Depending on the selection made in the wizard, the data in the lookup field can be text or numeric. Lookup fields have an additional set of properties that are 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 |
Ordinary monetary values. |
Euro |
Ordinary monetary values in EU format. |
Fixed |
Numeric 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 regional settings for the date and time. For example, 03/14/2001 for Russia. |
Medium date format |
|
Long date format |
Date in long format. Depends on the regional settings for the date and time. For example, March 14, 2001 for Russia. |
Time (12 hours) |
The time is in 12-hour format only, which will match changes in the date and time culture settings. |
Average time format |
Time in 12-hour format followed by AM (AM) or PM (PM). |
Time (24 hours) |
The time is in 24-hour format only, which will match changes in the date and time culture settings. |
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 set additional field properties. The set of available additional properties depends on the data type of the field. For example, you can set the size of a text field using the property Field size.
For numeric and currency fields, the property Field size especially important because it defines the range of the field's values. For example, a one-bit numeric field can only contain integers between 0 and 255.
Property Field size also determines how much disk space each value of the numeric field takes up. Depending on the size of the field, the number can take 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 sets the maximum amount of available space for a single value.
Data Types in Links and Joins
Relationships between tables are relationships between common fields in two tables. Relationships 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 can't create a join between a number field and a text field, even if the values in those fields are the same.
When using a relationship or join, fields with the data type "Counter" 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 for a field that participates in a table relationship Field size. To change a property Field size, temporarily remove the link. But once you change the data type, you can't create the relationship again until you change the data type of the associated field. For more information about tables, see Tables overview.
Reference information about data types
The data type applied to a field contains a set of properties that you can select from. 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 use the attachment field to attach resumes or photos of contacts. For some file types, Access compresses the attachments it adds. The Attachment data type is only available in ACCDB format databases.
Types of attachments that are compressed in Access
When you attach a file of any of the following types to a database, Access compresses it.
-
TIFF files
Bitmaps, such as BMP files
Windows Metafiles, including EMF files
EXIF files
You can attach a variety of files to a recording. But some types of files can be a threat, so they are blocked. As a rule, 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 attachments of the following types:
CounterAppointment. The counter field allows you to specify a unique value, that is, it is used for only one purpose - to make each record unique. Most often, this field is used as the 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 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 to handle two contacts with the same name? Names are not suitable as primary keys because they are often not unique. When using the counter field of each record, a unique identifier is guaranteed. Note: Do not use a count field to store the number of records in a table. Count values are not reused, so deleting entries creates gaps in the count. And the exact number of records is easy to get using the totals row in the table view. Supported field properties
ComputedAppointment. Used to store the results of a calculation. The calculation can refer to 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
MonetaryAppointment. Used to store money 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 value of the money field occupies 8 bytes. Supported field properties
date and timeAppointment. Used to store date and time values. Supported field properties
HyperlinkAppointment. Used to store hyperlinks such as an email address or a website URL. Supported field properties
MEMOAppointment. Used to store a block of formatted text longer than 255 characters. Be aware that beginning with Access 2013, the memo data type has been renamed to Long text. Supported field properties
NumericalAppointment. Used to store a numeric value that is not a monetary value. If the field values can be used in calculations, choose the numeric data type. Supported field properties
Big numberAppointment. Used to store a large numeric value that is not monetary. If the field values can be used in calculations, select the Big Number data type. Supported field properties
OLE objectAppointment. 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 in attachment fields. In addition, OLE object fields do not allow you to nest multiple files in a single entry. Supported field propertiesTextAppointment. Used to store text up to 255 characters long. Be aware that starting with Access 2013, the text data type has been renamed to Short text. Supported field properties
LogicalAppointment. Used to store boolean values. Supported field properties
|