Microsoft Office provides a broad array of tools and technologies for creating multi-user database solutions. Specifically, Access provides tools and features for creating multi-user database solutions by using four different database architectures: file/server, client/server, replication, and Web-based data access pages.
Access Workflow Designer uses SQL Server as the data store. SQL Server can support hundreds to thousands of simultaneous users. However, if you are building either a single-user solution or a multi-user solution for a small team, you can store the data in a Microsoft Data Engine (MSDE) database. MSDE is available on the Microsoft Office Premium CD-ROM.
An MSDE database uses a database engine that is compatible with the one found in SQL Server, but an MSDE database cannot support as many users. Best performance with MSDE is achieved with five or fewer users. The advantage of using MSDE is you can create a SQL Server database from within Access without having SQL Server on your computer. MSDE is a good tool for prototyping and designing an enterprise solution that you can migrate to SQL Server, because you can run an MSDE database under SQL Server without modification.
Note To create a team solution using Access Workflow Designer, you must use either SQL Server or MSDE. Access .mdb databases are not supported. To use Access to design and modify your SQL Server database, you create an Access data project. For details, see Using Access to Create the SQL Server Database.
In previous versions of Access, the only way to create a client/server solution was to create an .mdb file with linked tables that used an ODBC driver to link to a database server such as SQL Server. This kind of client/server solution also required Access to load the Jet database engine to open the database and the linked tables, which created additional memory overhead.
Although Access 2000 continues to support client/server solutions that use linked tables, it also supports a new file format and data access architecture that makes it possible for you to create a client application that connects to a SQL Server 6.5 (with Service Pack 5) or SQL Server 7.0 database through OLE DB without loading the Jet database engine. To do this, you create an Access project file that is saved by using an .adp extension.
An Access project can store forms, reports, data access pages, macros, and Microsoft Visual Basic® for Applications modules locally in your client solution file and use the OLE DB connection to display and work with the tables, views, relationships, and stored procedures that are stored on SQL Server. You create the forms, reports, macros, and Visual Basic for Applications modules in an Access project by using most of the same tools and wizards you use to create these objects in Access databases. This makes it possible for you to develop quickly client/server solutions that work directly against a SQL Server.
Note Although an Access project file uses an OLE DB connection to connect to a database, it can only use the Microsoft OLE DB Provider for SQL Server and can only connect to SQL Server 6.5 (with Service Pack 5) and SQL Server 7.0 databases. This is because the database creation and design tools in Access 2000 can only support SQL Server 6.5 or SQL Server 7.0 databases.
Access also makes it possible for you to create new SQL Server databases and provides a variety of visual tools to create and modify the design of tables, views, stored procedures, triggers, and database diagrams on your database server. The tables, views, and stored procedures you create, as well as SQL SELECT statements, are all valid data sources for Access forms, reports, and data access pages.
In addition to providing you with the ability to create and design client/server solutions from scratch, Access 2000 also includes the Upsizing Wizard, which makes it possible for you to convert an existing Access database to a client/server solution by creating a new SQL Server database linked to an Access client application.
With the addition of Access Workflow Designer, you can add workflow, offline replication, and security, as well as the ability to create a template of your database solution.
Regardless of which data store you choose, designing the database structure is likely to be the most challenging part of building the relational-database solution. In order to understand how the tables in the database should be structured and how they should relate to one another, you must understand the data. Although it is not difficult to modify the data model while you are developing the solution, it is much more challenging once your customers are using the solution. Therefore, it is important to put as much effort as necessary into the process of designing the data model before you begin writing code.
One way to start is to think of all the questions this database must answer. The answers become the columns (or fields) in your tables. How many corporate customers do we have by country? This could require the following columns: customer, country.
In addition to determining the columns in your tables, you must also set up the relationships between tables. Sometimes you may have what appears to be a straightforward one-to-many relationship that turns out to be much more complex.
The following example illustrates some of the issues you must consider when designing a relational database.
Note PK = Primary Key, FK = Foreign Key
Relational Database Design Example: Phase I
The simple one-to-many design with a Customers table with a single relationship to the Orders table has several limitations. For example, all the product information would have to be re-entered on each order. That would slow down the order entry process and could cause problems as products are added or changed.
Relational Database Design Example: Phase II
Adding a Products table to relate product information to the Order table is better, but there is still a major limitation. You can only place one order per order number. Therefore, another table is required to handle the order details.
Relational Database Design Example: Phase III
Adding an OrderDetails table and relating the Products table to this new table handles the relationship better. You can maintain and update the products separately, and each order can have one or many order details. Notice that total is still stored in the Orders table. This column could be updated using code, once the order total was calculated for good record keeping.
The design of your database itself enforces certain rules on the way users can enter data. For example, a user cannot violate a table's primary key by adding a duplicate record. In addition, you can establish custom validation rules using triggers that prevent users from entering invalid data.
Note Currently, Access Workflow Designer does not support multiple triggers within one table.
The Access Workflow Designer automatically tracks foreign-key constraints when you add a main table to the table hierarchy and creates a tree of detail and lookup tables. If you have other related tables for which there are no foreign-key constraints, you can add manually those as lookup tables using the Access Workflow Designer. For information about how Access Workflow Designer manages tables, see Setting up a Table Hierarchy.
Once you have designed the data-storage and data-entry components of your solution, you should begin thinking about how to present and summarize the data in a format that makes sense to users. Although generally not as difficult as database design, determining what data users want to see and building reports to display the data in a usable format can be a challenging task. Preplanning your reports can also lead you to rethink or enhance your database design. Sometimes, the lack of a certain column required for grouping or sorting does not become obvious until the reports are being designed.
Here are some questions to ask yourself as you design the reporting component of a solution:
For information about designing your user interface for a team solution, see Developing the Solution User Interface.