Access Solutions

Tips, Tricks, and Secrets from Microsoft Access MVPs

Arvin Meyer, Douglas J. Steele

• Publisher: Wiley
• Year: 2010
• ISBN:
  9780470947548
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

Two Microsoft Access MVPs show how you can become an Access power user

• Microsoft Access is the world's leading database system, with millions of users and hundreds of thousands of developers. The best practices, tips, and techniques in this book can turn users into power users.
• Millions of eager users make Access the most popular database system in the world
  These Microsoft MVPs exploit key features in Access, providing advice on techniques for capturing, sharing and reporting Access data.
• Each tip provides detailed solutions with clear instructions for implementation, and samples of all can be found on the companion Web site

Access 2010 Solutions offers professional advice that enables every Access user to get greater value from the Access database system.

Part I

Tables

In This Part

Tip 1: Creating Tables for Web Applications: An Introduction to Calculated Fields

Tip 2: Alternative to the Hyperlink Data Type

Tip 3: Fill a Table with Numbers

Access is a relational database management system (RDBMS), so it’s critical that the tables be properly designed for any application to be successful. We felt that it wasn’t appropriate to provide table-related tips in isolation, but there were a few items that we thought were worth including.

Access 2010 introduces a new concept of calculated table fields in order to be consistent with SharePoint. Note that the use of calculated fields is not limited to use in Access web applications. However, be aware that some of the new field types, including the calculated, attachment, and lookup fields, are not traditionally used in classical relational design. As such, they may not scale well to other systems. Even the traditional field type, hyperlink, has alternatives worth considering, as you will see in Tip 2.

Tip 1

Creating Tables for Web Applications

An Introduction to Calculated Fields

Objective

This Tip introduces you to designing tables for Web-based applications.

Tables in Access 2010 have a new feature called calculated fields that allows expressions (calculations) to be stored in a table. Whereas this new feature is available for both Web and client applications, it has greater importance in Web-based applications. This is due to the importance of moving program and application logic out of forms and into the back-end Web server.

When you design a table for Web Services in Access, you do not have the traditional Design View for Access tables. When building a Web-based table, you are restricted to using this Table Layout View, which was introduced in Access 2007.

Web-based forms have substantially fewer program features then the standard client VBA–enabled forms that traditional developers have become so accustomed to over the years. Thus, you need to use shortcuts like calculated fields as a means to add additional functionality to your Web-based applications.

Scenario

In this Tip, you learn how to build a table that allows you to enter a City value into a table and use a calculated field to store a resulting URL that points to a weather website for the given city.

For longtime Access developers, having calculated expressions occur at the table level is a relatively new concept. Crossing the bridge into Web-based development results in forms that run on the user’s desktop inside a Web browser. This means that code you write in a form also runs on the user’s desktop. It follows that code running on the desktop inside a Web browser has limited ability to manipulate data directly in the table that is now sitting on the Web server. For Web applications, this is a significant change for developers, who by nature stuff as much code and logic as possible into Access forms. The new goal in building Web applications is to move program logic, expression logic, and calculation logic out of the form running on the user’s desktop and into the data tables, which are located on the Web server.

Tables

To create Web-based forms and tables, you must first create a Web database, as shown in Figure 1-1.

Figure 1-1: You must choose a Web database to create Web-based applications.

Note The file extension for Web databases is the same accdb extension as for regular databases. However, the Web file format is different from that for regular Access databases. For this reason, it’s a good idea to come up with some type of naming convention to distinguish between regular Access datab...