DBMS Microsoft Access, Creating a spreadsheet - Computer...

Microsoft Access DBMS

Microsoft Access is a stand-alone software product that is part of the professional Microsoft Office suite. The Access program is a convenient tool for creating and operating sufficiently powerful databases without having to write code, although at the same time it has built-in programming tools. The advantages of Access database should also include integration with other programs of Microsoft Office: Excel, Word, etc. Data created in these applications are imported and exported from one application to another.

Run the program Access executes the command Start, Programs, Microsoft Access.

The database file created in the program version 2007/2010 has an icon in the form of a sheet of paper with the image of the letter A and the extension * .accdb (Access data base), and in the version 2003 - * .mdb (Microsoft data base).

Create a new Microsoft Access application file in any open Windows folder with the File, New command and select the appropriate application.

Typical interface structure. When working with the application, a work field containing a program title with traditional Windows window control buttons is displayed. The program window includes the following tabs and commands.

File. Commands for creating, opening, saving, closing, printing a database file, as well as exporting and importing it.

Main. Copying, deleting, inserting database fragments, operations with the clipboard, etc.

View. Object and view selection commands.

Insert. Inserting new tables, queries, forms, reports and other database components into the database.

Working with any program objects begins with the Database window displayed in the auxiliary control area. The window includes a title bar with traditional control buttons, a string of command buttons and a working zero. On the left panel of the working zero are concentrated controls for calling program objects. The Access database includes the following object types:

• Tables - the main objects of the database. They store data.

• Queries are special structures designed to process stored data.

• Forms - a user-friendly interface for entering data into the table, correcting and viewing them.

• Reports - prepared for displaying and printing forms, based on information stored in tables or queries.

• Pages - pages for remote access to data through the corporate network or the Internet.

• Macros are sets of sequences of internal commands.

• Modules - program codes created by the Visual Basic for Application language.

The three command buttons Open , Constructor, Create are used to select the mode of operation with the database. The buttons Create and Constructor are designed to design the database, and the Open button is used to design the database. Designing a database using the New button corresponds to its name and serves to create new objects. The Constructor button opens the structure of the selected object and allows you to edit it and create controls. The Open button opens the selected object and allows you to edit the entries and create new ones.

At the bottom of the screen, the prompt displays messages for the user regarding their possible actions at the current moment of working with the database.

Creating a table

To create a new database, choose the command File, New, New database. Database design begins with the formation of the structure of its tables; for this, the Access DBMS provides several options: the designer mode, the table wizard and the data entry mode. Before creating a table structure, you should think over and design the name and content of its fields.

In Design mode, the user must specify the names of the database fields and the type of data stored in them. The Table Designer window is actually a descriptive form for creating and editing a table structure (Figure 7.3). At the top of the form is a table of three columns. The original form is empty; To create a table, you must at least fill in the boxes "& Field name and Data Type & quot ;. In the first column, the field names are entered, in the second column - their type, which is selected from the drop-down list by the selection button.

The following data types are available in Access:

• text - text or numbers that do not require calculations (for example, phone numbers);

• MEMO field - long text or combination of text and numbers - sentences, paragraphs;

• numeric - numeric data used for calculations;

• date/time - dates and times for the years from 100 to 9999, inclusive;

• monetary - monetary values ​​and numerical data used in mathematical calculations;

• Counter - unique consecutive increments (by 1) or random numbers automatically entered when adding each new entry to the table. You can not update the counter type fields;

• Boolean - Boolean values, as well as fields that can contain one of two possible values ​​(Yes/No);

• object field OLE - an object (for example, an Excel spreadsheet, a Word document, a drawing, a sound recording or data in binary format) that is associated or embedded in an Access table;

Table Designer Window

Fig. 7.3. Table Designer Window

• hyperlink - a string consisting of letters and numbers and representing the address of the hyperlink;

• The substitution wizard is used to start the Wizard that allows you to create a link between tables.

Graph Description is not mandatory and is intended for the text of the hint displayed in the status bar when selecting a field in the form.

The lower part of the table designer window contains a list of properties of the field selected at the top. Some of the properties are already set by default. The main properties of the field displayed on the General tab are:

• the size of the field that defines the maximum size of data that can be stored in fields with the data type: Text, Numeric or Counter;

• the format of the field that specifies the format for presenting data when displaying data on the screen or printing;

• the number of decimal places that specifies the number of decimal places after the decimal point, which can vary from 0 to 15;

• an input mask that allows you to set the text, numeric and date/time mask for the data type that appears when you enter data in the field;

• signature - the second field identifier used by the program instead of the field name when working with data in a table form to create a column header.

The Substitution tab allows you to use substitution operations when entering data into a table, making it easier for the user to work. Field properties are not considered mandatory.

When creating a table, you need to set a key field by just right-clicking on its name and selecting Key field. The primary key can be composed of several fields. This situation occurs when only a combination of the values ​​of several fields can uniquely identify the record in the table. To create a composite key, select several lines in the designer's form and then use the Key field tool. The key image in the line indicates that the field is the key. A table can not have more than one key, so if the key symbol has two or more rows, the table has a composite key.

When you finish creating the table structure, the form is closed (the system prompts you to save the table), and the table is given a name. The created table appears and becomes available in the main Database window by double-clicking on its icon.

In the Table Wizard mode, the table structure is formed in four stages using the numerous templates built into the Access program. First, choose the category of the table: personal or business, then successively the samples of the table and its fields (Figure 7.4). Here you can rename the field.

The program analyzes the key fields of all database tables, and if it finds fields with the same names among them, then it suggests to link them.

You can define the connection yourself using the Communication scheme. The last step of the Table Wizard determines the mode that will be activated after the wizard completes. You can set the mode of changing the structure of the table, meaning the transition to the design mode. Selecting the direct input mode in the table allows you to open the table for viewing and editing.

The new table has no entries - only the column names that characterize the table structure. To fill the table, the cursor is placed in the required cell with the mouse pointer, after which the information is entered. The transition to the next cell can be done with the Tab, key, and it is executed automatically after filling the last cell.

You can change the default table name and define the key field automatically or ca -

Table creation window with the help of the Table Wizard

Fig. 7.4. The table creation window with the help of the Table Wizard

it is possible. When entering and editing data, you can also jump to the desired cell using the jump buttons at the bottom of the table.

After filling out the table with the data, all the content is saved automatically.

Changes to the structure of the table (the composition of the fields or their properties) are made in the Constructor mode, for this, it should be selected in the Database window and click on the Constructor button.

The data entry mode allows you to switch to a table with fields names, default data (Field1, Field2, etc.) and which can be renamed (see Figure 7.5). When entering data in a tabular mode, the type of fields is determined automatically by the program, depending on the information entered into them. Externally, the structure of the table is similar to the Excel worksheet. You can change the column width and row height by placing the cursor on the line of their separation in the field selection and recording area. The rows of the table can be added, deleted and copied using the context menu. To select the columns and rows of the table, simply click or drag the column headers or the selection bar. To select a rectangular area of ​​cells, simply place the cursor in the corner cell and give it the appearance of a thick white cross, then drag it along the selection area.

The context menu in table mode contains many commands. Using the context menu of the column header, you can sort the data in ascending or descending order to remove columns from the display command Hide Columns, use the command substitutions Column etc. Calling the context menu of the table header, you can apply filtering for the rows of the table, use the parameters

Table mode window

Fig. 7.5. Table mode window

font for its design, etc. The context menu of a table cell allows you to filter records, add an object to a field that has the OLE object field type, set a hyperlink to the document, and so on. Applying filtering allows you to hide some of the records when they are viewed on the screen and display only filtered entries for viewing. Add a new entry, delete the entry, set the line height allows the context menu of the selection area of ​​the line.

After creating the table and closing it, it is displayed in the database window as an icon and its name. DBMS Access allows you to change the structure of the table in the future at any stage of working with the database in the design mode.

thematic pictures

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)