A Look At Microsoft Access

A Look at Microsoft Access

Introduction

Team A of DBM/405 has chosen to look at Microsoft Access. This paper will examine Microsoft Access and its capabilities and benefits. It should show that Microsoft Access is one of the easiest user friendly database and cost effective database applications today. This paper will also examine the use of Access for a business called Parrothead Productions. It is a small one-man operation, which is a remote disc jockey business.

The Capabilities of Microsoft Access

Microsoft Access database gives you true command of your data, enabling you to retrieve it, sort it, analyze it, summarize it, and report results in moments. It can combine data from various files, so that you never have to enter information twice. It can even make data entry more efficient and accurate. Microsoft Access creates relational databases, which mean that data is stored in various separate tables by subject or task, but the data is related and can be brought together in ways that you specify. Microsoft Access creates relational databases, which means that data is stored in various separate tables by subject or task, but the data is related and can be brought together in ways that you specify. Relationships link data between two or more tables to increase its usefulness.

Access consists of objects such as, Tables, Queries, Forms and Reports. Tables; store your data in rows and columns, Queries; retrieve and process your data. They can combine data from different tables, update your data, and perform calculations on your data. Forms; control data entry and data views. They provide visual cues that make data easier to work with. Reports; summarize and print your data. They turn the data in your tables and queries into documents for communicating ideas.

Tables store data, so they're essential building blocks of any database. Each table contains rows called records and columns are called fields. A record is a collection of facts about a particular person, event, or other item of interest. A field is a single kind of fact that may apply to each person, event, or other record. The fields in our database have settings that determine the type of data they can store, how the data is displayed and what we can do with the data. An important setting for fields is the data type, including number, text, currency, date and time. The data type limits and describes the kind of information in the field. The data type also determines the actions we can perform on a field and how much memory data uses. Fields also have properties that control the details of information inside them, including a character length, a default value, and a validation rule that makes sure the data meets certain criteria. The properties make it easier to enter and manage data.

You may have heard no two people are the same; this characteristic also applies to records in a well-structured database. Each record in each table should be unique, should distinguish one record from another, tables can contain a primary key field. The primary key field is an identifier and should be a piece of information that won't change frequently. If you like, Access can assign a numeric primary key that increases by one each time you add a record to a table. The number continues to be associated with this record, even if you add and delete other records entered before this record in your database. The database can associate each primary key with a friendly name so you can work with familiar information, even though the underlying table is storing a number.

A primary key separates similar information and makes each record unique. It also brings information together. You relate one table to another using a primary key. This is how tables share data, and how you can avoid repeating information in both the tables. When tables relate, the primary key of one table becomes a foreign key of the other table.

Queries can answer those questions by assembling stored data from your database, or by performing calculations with the data to provide further information. To answer questions, queries retrieve, filter, sort, and assemble data on command. A query finds data and shows it to you, it can also process that data according to your instructions. Another important power of queries is to combine the data from several tables into a single view. A query can also remove data.

Forms control and simplify data input. Forms make data from a table or a query easier to understand by presenting it in visually appealing designs. Forms can also provide a start-up screen, with easy ways to launch database tasks. Forms provide drop-down lists, instructions, navigational controls, and graphics to help users work with your data. In more than one way, forms make data friendlier.

Using reports, you can group your data, perform calculations on it, and add headings and other formatting to make it more meaningful and easier to read. Reports convert data into documents. Reports come in various shapes and sizes. Reports provide methods to format the printed appearance of your data in the ways that are most effective for your purpose.

A data access page is a web page that is connected directly to your database. It enables you and other to view and work with your data online, dynamically. Use data access pages to view, edit, update, delete, filter, group, and sort data, as you do using forms and reports. What's different is that the page exists outside of your database, so that users can update or view data over the World Wide Web or an intranet.

As your business grows and demands more high-level performance from your database system, you may have to move from the file-server environment of the Microsoft® Access 2000 Jet engine to the client/server environment of Microsoft SQL Server™ 2000. You may be considering migrating (upsizing) your database to SQL Server because:

a) As the number of users increases, the file-sharing mechanism of a Microsoft Access database may result in a slower performance and increased network traffic. The client/server environment of Microsoft SQL Server is designed for a large number of users and minimizing network traffic. Microsoft SQL Server 2000 can support databases of up to 2 terabytes in size.

b) SQL Server 2000 is an XML-enabled database server that allows you to access the server through a URL, and to write and retrieve data as XML documents. Integration of Microsoft SQL Server's security with the security of a Microsoft Windows NT® or Microsoft Windows® 2000 network allows easier administration of complex network security settings.

c) SQL Server can be backed up and restored dynamically while the database is in use. Users