Unit 2. Create, open and close a Database

Unit 2. Create, open and close a Database (I)

Basic Access concepts.
If you want to learn or revise basic database concepts or want to learn the Access2003 managing objects read here .

Creating a database.

To create a new database we need to:

Click on the option Blank database... in the task pane that appears to the right of the screen.

If this pane is not visible we can use the File menu on the menu bar and select New...

Or we can click on the New  button on the tool bar. In this case a task pane will appear and we have to select Blank database...

The following dialogue box will appear next where we indicate the name of the database we are creating and where it should be stored. In the Save in: box click on the arrow on the right to select the folder where we are going to save the database. Note how in the lower box appear all the subfolders of the selected folder. Double click on the folder where we want to save the database. The buttons that appear on the right of Save in, are explained here . In the File name: box write down the name we want to call the database. click on the Create button.

A new database is created to which Access assignes a .MDB extention, and it will appear in the Database window:

If you look at the Database window, on the left appear the different types of objects that we can have in the database, (tables, queries, forms,...) and on the right, depending on the type of object selected on the left, Access shows us the objects of this type that are already been created and allows us to create new distinct objects. In our case the object selected is the Tables, the primary element of any database as all the rest of the objects are created from this. At this moment there are no tables created, when these are made they will appear on the right of the window below the Create... options. We will study these options in the next unit.

Unit 1. Basic elements of Access2003.

Unit 1. Basic elements of Access2003.

Lets look at what the basic elements of Access2003 are, the screen, the bars, etc, so as to be able to distinguish them. We will learn what their names are, where they are situated, and what they are used for. We will also learn how to get help in case of not knowing how to go on working. Once we know all of this, we will be in a position to begin creating databases on the following unit.

Opening and closing Access2003.

Lets look at the two basic ways of initiating Access2003.  From the Start button  , normally situated at the bottom left corner of the screen. Situate the mouse over the Start button, click, and a menu will unfold. On situating the pointer over Programs, a list of all the programs installed on your computer will appear; look at Microsoft Office, then Microsoft Access, click, and the program will initiate.  From the Access2003 button on your desktop .

You can now initiate Access2003 to try everything we explain to you.

To close Access2003, you can use any of the following methods:  Click on the Close button     depress the keys ALT+F4 .  Click on the File menu and then choose Exit option.

The Inicial screen

On initiating Access2003, an opening window will appear (seen below), we will now look at the basic components. This way we will get to know the names of the different elements and it will be easier for us to understand the rest of the course. The next screen we will show you (and generally all of those seen on this course) might not coincide exactly with what you will see on your own computer screen as every user can decide which elements to see at any particular time, as we will see further on.

The bars.

The title bar.

The title bar contains the name of the program. On the extreme right are the buttons to minimize, maximize/restore and close.

The menu bar.

The menu bar contains all Access2003 commands, grouped in drop down menus. By clicking on Insert for example, we will see the related operations with the different elements that can be inserted. All operations can be executed from these menus. But the more regular things are executed more quickly from the tool bar that we will see in the next point. Every option has an underlined letter, this means that we can access the option directly by pressing simultaneously the Alt key and the underlined letter, for example, Alt+F opens File option. In Access2003 the menu bar has an "intelligent" behaviour, which basically consists of showing the most important and most options used by the user. If you want more information on "intelligent" behaviour" of the drop down menus clic here  The drop down menus from the menu bar contain three basic elements:

Immediate commands. They are executed immediately on clic. They are recognised because to the right side of, either nothing appears or a combination of keys to use to execute it will appear. For example, in the Help menu, press F1 to enter into Microsoft Office Access Help.

Option with another drop down menu. Once situated over this, it opens a new menu along side with more options to choose from. Easily recognised because it has a triangle to its right as in the Help menu, Sample Databases...

Option with a dialogue box. By clicking on this option a dialogue box appears where we are asked for more information, and has buttons to accept or cancel the option. Easily recognised because the name ends with three dots. For example in the Help menu, Detect and Repair...

The toolbars.

The toolbars contain buttons with which we can immediately access the most habitual commands, like Save , Open , Print , etc.

There are options that are unavailable at certain moments. Easily recognised as they will have a toned down colour. The bar we showed you is the database, more toolbars exist for example the task pane (this occupies the right half of the sceen), these are accessible according to the screen we are on as we will see further in the course, including learning how to define our own bars.

The status bar. The status bar can be found at the bottom of the screen, and contains indications about the state of the application, it provides varied information according to the screen that we are on at the time.

MS-Access(Referential Integrity)

Basic concepts on relationships.

Relational databases.

In a relational database we have data organized in tables and programs (the DataBase Management System) that manage the data. In Access 2003, The Microsoft Jet is the database engine that manage the data. A relational database allows the simultaneous use of data from more than one table and the control on data. By using relationships, one can avoid the duplicity of data, saving memory and space on the drive, speeding up the execution and use of data in tables. To achieve a correct and eficious relational database it is essential to carry out previous studies about databases design. With the database design, we obtain the best distribution of data in tables and the relationships that must be defined in order to associate data properly. Tables relate from two to two, where one of them will be the main table (origin of the relationship) and the other will be the secondary table (relationships destination).

Types of relationships.

A relationship is an association that is established between columns (fields) in two tables. Three types of relationships can be distinguished:  One-to-one relationship: when one record in a table can have only one matching record the another table and vice versa. This type of relationship is used normally when we want to store information that only have some records in a table. For example we have a table of Employees, for each employee we store general data as name, address, ...and for those employees (not many) that have work in foreign countries, we want the total amount of years worked outside, and the total amount of years spend outside. So we can store this information in another table and relate the two tables with an ono-to-ono relationship. It is not a commonly used relationship type.  One-to-many relationship: When a record in a table can have only one matching record in the other table, and a record from the second table can have many matching records in the first table. E.g: we have two tables, one with the data of different countries, and another with data of clients. A client live in one country but in one country can live many clients, in this relationship, Clients table is the Related table. It is the most common type of relationship.  Many-to-many relationships: When a record in a table can have many matching records in a second table and when a record in the second table can have many matching records in the first table.A many-to-many relationship is only possible by defining an intermediate table between the two other tables. E.g: We have orders and products, one order can have many products and one product can appear on many orders, so we must have a third table Order details to relate products with orders. In this table the primary key consist Relationships (I) In this unit we will see how to relate tables and the different types of relationships that can exist between two tables in a database. If you do not know what a relationship is in a database, here  we will introduce you to some basic concepts to understand relationships better. Creating the first relationship. To create relationships in Access2003 firstly we need to position ourself in the Relationships window, we can opt for:  in Database window, drop down Tools menu, and select Relationships... option. or,  Click on the  button on the toolbar. The Show table dialogue box will appear: Click on one of the tables required in the relationship and click on the Add button; or double-click the name of the table. Repeat the previous step to add the second table, and so on. Finally click on the Close button to finish adding tables. Now the Relationships window will appear with the tables added before. To create the relationship: Drag the field of the principal table to the equivalent field in the related table. In our case drag Number (in Customers table) to Customer (in Invoices table). Normally you drag the primary key of the primary table. To relate tables with two or more fields, first select the fields mantaining CTRL key down, and then drag them. The Edit relationships dialogue box appears next: At the top should be the names of the related tables (Customers and Invoices) and below this the names of the related fields (Number and Customer). Observe! they always have to be the same kinds of fields containing the same types of information. Observe at the bottom the Relationship type assigned depends on the charateristics of the related fields (in our case One-to-Many) Activate the Enforce Referential integrity by clicking on it. If desired, the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated. If you want to know more about referential integrity and Cascade procedures clic here . To terminate, click on the Create button. The relationship is created and will appear in the Relationships window. Referential Integrity. Referential integrity is a system of rules that relational database systems use in order to ensure that data stored in related tables are valid. In Access 2003, we can activate these rules by checking the Enforce Referential integrity box when relationship is created o modified. If Referential Integrity is checked, Access will not allow us to insert a record in the related table if there is no matching record in the primary table. E.g: We have a residents table and a Cities table, in the Residents table I have a City field which indicates in which city the resident lives in, the two tables should be related by the City field. In this kind of relation (one-to-many) the Cities table is the primary table and the Residents table the related table (a city has many residents, an a resident lives in one city). By checking the Enforce Referential Integrity box, we will not be allowed to insert a resident with a city that does not exist in the Cities table. Referential integrity has two associated actions:  Cascade update the related fields: If checked, when a value is changed in the related field in the primary table, the values in its related records in the related table will be automatically changed. E.g: If we change the name of a city in Cities table, then automatically in the Residents table all the residents from this city will change to the new value. Cascade delete related records: If checked, when a record is deleted from the primary table, all of the related records in the related table will also be deleted. E.g: If we delete a city in the Cities table, all the residents from this city are automatically deleted from the Residents table. If options are not selected, Acess does not allow us to change the name of a city or eliminate a city if it has any residents assigned.