An Access desktop database 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.
You can use an Access desktop database (.accdb) either as a stand-alone RDBMS on a single workstation or in a shared client/server mode across a network. A desktop database can also act as the data source for data displayed on Web pages on your company intranet. When you build an application with an Access desktop database, Access is the RDBMS. You can also use Access to build applications in a project file (.adp) connected to Microsoft SQL Server, and you can share the server data with other applications or with users on the Web. When you create an Access project file, SQL Server (or the Microsoft SQL Server Desktop Engine-MSDE) is the RDBMS.
Access 2000, 2002 (XP), and 2003 databases use the .mdb file format, but Office Access 2007 introduces a new file format with an .accdb extension. To maintain maximum backward compatibility, Access 2007 can still open, run, and save .mdb databases created in the Access 2000 or Access 2002–2003 .mdb formats, but in order to take advantage of all the new features in Access 2007, you need to use the new .accdb file format. If you have to create an Access application that will be run by users with previous versions of Access, you should use the Access 2000 or Access 2002–2003 .mdb file formats.
As you work 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 same 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 data types 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 the user 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 an Access desktop database (.accdb), you have complete flexibility to define your data (as text, numbers, dates, times, currency, Internet hyperlinks, 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. When you connect an Access project (.adp) to an SQL Server database, SQL Server provides all these capabilities.
Because Access is a state-of-the-art application for Windows, you can use all the facilities of ActiveX objects and ActiveX custom controls. ActiveX controls extend the power of Access by allowing you to integrate custom objects created by Microsoft and other software vendors into your database applications. Within your Access forms and reports, for example, you can include ActiveX custom controls to enhance the operation of your application for your users. 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 it from a calendar picture. One of the ActiveX controls that you can use in an Access application is the calendar control that provides just such a graphical interface. This control is used in a pop-up form in the Conrad Systems Contacts database that is included with this book. You can see this form in Figure 1–1.
Figure 1–1: Choose a date using the ActiveX calendar control.
The user can type dates anywhere in the application or click a button next to any date value to open the ActiveX calendar pop-up form.The user can choose a different month or year from the drop-down list boxes on the control, and the control displays the appropriate month. When the user clicks a specific date on the calendar and then clicks Save in the pop-up form, the control passes the date back to the form to update the date field in the record. If you purchase the Office Access 2007 Developer Extensions, 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 Access.
Office Access 2007 includes a new Attachment data type that can store images and other file types within the record. The Attachment data type can handle multiple attachment files per record via the use of a concept called Complex Data. In previous versions of Access, storing images and files through OLE Object data types caused significant bloat of the database file, but in version 2007, Access compresses these files to minimize the size overhead. Examples of files that could be attached to a record using the Attachment data type include a cover letter created in Microsoft Word for each business contact, a bitmap picture of the contact person, or various sales worksheets created in Microsoft Excel. Figure 1–2 shows an example of a form using the Attachment data type to display a contact picture in the Issues template database that comes with Access. (You can find a database created using this template, IssuesSample.accdb, loaded with sample data on the companion CD.)
Figure 1–2: The Attachment data type displays a picture in a form.
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 SQL Server, Oracle, and DB2. Access 2007 has added enhanced functionality to work with Microsoft Windows SharePoint Services (version 3).
Working with data in an RDBMS is very different from working with data in a word processing or spreadsheet program. 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. You can update a single field or many records with a single command. You can write programs that use RDBMS commands to fetch data you want to display and allow the user to update.
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 keystrokes, you can build a complex query in a matter of seconds.
Figure 1–3 shows a complex query used in the desktop database version of the Conrad Systems Contacts application. You can find this query in the Contacts.accdb 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.
Figure 1–3: This query will retrieve information about products owned by contacts in the Conrad Systems Contacts sample application.
To create the query, you add the tables containing the data you need to the top of the query design grid, select the fields you want from each table, and drag them to the design grid in the lower part of the window. Choose a few options, type any criteria, and you’re ready to have Access select the information you want.
Figure 1–4 shows the same query in the project file version of the Conrad Systems Contacts application, Contacts.adp. You can see that the design interface is similar but also provides an SQL pane so that you can watch Access build the SQL for your query as you work. You don’t need to be an expert to correctly construct the SQL syntax you need to solve your problem, but you can learn a lot about SQL in Chapter 27, “Building Queries in an Access Project,” and in Article 2, “Understanding SQL,” both found on the companion CD. For certain advanced types of queries, you’ll need to learn the basics of SQL.
Figure 1–4: Here is the project file version of a query to retrieve information about products owned by contacts.
Figure 1–5 shows the result of asking the query to return its data.
Figure 1–5: The query returns a list of contacts and the products they own.
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. Although spreadsheets are useful for providing templates for simple data entry, 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, the company needs 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 give you the flexibility to allow multiple users to 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 confirming a new order for a customer, you probably want to know that both the inventory for ordered products is updated and the order confirmation is saved 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.
The new .accdb file format in Office Access 2007 no longer supports user-level security through workgroup files for data control. Properly securing an Access database using user-level security was a difficult process for most users to undertake. If any important steps in the process were missed or applied incorrectly, the database would not be properly secured. In some cases it was easy to overlook that a step was missed, which would leave an unknown security hole in the Access database file. Although difficult to set up, user-level security was unfortunately extremely easy to break with tools and utilities available on the Internet It was simply not possible to fix Access user-level security to comply with Microsoft’s current software security standards. For backward compatibility, user-level security defined in databases left in the older .mdb format will continue to be supported. Access 2007 will still allow you to design and modify database object permissions with user-level security if the file is left in the .mdb format Access 2007 will even continue to honor existing security settings in .mdb and .adp files, but it will not support any of these features in the new .accdb file format. To provide the best security for shared data, Microsoft recommends moving your data to SQL Server or to Microsoft Office SharePoint Server.
Because you can share your Access data with other users, you might need to set some restrictions on what various users are allowed to see or update. Access 2007 has greatly improved the ability to share data with secured Windows SharePoint Services Version 3 lists to ensure data security. With SharePoint-to-Access integration, users can take advantage of improved workflow support, offline SharePoint lists, and a Recycle Bin to undo changes. Access 2007 also has improved data encryption with tougher encryption algorithms. Access automatically provides locking mechanisms to ensure that no two people can update an object at the same time, and 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.