Microsoft Access 2003


Microsoft Access 2003 is architecturally different than SQL Server 2000. Access is designed to enable enterprise-wide data access using a client/server model to corporate information. The client/server-based architecture is designed to provide the links to access and manipulate data regardless of its location and storage format. Using this type of data model as a pattern of database development allows SQL Server to act as the next scalable design pattern. Inherently, applications will grow and increase both in their size and complexity within their lifetime. Utilizing the client/server application model, Access provides a simplified graphical user environment that rapidly enables application developers and end users to create applications. One of the benefits of platform architectures like .NET is that the common event model is used as the basis of all application design and development. This rapidly enables application users and developers to scale their applications quickly and easily.

Microsoft Access is a simplified relational database. The benefits of Access are really in that it allows users to easily create and modify database structures and applications using a variety of built-in wizards. Additionally, the familiar look and feel of Microsoft Office makes Access an ideal solution for applications requiring the client/server architecture and less than two gigabytes of data storage.

Database Architecture

All database objects within Microsoft Access are maintained within a single MDB file. At the top level is the Access user interface, which enables developers and end users to create and manage the relation components. The interface components and functions are stored within the Access relational Jet database. This database contains the following information:

Data Access Objects: A high-level object-oriented data access language that is both a Data Definition Language (DDL) and Data Manipulation Language (DML) for Access.

Query Manager: A sophisticated query processor that builds SQL statements based on a cost-based optimizer that manages query objects and their result sets. This optimizer enables heterogeneous updateable joins using a query-on-query model. These joins are a unique feature of Microsoft Access that allows the creation of updateable queries against different tables in a different type of databases. For example, these joins allow you to update an Access table and a SQL Server data table with one query. The query manager is also responsible for deciding when to send queries to the Indexed Sequential Access Methods (ISAM) manager or the remote manager.

ISAM Manager: This manager provides access to various foreign ISAMs and has a native (local-to-Jet) ISAM called the Jet ISAM.

Remote Manager: The remote manager is responsible for receiving queries from the query manager and then makes the appropriate ODBC API calls to retrieve and pass data back to the query manager.

The lowest level of the Jet database is the ODBC layer. This layer enables Access to connect to any ODBC-compliant database. This is designed to enable Access to serve as the front end for a variety of database sources like SQL Server and enhances the idea of enterprise-wide connectivity.

The Jet Engine

Version 4 of the Jet Engine contained within Access 2003 is a mixture of both ANSI-89 Level 1 and ANSI-92 SQL syntax. Additionally, when you use the OLE DB provider within Access, additional keywords and features that are not directly supported within ANSI SQL are exposed. The major differences between these two types of SQL syntax and Microsoft 2003 are:

  • Different rules are applied to the BETWEEN AND SQL construct. This SQL feature has the following syntax:

     Syntax: expr[1] [NOT] Between value 1 and value 2 
  • Jet allows value 1 to be greater than value 2. With ANSI SQL, value 1 must be equal or less than value 2.

  • Microsoft Jet SQL supports both the ANSI SQL wildcard characters and the Microsoft Jet-specific wildcard characters to use with the LIKE operator. The use of the ANSI and Microsoft Jet wildcard characters is mutually exclusive. ANSI SQL wildcards are available only when you re using Jet 4.X and the Microsoft OLE DB provider for Jet. When the ANSI SQL wildcards are used through Microsoft Access or DAO, they are interpreted as literals. When used through the OLE DB provider for Jet and Jet 4.X, they are not. Table 5.4 shows the major pattern match syntax requirements.

    Table 5.4: Pattern match syntax requirements.

    Matching Character

    Microsoft Jet SQL

    ANSI SQL

    Any Single Character

    ?

    _(Underscore)

    Zero or More Characters

    *

    %

  • Microsoft Jet SQL is considered less restrictive and allows grouping and expression ordering. This natively enables more powerful and ordered expressions.

Access Components

Unlike SQL Server, which provides integrated security access within both the table and database structure, Access supports a workgroup information file that it reads on startup and that contains information about the users in the workgroup. This information includes user account names , password, and the groups of which they are members . This enables Access to function as a multi-user database and define permissions around the database attributes and objects that a specific user or group may have access to.

The structure of the Access database makes multi-user access very different than with SQL Server. When looking to provide an Access database to users in a networked environment, you should keep the following guides in place:

  • Place the entire application on a network server and allow multiple users to open the database.

  • Split the database into two databases. This creates a back-end database, which contains the application tables, and a front-end database, which contains the application objects. The back-end database is then placed on a network server, while the front-end database is distributed to the application users. The benefit of this approach is to reduce the network traffic needed to run the application. The downside is that all application access requires connectivity to the back-end database.

When running in a multi-user environment, Access consists of several components. In addition to the access database, a multi-user Access database contains a workgroup information file and a locking database. The workgroup information file (.mdw) is the file that stores information about users in the workgroup. The other component is the locking information (.ldb) file for each database. This file resides in the same folder as the database.

The workgroup information (.mdw) file is a Microsoft Access database that stores information about users in a workgroup, including their account names, their passwords, and the groups that they belong to. It also stores preference information that each user can specify in the Tools Options dialog box.

The location of the workgroup information file in multi-user environments is important. It can either be placed on each local workstation or shared on a network server. However, if it s stored locally, you must take the steps necessary to update it when security settings change. The workgroup information file also stores a list of the most recently used databases. If the database design dictates that users must have their own lists of the most recently used database, then these need to be stored on the individual users workstations and a schema for user-level security has to be designed.

 CD-ROM     The locking information (.ldb) file stores information about any records that are currently locked in the database. If the locking information file doesn t exist when the database is opened, the Microsoft Jet database engine creates it. It creates one locking information file for every Microsoft Access database file that is opened in shared mode. Microsoft Jet gives the file the same name as the opened database but with an .ldb extension. For example, the sample  Inventory.mdb has an associated locking information file called Inventory.ldb (see \Code\Chapter 5\Access Product\ProductActivity.xsn on the companion CD-ROM). The locking information file is stored in the same folder as the database. Microsoft Jet deletes the .ldb file when the database is closed.

Database Structure

Unlike with SQL Server, in Access a single MDB file matches a single database. This single file contains all the components that relate to the database. Table 5.5 shows the components that are stored as part of the database.

Table 5.5: Components stored within an Access 2003 database.

Component

Description

Tables

The tables are the backbone and the storage container of the data entered into the database. If the tables are not set up correctly with the correct relationships (see the next entry in the table), then the database may be slow, may give you the wrong results, or may not react the way you expect. Therefore, take a bit of time when setting up your tables. All actions ”i.e., queries, forms, etc. ”must be based on or contained in a table. When opened, these tables look similar to a Microsoft Word or Microsoft Excel spreadsheet. They have columns and rows. Each column will have a field name at the top, and each row will represent a record.

Relationships

Relationships are the bonds built between the tables. They join together tables that have associated elements. In order for you to relationally join tables, there must be a common field between the two tables.

Queries

Queries are the means of manipulating the data to display in a form or a report. Queries can sort , calculate, group, filter, join tables, update data, delete data, etc. In the early stages of learning Access, you don t need to know SQL. Microsoft Access writes the SQL for you, after you tell it what you want, in the design view of the queries window.

Forms

Forms are the primary interface where users enter data. The person who enters the data will interact with forms regularly. By using queries, properties, macros, and Visual Basic for Applications (VBA) , the programmer can set the forms to show only the data required. The ability to add, edit, and delete data can also be set. Forms can be set up very individually to reflect the use they are required for.

Reports

Reports are the results of the manipulation of the data entered into the database. Unlike forms, they cannot be edited. Reports are intended to be used to output data to another device or application; i.e., printer, e-mail, or Microsoft Word or Excel.

Macros

Macros are an automatic way for Access to carry out a series of actions for the database. Access lets you select actions that are carried out in the order you enter. Macros can open forms, run queries, change values of a field, or run other macros.

Modules

A module is a window where developers write and store VBA. Advanced users of Microsoft Access tend to use VBA instead of macros.

Data Access Page

Data access pages are Web pages published from an Access database that have a connection to the database. These pages also allow you to connect to other data sources that can be pushed to the Web; these include Excel and SQL Server.

All components of Access are persistent. This is a design feature that saves component changes not only when the database is closed but also when a developer moves from one component to another. This is different that other Office applications like Word, which saves the entire document only when it is closed.

Database Relationships

Tables contain definitions that relate to objects. These object definitions may require several tables of related data to fully describe them. Relationships are the object definitions that exist across tables. Once these relationships are defined using SQL, they allow data across multiple tables to be displayed.

Relationships work by matching data in key fields. Usually, these fields contain the same name in both tables. In most cases, these matching fields are the primary keys from one table that provide the unique identifier for each record and a foreign key in the other table. For example, an inventory management application can contain tables that describe products and the related purchase orders that together define an inventory item. Figure 5.10 shows the tables that are used to define the sample Inventory application.

click to expand
Figure 5.10: Database relationships stored in the Inventory.mdb file.

Database relational concepts for both SQL Server and Microsoft Access define different types of table relationships and consist of the following:

One to One: In a one-to-one relationship, each record in a table can have only one matching record in another table. This type of relationship is not common, because most of the related information would be stored in a single table. Often, this type of relationship is used to divide tables with a large amount of fields, isolate sections of a table for security reasons, or store a subset of main tables.

One to Many: One-to-many relationships are the most common database type relationships. In this type of relationship, a single record in one table has many matching records in another table. The other side of the relationship is that many records are related to only a single record in the master table.

Many to Many: In many-to-many relationships, a single record has many matching records in another table, and in that table a single record contains many matching records in the other table. This type of relationship is defined by the use of a third table called a junction table , which provides a primary key that consists of two fields from both tables. These types of relationships are really two one-to-many relationships, with a third table providing object context.

The Shape Provider

 CD-ROM     Using the ADO Adapter within InfoPath, you can extend the Inventory Management Access Application to include InfoPath forms (this is included on the companion CD-ROM in \Code\Chapter 5\AccessProduct\ProductActivity.xsn). Using this application, you can add a form that provides product updates and transaction histories. This type of form utilizes the built-in relationships defined within the application to define the form. The base data source is defined from the initial products table, as shown in Figure 5.11.

click to expand
Figure 5.11: The listing of tables as seen in InfoPath.

Once the base table is selected, additional related tables are added to the data source, as shown in Figure 5.12.

click to expand
Figure 5.12: Using InfoPath to add additional tables to the data source.

As related tables are added, the data source analyzes the existing table structure to determine if these tables have an existing relationship. These relationships are pulled into the data source and shown with the Data Source Wizard in Figure 5.13.

click to expand
Figure 5.13: Selecting the related tables using InfoPath.

Doing this allows the data source to access both defined data sources and define new ones, as shown in Figure 5.14.

click to expand
Figure 5.14: Reviewing the defined relationships within InfoPath.

Once all related tables are added, the data source adapter generates the SQL statement shown in Listing 5.7.

Listing 5.7: The shape provider statement created by the data source adapter.
start example
 shape {select [ProductID],[ProductName],[ProductDescription],[CategoryID],[SerialNumb er],[UnitPrice],   [ReorderLevel],[Discontinued],[LeadTime]   from [Products] as [Products]} as [Products]   append     ({select [TransactionID],[TransactionDate],[ProductID],[PurchaseOrderID],[Transa ctionDescription],       [UnitPrice],[UnitsOrdered],[UnitsReceived],[UnitsSold],[UnitsShrinkage]       from [Inventory Transactions]}       relate [ProductID] TO [ProductID],[UnitPrice] TO [UnitPrice]) as  [Inventory_Transactions] 
end example
 

The underlying Data Source Wizard uses the shape provider to generate a hierarchical recordset for display in InfoPath. The shape provider works by reading the parent and child relationships into a temporary table on the local machine; using the cursor engine dynamically filters the child records to match the current parent record.

The shape provider allows tables to be joined based on relation, parameters, or groups. The relation and parameter joins are similar to the SQL-based JOIN command except that both the child and parent records are read into the local cache before processing (instead of only selected fields). Initially, this type of query requires a higher overhead than a standard SQL JOIN , but once all the fields are cached locally, the overall performance increases .

The parameter-based shape hierarchy reads only the parent records and then fetches the matching child records on demand. This type of statement offers reduced initial overhead, but each new child query generates a database call, and a database connection is always maintained. The group-based hierarchy is the equivalent to the AGGREGATE SQL statement or performing an aggregate function on non-normalized data. This creates a non-updateable recordset that is presented within InfoPath, and all database records are fetched at the start of the connection. Once the data source is complete, the wizard provides summary information that can be used to determine whether the query is updateable, as shown in Figure 5.15.

click to expand
Figure 5.15: Finalizing the Data Source Wizard.

The completed data source provides the structured recordset within the data source window that matches the relationships defined during the wizard. Listing 5.8 shows the persisted data source maintained by InfoPath.

Listing 5.8: SQL query persisted by the InfoPath solution.
start example
 <xsf:query> <xsf:adoAdapter connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Password=&quot;&quot;;   User ID=Admin;Data Source=D:Chapter 5\Inventory.mdb;Mode=Share Deny None;   Extended Properties=&quot;&quot;;Jet OLEDB:System database=&quot;&quot;;   Jet OLEDB:Registry Path=&quot;&quot;;Jet OLEDB:Database Password=&quot;&quot;   ;Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:   Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:   New Database Password=&quot;&quot;;Jet OLEDB:Create System  Database=False;   Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on  Compact=False;Jet OLEDB:   Compact Without Replica Repair=False;Jet OLEDB:SFP=False"   commandText="shape&#xA;  {select  [ProductID],[ProductName],[ProductDescription],   [CategoryID],[SerialNumber],[UnitPrice],[ReorderLevel],[Discontinued],    [LeadTime] from   [Products] as [Products]} as [Products]&#xA;  append&#xA;   ({select [TransactionID],[TransactionDate],[ProductID],[PurchaseOrderID],   [TransactionDescription],[UnitPrice],[UnitsOrdered],[UnitsReceived],    [UnitsSold],   [UnitsShrinkage] from [Inventory Transactions]}&#xA;   relate [ProductID] TO [ProductID],[UnitPrice] TO [UnitPrice]) as   [Inventory_Transactions]" queryAllowed="yes" submitAllowed="yes"> </xsf:adoAdapter> </xsf:query> 
end example
 

The  manifest.xsf maintains the data source that contains the shape statement and based on query design executes that against the database. Using the relational shape provider within Access will cause forms to open more slowly as the size of the database increases.




Programming Microsoft Infopath. A Developers Guide
Programming Microsoft Infopath: A Developers Guide
ISBN: 1584504536
EAN: 2147483647
Year: 2006
Pages: 111
Authors: Thom Robbins

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net