Microsoft Access as an RDBMS
Microsoft Access is a fully functional RDBMS. It provides all the data definition, data manipulation, and data control features you need to manage large volumes of data.
Data Definition and Storage
While you're working with a document or a spreadsheet, you generally have complete freedom to define the contents of the document or each cell in the spreadsheet. Within a given page in a document, you might include paragraphs of text, a table, a chart, or multiple columns of data displayed with multiple fonts. Within a given column on a spreadsheet, you might have text data at the top to define a column header for printing or display, and you might have various numeric formats within the column, depending on the function of the row. You need this flexibility because your word processing document must be able to convey your message within the context of a printed page, and your spreadsheet must store the data you're analyzing as well as provide for calculation and presentation of the results.
This flexibility is great for solving relatively small, well-defined business problems. But a document becomes unwieldy when it extends beyond a few dozen pages, and a spreadsheet becomes difficult to manage when it contains more than a few hundred rows of information. As the amount of data grows, you might also find that you exceed the data storage limits of your word processing or spreadsheet program or of your computer system. If you design a document or spreadsheet to be used by others, it's difficult (if not impossible) to control how they will use the data or enter new data. For example, on a spreadsheet, even though one cell might need a date and another a currency value to make sense, a user might easily enter character data in error.
Some spreadsheet programs allow you to define a "database" area within a spreadsheet to help you manage the information you need to produce the desired result. However, you are still constrained by the basic storage limitations of the spreadsheet program, and you still don't have much control over what's entered in the rows and columns of the database area. Also, if you need to handle more than number and character data, you might find that your spreadsheet program doesn't understand such things as pictures or sounds.
An RDBMS allows you to define the kind of data you have and how the data should be stored. You can also usually define rules that the RDBMS can use to ensure the integrity of your data. In its simplest form, a validation rule might ensure that you can't accidentally store alphabetic characters in a field that should contain a number. Other rules might define valid values or ranges of values for your data. In the most sophisticated systems, you can define the relationship between collections of data (usually tables or files) and ask the RDBMS to ensure that your data remains consistent. For example, you can have the system automatically check to ensure that every order entered is for a valid customer.
With Access, you have complete flexibility to define your data (as text, numbers, dates, times, currency, Internet links, pictures, sounds, documents, and spreadsheets), to define how Access stores your data (string length, number precision, and date/time precision), and to define what the data looks like when you display or print it. You can define simple or complex validation rules to ensure that only accurate values exist in your database. You can request that Access check for valid relationships between files or tables in your database.
Because Access is a state-of-the-art application for Microsoft Windows, you can use all the facilities of Dynamic Data Exchange (DDE), ActiveX objects, and ActiveX custom controls. DDE lets you execute functions and send data between Access and any other Windows-based application that supports DDE. You can also make DDE connections to other applications using macros or Microsoft Visual Basic. ActiveX is an advanced Windows capability that, in part, allows you to link objects to or embed objects in your Access database. Objects include pictures, graphs, spreadsheets, and documents from other Windows-based applications that also support ActiveX. Figure 1-1 shows embedded object data from the sample Northwind Traders database that ships with Access. You can see a product category record that not only has the typical name and descriptive information but also has a picture to visually describe each category. Access 2000 can also act as an ActiveX server, allowing you to open and manipulate Access database objects (such as tables, queries, and forms) from other Windows-based applications.
Attached Figure 1-1. The Categories form in the Northwind Traders sample database.
Within your Access forms and reports, you can include ActiveX custom controls to enhance the operation of your application. ActiveX controls provide sophisticated design objects that allow you to present complex data in a simpler, more graphical way. Most ActiveX controls provide a rich set of "actions" (called methods in object terminology) that you can call from a procedure and properties you can set to manage how the control looks and behaves. For example, you might want to let your user enter a date by selecting from a calendar picture. You could laboriously build a "calendar" form that has sets of boxes arranged in rows of seven columns and write lots of code to let the user "scroll" to the "next" or "previous" month and then click in a box to pick the date. Access 2000 comes with a standard ActiveX calendar control that takes care of all the details for you. This control is used in the Contracts form in the Entertainment Scheduling database that is included with this book. You can see this form in Attached Figure 1-2.
Attached Figure 1-2. Choosing a date using the ActiveX calendar control.
The user can type in contract start and end dates or click a down-arrow button to reveal the ActiveX calendar control. The user can choose a different month or year from the drop-down boxes on the control, and the control displays the appropriate month. When the user clicks a date on the calendar control, the control passes the date back to the form to update the date field in the record. If you purchase Microsoft Office 2000 Developer, you will have several additional ActiveX controls available to use in your applications. Many third-party software vendors have built libraries of ActiveX controls that you can purchase for use with Microsoft Access.
Access can also understand and use a wide variety of other data formats, including many other database file structures. You can export data to and import data from word processing files or spreadsheets. You can directly access Paradox, dBASE III, dBASE IV, Microsoft FoxPro, and other database files. You can also import data from these files into an Access table. In addition, Access can work with most popular databases that support the Open Database Connectivity (ODBC) standard, including Microsoft SQL Server, Oracle, DB2, and Ingres.
Data Manipulation
Working with data in a word processing or spreadsheet program is very different from working with data in an RDBMS. In a word processing document, you can include tabular data and perform a limited set of functions on the data in the document. You can also search for text strings in the original document and, with ActiveX, include tables, charts, or pictures from other applications. In a spreadsheet, some cells contain functions that determine the result you want, and in other cells you enter the data that provides the source information for the functions. The data in a given spreadsheet serves one particular purpose, and it's cumbersome to use the same data to solve a different problem. You can link to data in another spreadsheet to solve a new problem, or you can use limited search capabilities to copy a selected subset of the data in one spreadsheet to use in problem-solving in another spreadsheet.
An RDBMS provides you with many ways to work with your data. You can, for example, search a single table for information or request a complex search across several related tables or files. You can update a single field or many records with a single command. You can write programs that use RDBMS facilities to read and update your data. Many systems provide data entry and report generation facilities.
Access uses the powerful SQL database language to process data in your tables. (SQL is an acronym for Structured Query Language.) Using SQL, you can define the set of information that you need to solve a particular problem, including data from perhaps many tables. But Access simplifies data manipulation tasks. You don't even have to understand SQL to get Access to work for you. Access uses the relationship definitions you provide to automatically link the tables you need. You can concentrate on how to solve information problems without having to worry about building a complex navigation system that links all the data structures in your database. Access also has an extremely simple yet powerful graphical query definition facility that you can use to specify the data you need to solve a problem. Using point and click, drag and drop, and a few keyboard strokes, you can build a complex query in a matter of seconds.
Attached Figure 1-3 shows a complex query under construction in Access. You can find this query in the Entertainment Scheduling sample database on the companion CD included with this book. Access displays field lists from selected tables in the upper part of the window; the lines between field lists indicate the automatic links that Access will use to solve the query.
To create the query, you simply select the fields you want from the upper part of the window and drag them to the design grid in the lower part of the window. Choose a few options, type in any criteria, and you're ready to have Access select the information you want.
Attached Figure 1-3. A query to retrieve club and contract information from the Entertainment Scheduling database.
Attached Figure 1-4 shows an example of an SQL statement that Access automatically creates from your specifications in the design grid. You don't need to be an expert to correctly construct the SQL syntax you need to solve your problem, but as you'll learn in Chapter 11, "Advanced Query Design—SQL" you can specify the SQL yourself for certain advanced types of queries. Attached Figure 1-5 shows the result of running the query.
Attached Figure 1-4. The SQL statement for a query to retrieve club and contract information.
Attached Figure 1-5. A list of clubs and their contracts.
Data Control
Spreadsheets and word processing documents are great for solving single-user problems, but they are difficult to use when more than one person needs to share the data. Spreadsheets are also useful for providing templates for simple data entry, but they don't do the job well if you need to perform complex data validation. For example, a spreadsheet works well as a template for an invoice for a small business with a single proprietor. But if the business expands and a number of salespeople are entering orders, you need a database. Likewise, a spreadsheet can assist employees with expense reports in a large business, but the data eventually must be captured and placed in a database for corporate accounting.
When you need to share your information with others, true relational database management systems allow you to make your information secure so that only authorized users can read or update your data. An RDBMS that is designed to allow data sharing also provides features to ensure that no two people can change the same data at the same time. The best systems also allow you to group changes (a series of changes is sometimes called a transaction) so that either all of the changes or none of the changes appear in your data. For example, while entering new order information for a customer, you probably want to know that all items are recorded or, if you encounter an error, that none of the changes are saved. You probably also want to be sure that no one else can view any part of the order until you have entered all of it.
Access is designed to be used either as a stand-alone RDBMS on a single workstation or in a shared client/server mode across a network. Access can also act as the database server for data displayed on Web pages on your company intranet. Because you can share your Access data with other users, Access has excellent data security and data integrity features. You can define which users or groups of users can have access to objects (such as tables, forms, and queries) in your database. Access automatically provides locking mechanisms to ensure that no two people can update an object at the same time. Access also understands and honors the locking mechanisms of other database structures (such as Paradox, FoxPro, and SQL databases) that you attach to your database. In addition, Access lets you create multiple copies of a "master" database through a process called replication. Several remote users can have their own copies of the database, and they can each use utilities built into Windows and Access to periodically synchronize their copies.
 Attachments
Figures Understanding (34543-3136-MS-Access-as-an-RDBMS-Figures.zip)
|