Ronald ross p. G. College (mba) koheda road, mangalpally (V) ibrahimpatnam (M), R. R. (Dist) 505510

MS Access

Access version 1.0 was released in November 1992, quickly followed in May of 1993 by an Access 1.1 release to improve compatibility with other Microsoft products and include the Access Basic programming language.

Microsoft specified the minimum hardware requirements for Access v2.0: Microsoft Windows v3.0 with 4 MB of RAM required, 6 MB RAM recommended; 8 MB of available hard disk space required, 14 MB hard disk space recommended. The product was shipped on seven 1.44 MB diskettes. The manual shows a 1993 copyright date.

Originally, the software worked well with relatively small databases but testing showed some circumstances caused data corruption. For example, file sizes over 10 MB were problematic (note that most hard disks were smaller than 500 MB at the time this was in wide use), and the Getting Started manual warns about a number of circumstances where obsolete device drivers or incorrect configurations can cause data loss. With the phasing out of Windows 95, 98 and ME, improved network reliability, and Microsoft having released 8 service packs for the Jet Database Engine, the reliability of Access databases has been vastly improved in both size and number of users.

With Office 95, Microsoft Access 95 became part of the Microsoft Office Professional Suite joining Microsoft Excel, Word, and PowerPoint and transitioning from Access Basic to Visual Basic for Applications (VBA). Since then, there have been releases of Microsoft Access with each release of Office. This includes Access 97 (version 8.0), Access 2000 (version 9.0), Access 2002 (version 10.0), Access 2003 (version 11.0), and Access 2007 (version 12.0).

The native Access database format (the Jet MDB Database) has also evolved over the years. Formats include Access 1.0, 1.1, 2.0, 95, 97, 2000, and 2002-2007. The most significant transition was from the Access 97 to the Access 2000 format which was not backward compatible, and Access 2000 required the new format. Since Access 2000, all newer versions of Access support the Access 2000 format. New features were added to the Access 2002 format which can be used by Access 2002, 2003, and 2007.

In Access 2007, a new database format was introduced: ACCDB. The ACCDB supports complex data types such as multivalue and attachment fields. These new field types are essentially recordsets in fields and allow the storage of multiple values in one field.

Access's initial codename was Cirrus; the forms engine was called Ruby. This was before Visual Basic - Bill Gates saw the prototypes and decided that the BASIC language component should be co-developed as a separate expandable application, a project called Thunder. The two projects were developed separately as the underlying forms engines were incompatible with each other; however, these were merged together again after VBA.

Prior to the introduction of Access, the desktop database market was dominated by Borland with their Paradox and dBase programs, and FoxPro. Microsoft Access was the first mass market database program for Windows. With the purchase of FoxPro and incorporating its Rushmore query optimization routines into Access, Microsoft Access quickly became the dominant database for Windows effectively eliminating the competition which failed to transition from the MS-DOS world[1].

Access is a database program that stores information that can be manipulated, sorted, and filtered to meet your specific needs.

• 
  A database is a collection of related information.
  
• 
  An object is a competition in the database such as a table, query, form, or macro.
  
• 
  A table is a grouping of related data organized in fields (columns) and records (rows) on a datasheet. By using a common field in two tables, the data can be combined. Many tables can be stored in a single database.
  
• 
  A field is a column on a datasheet and defines a data type for a set of values in a table. For a mailing list table might include fields for first name, last name, address, city, state, zip code, and telephone number.
  
• 
  A record in a row on a datasheet and is a set of values defined by fields. In a mailing list table, each record would contain the data for one person as specified by the intersecting fields.
  

Tables

Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. Access provides three ways to create a table for which there are icons in the Database Window. Double-click on the icons to create a table.

the Database Window

• 
  Create table in Design view will allow you to create the fields of the table. This is the most common way of creating a table and is explained in detail below.
  
• 
  Create table using wizard will step you through the creation of a table.
  
• 
  Create table by entering data will give you a blank datasheet with unlabelled columns that looks much like an Excel worksheet. Enter data into the cells and click the Save button.
  

Creating a Table in Design View

Design View will allow you to define the fields in the table before adding any data to the datasheet. The window is divided into two parts: a top pane for entering the field name, data type, and an option description of the field, and a bottom pane for specifying field properties.

• 
  Field Name - This is the name of the field and should represent the contents of the field such as "Name", "Address", "Final Grade", etc. The name can not exceed 64 characters in length and may include spaces.
  
• 
  Data Type is the type of value that will be entered into the fields.
  

• 
  Text - The default type, text type allows any combination of letters and numbers up to a maximum of 255 characters per field record.
  
• 
  Memo - A text type that stores up to 64,000 characters.
  
• 
  Number - Any number can be stored.
  
• 
  Date/Time - A date, time, or combination of both.
  
• 
  Currency - Monetary values that can be set up to automatically include a dollar sign ($) and correct decimal and comma positions.
  
• 
  AutoNumber - When a new record is created, Access will automatically assign a unique integer to the record in this field
  
• 
  Yes/No - Use this option for True/False, Yes/No, On/Off, or other values that must be only one of two.
  
• 
  OLE Object - An OLE (Object Linking and Embedding) object is a sound, picture, or other object such as a Word document or Excel spreadsheet that is created in another program. Use this data type to embed an OLE object or link to the object in the database.
  
• 
  Description (optional) - Enter a brief description of what the contents of the field are.
  

Queries

Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future. The query will be updated whenever the original tables are updated.

Creating a Query in Design View

Follow these steps to create a new query in Design View:

• 
  From the Queries page on the Database Window, click the New button.
  

• 
  Select Design View and click OK.
  
• 
  Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query.
  
• 
  Click Close when all of the tables and queries have been selected.
  

• 
  Add fields from the tables to the new query by double-clicking the field name in the table boxes or selecting the field from the Field: and Table: drop-down menus on the query form. Specify sort orders if necessary.
  

• 
  Enter the criteria for the query in the Criteria: field. The following table provides examples for some of the wildcard symbols and arithmetic operators that may be used. The Expression Builder can also be used to assist in writing the expressions.
  

  Query Wildcards and Expression Operators
  Wildcard / Operator	Explanation
  ? Street	The question mark is a wildcard that takes the place of a single letter.
  43th *	The asterisk is the wildcard that represents a number of characters.
  <100	Value less than 100
  >=1	Value greater than or equal to 1
  <>"FL"	Not equal to (all states besides Florida)
  Between 1 and 10	Numbers between 1 and 10
  Is Null Is Not Null	Finds records with no value or all records that have a value
  Like "a*"	All words beginning with "a"
  >0 And <=10	All numbers greater than 0 and less than 10
  "Bob" Or "Jane"	Values are Bob or Jane

• 
  After you have selected all of the fields and tables, click the Run button on the toolbar.
  
• 
  Save the query by clicking the Save button.
  

Forms

Forms are used as an alternative way to enter data into a database table.

Creating a Form by Using Wizard

To create a form using the assistance of the wizard, follow these steps:

• 
  Click the Create form by using wizard option on the database window.
  
• 
  From the Tables/Queries drop-down menu, select the table or query whose datasheet the form will modify. Then, select the fields that will be included on the form by highlighting each one the Available Fields window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select Fields, click the double right arrow button >>. If you make a mistake and would like to remove a field or all of the fields from the Selected Fields window, click the left arrow < or left double arrow << buttons. After the proper fields have been selected, click the Next > button to move on to the next screen.
  

• 
  On the second screen, select the layout of the form.
  

• 
  Columnar - A single record is displayed at one time with labels and form fields listed side-by-side in columns
  
• 
  Justified - A single record is displayed with labels and form fields are listed across the screen
  
• 
  Tabular - Multiple records are listed on the page at a time with fields in columns and records in rows
  
• 
  Datasheet - Multiple records are displayed in Datasheet View
  

Click the Next > button to move on to the next screen.

• 
  Select a visual style for the form from the next set of options and click Next >.
  

• 
  On the final screen, name the form in the space provided. Select "Open the form to view or enter information" to open the form in Form View or "Modify the form's design" to open it in Design View. Click Finish to create the form.
  

Reports

Reports will organize and group the information in a table or query and provide a way to print the data in a database.

Creating a Report by Using the Wizard

Create a report using Access' wizard by following these steps:

• 
  Double-click the "Create report by using wizard" option on the Reports Database Window.
  
• 
  Select the information source for the report by selecting a table or query from the Tables/Queries drop-down menu. Then, select the fields that should be displayed in the report by transferring them from the Available Fields menu to the Selected Fields window using the single right arrow button > to move fields one at a time or the double arrow button >> to move all of the fields at once. Click the Next > button to move to the next screen.
  

• 
  Select fields from the list that the records should be grouped by and click the right arrow button > to add those fields to the diagram. Use the Priority buttons to change the order of the grouped fields if more than one field is selected. Click Next > to continue.
  

• 
  If the records should be sorted, identify a sort order here. Select the first field that records should be sorted by and click the A-Z sort button to choose from ascending or descending order. Click Next > to continue.
  

• 
  Select a layout and page orientation for the report and click Next >.
  

• 
  Select a color and graphics style for the report and click Next >.
  

• 
  On the final screen, name the report and select to open it in either Print Preview or Design View mode. Click the Finish button to create the report.
  

Macros
When you want to control the flow of activities in the application there are several ways you can go. We'll look at a couple of them.

First, if you don't want to use the Menu Manager discussed in the previous lesson, you can create your own menu using a simple form, as in the example below.



If you only have a form with buttons, nothing much will happen. You have to tell the application what to do when a button is clicked. That's what macros are for.

It's important to save the macro and name it. Give it the same name as the form it's on. That will simplify your life later. If you use macros you'll probably end-up with dozens in the application. Trust me: if you use names like macro1 and macro2 and so on, you'll tear your hair out later in trying to find what each one does. So, name the macro the same as the form it's on and name the actions inside the macro the same as the buttons.

Macros can also be used to open a form from another form.

For example, if you have to change the customer's address while the customer is renting a movie, you don't do the change in the Invoice form. You call the Customers form from the Invoice form. The trick is to make sure that the Customers form that opens is the one for the customer who is on the invoice.

We could create a macro for that.

However, it's more easily done by using the Wizard. Make sure the Wizard is activated in the Toolbox (the magic wand) and it will be called automatically when you create the button.

Finally, you should add navigation buttons to all forms.

You may have noticed that the navigation bar at the bottom of every form, although it is functional, it is small and hard to work. Adding navigation buttons makes it easier for the user.

Every form should have the standard buttons: First, Previous, Next, Last, New record, Close form

Using reports

Reports are printed documents showing information from the database.

Like forms, reports can be based on tables or queries. However, unlike forms, reports are for output only. Obviously, since they are output to the printer, reports cannot accept input from the user.

Creating the report

Before you launch the report editor, know where the information for the report is coming from.

Actually it would be helpful to draw a draft on paper (with a pencil!) to show where to put the columns, how to align them, where to put totals, etc.

If the data is in several tables, you will first create a query to get everything together.

Using the Report wizard is the easiest way of building the report.

The normal form is called tabular, meaning that the information will appear as a table with headings at the top of the page.

For the first example we will print a simple list of customers.

Editing the report

In design mode you can edit the layout of the report.

The first draft, created by the Wizard, needs some work. To fit all the columns on the page, some has to be shortened, etc.

Note that the important section is Detail. That's where the data is printed. Headers and footers contain headings and summaries.

The height of each section can be adjusted by clicking on the lower margin of the section.

You edit the contents of the headings, titles, etc. like on the form editor.