Logical format
Currency format
Number 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 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 data entry
An OLE object field called Foto can be filled in 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 such 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 an OLE type field you might see Paintbrush Picture). There are two ways to enter an object.
Paste from clipboard
Insert from the dialog box that appears after executing the Insert->Object command
MEMO data entry
The Recenzia field contains MEMO type data. This type allows you to enter up to 64,000 bytes of text for each entry. The table displays only part of the entered text. By pressing
Move through records in a table
As a rule, after entering data, it often becomes necessary to make some changes. This may be due to the following reasons:
New information received
Errors detected
Need to add new entries
To change data, first open the table. In the database window, open the table in Datasheet view by double-clicking the row with its name in the list of tables. AAAAAAAAAAAAAAAAAAAAAAAA
If you are in Table Design mode, click the Views button to enter Table view to make changes to the data.
Navigate through entries
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 move through records. The arrow buttons on the scroll bar only allow you to move the record marker one position per click. Therefore, to move faster (through multiple 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 jump buttons located at the bottom of the window in Datasheet view can also be used to navigate through records. Clicking these buttons will allow you to jump to any record. If you know the record number (the row number for a given record), click on the record number field, enter entry number and press the key
As you move around the table, pay attention to the scroll bar prompts. Access won't update the record number field until you click on any field in the record.
Setpoint search
Although knowing the record number you can go 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 the command Edit->Find (Edrt->Find)
Click on the Find Specified Text button located on the toolbar (it shows binoculars).
Use a 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 search only the specified field.
This dialog box allows you to set various search parameters. In the Find What text box, enter the value you are looking for. You can enter the value as it appears in the field or by using the following special characters:
* -- Matches any number of characters
Matches one character
# -- Corresponds to one digit
To understand how these symbols work, suppose you want to find all values that start with AB. To do this, enter AB*
Now let's say you want to find values that end with the characters 001. In this case, enter *001. To search for any value that starts with the characters AB, ends with the characters 001, and has only two characters in between, enter AB??001. If you need to find all last names ending with “ko,” then to search for values like Brodsky and Tchaikovsky, enter *ko.
The Match drop-down list contains three choices:
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 equal to Pet. If the Any Part of Field option is selected, the values Petrov and Petrovsky will be retrieved as a result of the search. Shpetny, etc. As a result of the search with the setting Start of O, the values Petrov, Petrovsky will be found.
In the Search drop-down list, you can select one or more options from the Up, Down, All set.
When you select the Search Only Current Field option button, the value will be searched in only one field. The Match Case checkbox determines whether uppercase and lowercase letters will be distinguished. By default, they do not differ. When searching for the value Pet, pet, Petr and Petrov will be found. When you select the Match Case checkbox, you must enter a search string that takes into account uppercase and lowercase letters
Obviously, for Number, Currency, and Date/Time data types, case sensitivity does not make sense.) If you select the Match Case check box, Access ignores the Search field format check box. Fields As Formatted (If you formatted the table fields, check this box) For example, you need to search the DateBirth field for records of everyone born in April 1982. To do this, check the Search Fields as Formatted option and enter Apr 92. If you do not check this box, the search will have to be performed using the exact date of birth, for example, search for 4/8/92
Using the Search Fields as Formatted checkbox can significantly slow down the search.
The search begins when you click the Find First or Find Next button. Once you find a value, Access selects it. To find the first matching value, click the Find button. To find the next value, click the Find Next button. Throughout the search process, the dialog box remains open. Therefore, once you find the value you want, click the Close button to close the dialog box
OLE Object Field(Object Linking and Embedding) – the last field type selected from the list. It has only two properties: a signature and a “required field” parameter. Fields of this type do not store information as such, but contain links to objects that can be included in the database using the OLE data exchange protocol. Such objects can be, for example, graphic files. Even the best DBMS is not able to provide storage of all types of information representation that exist today. Yes, this is not necessary. It is enough to communicate with an application that works with one or another type of file. This is why the OLE protocol exists.
In order to embed an OLE object in a field cell, you need to place the cursor on it and, by clicking the right mouse button, call up the context menu. Select the command there Insert object. The program will open a dialog box to select the object type. The object in this case is identified with the application in which it can be created or edited. In the case when the switch is selected in this window Create new, the corresponding application opens, the work with which is somewhat different from the usual one. So, the user is not able to open existing file in order to make it an object. For example, if you select Word document and the finished document is already on disk, you will have to open it separately in the editor, copy it to the clipboard, and then go to the document window associated with Access and paste the contents of the clipboard. If the object is created, as they say, “from scratch,” then work in the selected application occurs in the usual way. The command to exit the application is also modified. In this case, it is no longer possible to close the application and end up in Windows; you can only return to Access.
The second option for inserting an object is selecting a radio button Create from file in the window Inserting an object. In this case, it is assumed that the file already exists, and you need to establish a connection with it. Access will open a document opening window and, after selecting the desired file, determine which application needs to be opened to work with it. Please be aware that multiple applications can match the same file type. For example, files with the BMP extension are used by various graphic editors. The selected Access application starts and opens the user-selected file. Object injection ends after you close the program.
Entering and editing data in Table mode
The DBMS switches to Table mode when the button is pressed. The table window uses Special symbols to display the new and currently edited entry.
Fig.5. Table window in Table mode
Each table contains an empty entry that follows the last one existing entry and is intended for entering new data. To enter, place the cursor in the table field and enter data on the keyboard. To move to the next field use the TAB key, to move to the previous field - SHIFT+TAB. The record is automatically saved when moving to another record (the record marker along the left border of the table changes shape: a triangle appears instead of a pencil), that is, special saving of data in the table is not required. To move between records, you can use the on-screen buttons at the bottom of the table window (to the first, next, last record).
Some fields cannot be changed: these are “Counters”, calculated fields and fields that are locked by another user when working collectively with one table. If the database was opened in Read-Only mode, then data entry is also not available.
Data editing includes the following basic operations: replacing data values, deleting records, adding records. To correct data in the "Table" mode, "manual" methods are used: the mouse cursor is moved to the place to be changed in the table, a fragment (a separate field, record or column) is selected and new data is entered, data is inserted from the buffer, or data is deleted using the DELETE key.
Methods for selecting fragments:
Field stands out double with a mouse click,
Record(line) is selected by clicking on the empty gray cell near the left border of the window,
Column highlighted by clicking on the column header,
Rectangular fragment: select a field in one corner, press the SHIFT key and, without releasing it, select the field in the opposite corner (or drag the mouse).
Entire table highlighted by clicking on the gray rectangle in the upper left corner of the table.
To copy data from one table to another (or to the same table) the Windows buffer is used:
· select the necessary data in one table;
· click the "Copy" button on the toolbar;
· go to the destination field (by pointing at it with the mouse or by moving the cursor with the TAB keys),
· Click the "Insert" button on the toolbar.
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.
Access 2010 has the ability to add objects to a field with OLE data type. In the case where the data type is specified as OLE (Object Linking and Embedding– object binding and embedding), then the Access DBMS retains the external an object in the general database file, allocating for its storage as much space as this object occupies in the form of a separate file. Under objects 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 that is an object for the database and is an external file, it is required to have these files. Therefore, you will need to create several files in order to see how they are displayed in the database in the future, and also try to change these files. For example, if you have a photograph of an object in the file Monitor.jpg(jpg is a universal photo format). In order for this file to be saved in the database, you should check the box (Fig. 3.40) "Create from file", after which the window shown in Figure 3.41 will appear. Using a button "Review" you should select the required file.
Rice. 3.41 Finding a file when creating an object in the database
In the corresponding line for the field, for example, "Product photo" the word will appear "Package", this means that the file is tied to the application that created it. Therefore, when viewing a database, be it a query, form or report, the user will see an image of the file as an icon, and when double-clicking on this image, the system will first find the application and then display the contents of the file (in this example, a photo) in it. . The exception is image files saved with the extension .bmp(bit map format). However, it should be recalled that files created in Microsoft applications, will be displayed immediately in forms and reports. The conclusion is simple, photos can be inserted into applications such as Paint, Word, Power Point, save 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 a Word document, insert a photo from the file, add text, and then save as: Monitor.docx(Fig. 3.42).
Rice. 3.42 Photo file created in Word
2. Creation of new objects.
By creating new objects in the database, you should mean using an application in which the file is created and then included in the field describing the type of this OLE. To start the mode for creating new objects, you need to open the table in "Table" mode, select option "Create new" in the dialog box (Fig. 3.39), and then select in the list "Object type"(required application). The list of applications that Access supports for creating an object is in the list (Fig. 3.43), which will open after running the command "Insert object" .
Rice. 3.43 List of Access object types that can be used to create files
The convenience of using applications to create objects in the form of files is that in the future these files can be used autonomously or corrected in the database.
Let's consider object creation option for the database, using paint applications
For example, it is necessary to create an object with an image of a company and text, which the database user can later replace or correct. To do this you need to select from the list Bitmap Image object, further technology for creating an object is shown in Figure 3.44.
1. Open 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.
Control questions
1. What objects can be inserted into a database with an OLE data type?
2. How can I insert a photo into a table in Table mode?
3. Where will the photo appear in the database?
4. What is the difference between the “Create a new object” method and the “Create from file” method?
5. How to place a product photo file in the database if it has the extension .png?
6. Is it possible to edit a file that is inserted as an object into 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?
