Neon-komputadór

Application: Microsoft Access

Microsoft Access is a relational database, which means that it stores information in tables that relate to information in other tables. This differs to a flat-file database, which stores all the information in a single table. Once the lists of information are entered into Access as tables, relationships between the tables need to be established. These relationships are associations between common fields. A relationship between tables can be one to one, one to many or many to many. After the relationships are established queries, forms and reports can be created as alternative means to display the information from several tables at once.

Unlike the other overviews of the MS-Office suite of applications, this section on MS-Access can only hope to cover the key concepts and the most rudimentary use of the reporting tools available. Some computer experts are almost entirely dedicated to the management of relational databases and books as complex and larger than this are devoted to such programs. Indeed, in the world of database programs, MS-Access is really considered the basic rung, and is not recommended for instances when perfect data integrity is required or with extremely large or complex operations. The most important element of any database is the initial design. If this is not implemented correctly in the first place, enormous problems can occur both in the scalability of the database, the legibility for users and programmers, the integrity of the data and even the capacity of hardware to run it. Fortunately there are a number of simple rules and processes for effective and efficient design. The process for general database design is:
1) Determine what the database will be used for and what reports will be required. This will help determine what fields and tables to include.
2) Determine the fields required for the database. Data should be broken down into as many subcategories as possible. For example, use firstnames and surnames as separate fields rather than a single field for name.
3) Determine the tables required for the database. Tables should refer to only one subject. Rather than repeating information in a single table, split the information into multiple tables and associate the records through a common identifier. For example, rather than listing an author of multiple books several times on a single table with one record for each book, list the author once and relate an author ID number to a table that lists the titles of books.
4) Determine the primary key field, so that each record in each table has a unique identifier. This is necessary so that relationships can be established.
5) Determine the relationships between the Tables. A sketch can be of great assistance.

A standard naming convention exists in MS-Access to ensure that various database objects are not confused in expressions, macros and modules and, for that matter, among users. The convention simply consists of prefixing the database object according to type, thus a range of objects relating to "authors" would include tblAuthors for a Table, qryAuthors for Queries, frmAuthors for Forms, rptAuthors for Reports and mcrAuthors for Macros. The Menu bar in Access consists of File, Edit, View, Insert, Format, Records, Tools, Window, Help. When a new database (File -> New) is created, a Database Window opens displaying the types of Objects on the left hand column. The rest of the MS-Access screen is the same as previous MS-Office applications with a menu bar, toolbar and status bar. The Database Objects consist of Tables, Queries, Forms, Reports and Pages, Macros, and Modules. A Table stores information in rows and columns, rather like an Excel or other spreadsheet application. All databases must have at least one Table. Queries allow the user to question the information stored in a Table. Forms are custom-made screens that make the display of Tables or Queries easier for an end-user. Reports present the data of Tables or from Queries in a printed format. Pages are webpages designed viewing and working with Microsoft Access over an intranet or the Internet. Macros allow a number of simple tasks to be combined in a single command. They will be discussed in the next Chapter. Modules refer to Visual Basic modules, which automate complex tasks. They will also be discussed in the next Chapter.

Like other MS-Office applications, MS-Access 2002 (also known as MS-Access 10) fits in the style of MS-Windows XP, smart tags, task panes, pivot tables and charts, and support for the XML file format. In MS-Access 2000, the new features were multiple item clipboards, data access pages, the creation of webpages, subdatasheets, conditional formatting and integration into Microsoft SQL (Structure Query Language) Server. Tables constructed in MS-Access 2002 can be saved (File -> Export) as Dbase V, IV, III files, MS-Excel 3-7, 97-2002, HTML documents, Lotus 1-2-3 WK 1, WJ 2 and WK3, Paradox version 3-5, 7-8, as a Text file, Microsoft Application Server pages, Microsoft Internet Information Service files, XML files and ODBC databases. MS-Access can open all the above mentioned files as well as MS-Outlook and MS-Exchange files. Furthermore, previous editions of MS-Access can be imported and converted (Tools -> Database Utilities -> Convert Database).

The columns of an Access database table are referred to as fields, whereas the rows are called records. The two main ways to view a Table are either as Datasheet View (View -> Datasheet) or Design View (View -> Design View). In the Table Design View field names, data types and data type characteristics (e.g., maximum size) are determined. Data types include text, memo (long text), number, date/time, currency, autonumber (e.g., primary key), yes/no (or other binary opposite, true/false etc), OLE objects (such as graphics or other MS-Office files), hyperlinks (to the Internet or an intranet), or a Lookup wizard, which creates a field whose values are selected from a table, query or an otherwise preset list of values. Fields can be set with default values or require data entry by users.

Input Masks limits the amount of information placed in a field by masking the style. Particular formats can be chosen from the field properties section. Lookup fields allow a user to determine a field's value from a list of entries which is predetermined or derived from a Table or Query. Another feature available through the Design View of a datasheet is data validation (Edit -> Test Validation Rules). This can take a long time with a large and complex database. The field properties section allows for specific expressions that limit the values that can be entered in the field and display error messages when a value is entered that is contrary to the rule. One particular field (or fields) which every data table should have is a primary key. A primary key is one or more values which are unique and identifies each record in a table. It is used to relate "foreign keys" in other tables and thus create associations between Tables. The primary key field is selected either by the key icon on the Table Design toolbar or by the menu (Edit -> Primary Key). Using the autonumber field in the primary key is highly recommended as it must be unique. Other field types can be used instead, but it is not recommended. For obvious reasons, OLE, yes/no and hyperlink field types can't be used as the primary key.

Relationships are established and edited through the Table Design View through the initial Show Table window. Once initial relationships have been established new ones may be added or editing can occur through Tools -> Relationships or the relationships icon on the Database toolbar. The most important idea is that of referential integrity. For referential integrity, one of the linked fields must be a primary key, both fields must be of the same data type and size, both tables must be in the same database and a record in a table must have a matching record in the primary table. Once these criteria for referential integrity are set, the following additional rules apply: (i) a record in a related table can't be established unless there is a record in the primary table., (2) the primary key of the primary table can't be changed if matching records existing in related table (unless the Cascade Update Related Fields option is selected)., (3) likewise, a record cannot be deleted from a primary table if matching records exist in a related table (again, unless the Cascade Update Related Fields option is selected). The option Cascade Deleted Related Records is not recommended for general use.

Relationships come in three basic types, which must be understood. One-to-one relationships are where a field in one table directly relates to a field in another table. These are the simplest relationship, but they don't occur very often as it is easier to store the information in one table. One-to-many relationships are the most common. This is where a single record field (e.g., an author identifier) relates to multiple records (e.g., book titles). The third type of relationship is many-to-many. This is where one or more records in one table relate to one or more records in another table. For purposes of legibility these often require a third, intermediate table (a junction table) is required that links data from each of the two tables. For example, where each author writes several books and many books are written by multiple authors a third table will be required.

In the Datasheet View, a user can edit, add or delete data. The Datasheet View is available for Tables, Queries and Forms. Other options whilst the user is in the Datasheet View is to print (File - Print) or preview the datasheet (File -> Print Preview), Cut, Copy, Paste or Delete selected rows and columns (Edit -> various), insert new columns or records (Insert -> New Record, Insert -> Column), Format data according to font (Format -> Font), the entire datasheet (Format -> Datasheet), and Columns and Rows. The Format menu also provides the option to rename Columns (Format -> Rename Column). Data records can be filtered and sorted (Record -> Filter, Record -> Sort) and the spelling of data (Tools -> Spelling) can be checked. By using the navigation bar (bottom of the datasheet) to move through records, either back or forward one at a time, to the first or last record or to a blank record. Finding specific records in a database can be achieved through the Find and Replace function (Edit -> Find, Edit -> Replace) which searches Tables, Queries and Forms.

Queries are used to view, change or analyze data. They can be used as a source of records for forms, reports and data access pages. There are five main types of Query in MS-Access; select queries, parameter queries, crosstab queries, action queries and SQL queries. A select query (the most common), simply collects data from across one or more tables and displays the results on a datasheet. Select queries can also be used to group records and calculate sums, counts, averages and so forth. A parameter query displays its own dialogue box prompting for the parameters from which a query can be run (e.g., two dates). Crosstab queries reorganize data that is grouped by two types of information (field and record). Action queries make changes to many records with a single operation. This includes deletions, updates, appends and make-table queries. SQL queries are those that are formed through SQL statements that cannot be constructed through the Design View for Queries.

When a Query is requested through Design View, the first window to appear is Show Table. Tables for a query are selected here. After this a split-level Query Window requests which fields are being used in a query, which can be selected by either double clicking the field name in the upper field list, by dragging the field with the mouse to the lower design grid or, preferably, by selecting the field name in the design grid. The design grid itself consists of Field, Table, Sort, Show (whether to show the results in the Query datasheet) and any additional criteria, which can be based on operators (=, <>, <, >, <=, >=) or the statements BETWEEN (e.g., dates) and LIKE, which recognizes characters and wildcards (e.g., LIKE "J*"). To select all the fields of a Table in a query, use the Table name with an asterisk in the Field dimension of the design grid. To make a logical 'AND' in a query, list each criteria in a separate column. To make a logical 'OR' statement in a query, simply list additional criteria. An AND statement restricts a query, requiring more than one criteria to be satisfied. An OR statement, relaxes a query, providing more than one alternative. Once a Query has been designed, it can be run by either clicking on the exclamation mark (!) from the Query design toolbar, or preferably, from the Query Design menu (Query -> Run).

Often queries are created from multiple tables. As Access is a relational database, this is a relatively simple matter - all that has to be done is the fields from one table need to be linked with the fields from another (in SQL terms, this is called a JOIN). To achieve this, select the required tables when creating a query and manually drag and link the requisite fields in the select query window whilst in query design view. Other queries require calculated fields. To achieve this an expression is created, consisting of the name of the calculated field followed by a colon and bracketed field names separated by arithmetic operators. If a field name exists in multiple tables then the table should be noted followed by an exclamation mark noting the field. For example, a simple calculation field which added the assets of each Division of the Ministry would be [TotalAssets]: [tblAdministration]![Assets]+[tblConsularAffairs]![Assets]+ and so on. Constructing such queries is greatly assisted by the Expression Builder, which is located on the Toolbar. Finally, perhaps on of the most useful queries is the use of the conditional function IIF, that is, the value of a field is changed according to whether a conditional statement is true or false. The general syntax for such queries is FieldName: IIF (expression, truevalue, falsevalue).

A form is a database object that is used to enter or display data in a manner that is easy for end-users. In general there are three types of form: a switchboard, that opens other forms and reports in the database, a data-entry form or a custom-dialogue box, which is often used to generate queries. Forms are usually bound to one or more Tables of Queries in the database, which is referred to as the Form's "record source". A Form usually consists of graphical elements to aid legibility, data from the record source, calculations stored within the form's design and descriptive text accompanying the design, which normally would be not be in the record source. A link between a form and its record source is through the graphical objects called controls, the most common being for displaying and entering data.

Forms are best created through an Autoform (Insert -> Form) or Wizard, as developing it through Design View is a very time consuming process. Modifications to the Form can then occur with the Design View, as the Wizard only provides three options in its own right. The main steps in designing a form is to decide what table fields or queries are desired and the layout and style. The layout choices are columnar, which displays one record at a time, tabular, which displays many records at once, datasheet, which is the same as the datasheet view of a table (which is redundant of course), justified, which displays one record at a time in a justified format.

To open forms in Design View either click the form button whilst in the database window or open the form in the form window and use the menu (View -> Design View). To add new fields to a form ensure that the field list is open (View -> Field List) and simply drag the list item to the desired place in the form. A toolbox to aid design is available through the menu command (View -> Toolbox). To delete a field simply select the field or control and enter Delete. Resizing and moving a control is achieved through selection of the control and modifying with the size handles or dragging it respectively. To change the tab order use the menu command (View -> Tab Order). To display or change the properties of a control, or even its datasource, use the menu command (View -> Properties). Furthermore, the properties of a form itself can be altered by clicking on the top left corner of the form in Design View.

Forms can also have subforms, a form within a form. These are particularly useful for working with multiple tables with one to many relationships. The main form is used to display the data for the 'one' side of a relationship, whereas the subform deals with the data on the 'many' side of the relationship. To create a subform select from the menu (View -> Create Subform) or use the toolbar icon. A related field must appear in both the main form (where it is called the parent field) and the subform (where it is called the child field). Modifying a subform is exactly the same as modifying a main form.

Reports are an effective means to present data in a printed format. They can be used, for example, to create mailing labels, to group records or to perform calculations. Like a Form, they are usually bound to one or more Tables and Queries in a database, and are usually presented with a title and date, data from the Tables or Queries, and, if part of the Report's design, totals. Also like Forms, Reports are linked to a record source by graphical objects called controls, which through text boxes, labels or lines organize the data.

Even more so than Forms, Reports are best created initially through the various Report Wizards (Insert -> Report), which allows the options for chart and label reports as well as standard reports. The first step in Report design is determining the record source, that is, which Tables and Queries the Report is based on. Then the sorting and grouping of data should be determined. Finally, the presentation should be determined, such as what sections are used, whether to use columnar and tabular styles, and what controls to use. Once a Report has been designed with a Report Wizard it can be modified by choosing Design View (View -> Design View). Modifications are generally the same as those used with forms. As a printed display, page setup options (File -> Page Setup) are very important to reports. Features like page numbers (Insert -> Page Number) and the date and time (Insert -> Date and Time) should also be considered.

A Data Access Page is a special type of webpage used for viewing and working with data from a MS-Access or Microsoft SQL Server over the Internet or an intranet. A Data Access Page is like a Form - it can be used to view, add, alter or delete data. A Data Access page, whether in a Page View, webbrowser, or Design View, consists of three sections. First is the title or group level which is used to display data and contains the record source. The second is the group record navigation. The third is the caption, which appears immediately after the group header. Bound controls cannot be placed in the group navigation or caption section.

Data Access Pages can be designed either through the Wizard (Insert -> Page) or through Design View. Like Forms and Reports, it is often best to construct a page through the Wizard first and then modify it through Design View. A Data Access Page is directly connected to a database, so when users display (filter, sort etc) a Data Access Page in Internet Explorer, they are viewing their own copy of the page. When however they make changes, such as adding or deleting data, this changes the values in the underlying database. Data Access Pages only work on Internet Explorer versions 5 or later.

MS-Access databases require regular maintenance to prevent and correct problems with data integrity. There are two utilities, Compact Database and Repair Database, which in MS-Access 2002 have been combined in a single command (Tools -> Database Utilities -> Compact and Repair). The main cause of problems is when a data record is deleted it leaves a (non-visible to users) gap in the structure. This isn't a problem if there is only a few records deleted, but when there are many and the database is complex it can severely affect performance.