With Microsoft Access, the objects manage themselves. There is no main program. For example, suppose you want something to happen when the user clicks a button. You don't need code that checks to see whether the user clicks the button. You attach the code to the button so it's executed when the Click event occurs. When the event occurs, Microsoft Access runs your code automatically.
Laying the Groundwork
Here are the first steps in building a Microsoft Access application i.e. the steps you take before you build your first form:
Step One: Define the application's purpose and scope You'll design a better application if you start by defining exactly what people are to accomplish when they use your application. Usually, an application's purpose is a specific data management task, such as taking new orders or tracking inventory. Larger systems may encompass many tasks. It's a good idea to write down as clearly as you can all the tasks your application is to accomplish. Talk to the people who will use your application and find out how they accomplish the tasks without your application. Get copies of the paper forms and reports they use; these will be helpful when you start designing the forms and reports in the application.
Step Two: Plan your tables and relationships You'll have an easier time creating an application if you determine how to divide your data into tables. Your tables make up the foundation of your application: If their structure is weak, the whole application will be weak. In fact, you'll probably find it impossible to create the application you want without the right set of tables and relationships. Analyze your data and break it down into tables before you create any other objects. You'll find good ideas and examples in the Table Wizard dialog boxes. (To view the Table Wizard dialog boxes, click the Tables button in the Database window, and then choose the New button. In the New Table dialog box, choose the Table Wizards button.)
Switching to the Microsoft Access Point of View
If you're new to Microsoft Access, you may find that building a Microsoft Access application requires a change in your point of view. For example, if you have experience storing data in a spreadsheet or a single-table database, you might be tempted to simply import your data and start building forms. But data in a spreadsheet is rarely structured the same way it should be structured in a relational database. In the long run, you'll save time by restructuring your data into tables appropriate for the tasks you can perform with a relational database.
If you're a programmer, you might be tempted to start with Access Basic, write some code, and see how it works. But this approach doesn't work in a Microsoft Access application because code runs only in the context of an object and an associated event. Until you build objects, you don't have any use for code. And because of the intelligence you can build into your objects without using Access Basic, you may not need code at all.
Creating the Objects
Once you define the purpose and scope of your application and plan the tables and relationships you need, you're ready to create the objects. Begin by creating the tables and defining the relationships between them. After the tables are in place, Microsoft Access is flexible enough that you can create the other objects in the order that feels most natural to you. Here's one approach that works well:
Step One: Create the tables and add a few records of sample data to each table This makes it easier for you to see whether your forms and reports display the data you want.
Step Two: Create a select query for one of the forms in your application You might start with a form that's used to enter most of the data in the application. Determine what data you want on the form, and then create a select query that contains the data. Creating a select query will test your table structure because you'll be able to see if you can get the data you want from your tables.
Step Three: Create the form, basing it on the select query You can use a Form Wizard to create the form for you.
Step Four: Add the features you need to make the form work Create the macros, event procedures, or other queries you need to support the form.
Step Five: Test your form Does the form store and display data the way you want it to? Work out its bugs before you create the next form.
Step Six: Add other forms and reports When the first form stores and displays data the way you want, start adding other forms and reports. Work on one object at a time, testing its features until you know it works correctly before going on to the next object.
Tip When you name the tables, fields, and other objects in your database, keep in mind that you'll use these names to refer to the objects elsewhere in your application. Although descriptive names for objects with spaces are easier to recognize than more compact names and preferable for most cases, they can be difficult to use in expressions, SQL statements, and Access Basic code. If you are creating a database that uses these advanced features, you may want to use short, consistent names that are easier to remember and type, for example, field names such as LName and HPhone.
When you take the preceding approach to creating the objects in your application, you'll find yourself working with a variety of things ¾ queries, macros, event procedures attached to the form or report, and procedures in other modules ¾ to get a form or report to do what you want.
The Central Role of Forms
In a Microsoft Access application, forms aren't just screens for entering and editing data, they make up most of your application's interface. To your users, forms are the application. And by building your application around forms, you can control the flow of your application through the events that occur on the form.
Forms have an additional behind-the-scenes benefit to you when you use macros to tie your objects together. In addition to using forms as your application's interface, you can use fields on hidden forms to store and pass values in macros from form to form or from operation to operation. For example, suppose you want to provide your users with the ability to enter a range of dates in a dialog box and then print a series of reports based on that range of dates. The dialog box is a form that you create. When the user chooses OK in the dialog box, you hide the form rather than close it. Now the dates that the user entered are available to the macro that prints each of the reports.
Connecting the Dots
Simply getting the individual objects in your application up and running isn't enough. You need to connect the dots i.e. tie the objects in your application together into a coherent system that's designed for the particular tasks your users are trying to accomplish. You connect the dots by running macros or event procedures in response to the events that occur on the forms or reports in your application.
The following illustrations show how you can tie separate objects together into a custom system for entering orders and printing invoices. In the illustrated application, the Orders form is the central form. When you start the application, the Orders form opens automatically. People who take orders can do all their tasks directly on this one form.
You can also use custom toolbar buttons to make the objects in your application work together as shown in the following illustration.
Automating Bulk Updates Without Code
In database applications, you often need to automate bulk updates of records ¾ to delete and archive inactive customer records, for example. If you're an experienced application developer, you may have found that operations like this can require writing many lines of code. But in a Microsoft Access application, you can usually manipulate sets of records better with action queries than with code. The action or bulk operation queries ¾ make-table, update, append, and delete ¾ are often the most efficient way to change data. Running a predefined delete query, for example, is a much more efficient way to remove a lot of records than looping through records in an Access Basic procedure. You can define the query graphically in Design view, and then run it from the Database window. You can also write a macro that uses only two actions SetWarnings and OpenQuery to run the query. Then you can attach the macro to a menu command, a command button, or specify it as the event property setting for any event that occurs on a form.
Referring to Objects and Their Values
The preceding section showed that you can tie objects together not only by using one object to open another, but also by passing data from one object to the next. For example, when you open the Print Invoice dialog box, which is a form, you want the right order to be selected in the dialog box.
How do you do it? Using a macro or event procedure, you identify the value from the open object you want to pass on, usually the value of a control on a form. Because you may have many forms open at one time in your application,
Microsoft Access requires a specific syntax structure to identify the control that contains the value you want.
To refer to an object or a value, you start with an object and identify each element in turn. Use the ! (exclamation point) operator before an element of a collection (usually an object you've named yourself). For example, to refer to a form that you've named Orders, use this expression:
Forms![Orders]
To refer to the Order ID control on the Orders form, for example, use this expression:
Forms![Orders]![Order ID]
You refer to the control to get or pass its value.
Methods are part of the Access Basic language.
To refer to a property, use the . (dot) operator before the property name. You use this operator before properties, methods, and collections (usually the objects that Microsoft Access names). For example, to refer to the Width property of the Orders form, use this expression:
Forms![Orders].Width
To refer to the Order ID control's Width property on the Orders form, use this expression:
Forms![Orders]![Order ID].Width
Tip The easiest way to refer to an object or property is to use the Expression Builder. With the Expression Builder, you can simply select the object you want from lists, and the Expression Builder writes the reference for you with all the operators in the right places. To display the Expression Builder, click the right mouse button where you want to enter the expression, and then choose Build from the shortcut menu.
Controlling How Your Application Starts and Providing Custom Commands
Once your application is working smoothly as a system, you can control how it starts, assign your own commands to keystrokes, and provide custom commands on your menu bars.
Use the AutoExec macro to control how your application starts If you name one of your macros AutoExec, Microsoft Access runs that macro every time you open the database. You can use the AutoExec macro to set up your application's environment ¾ for example, to hide the Database window and open the form you want users to see first.
Use the AutoKeys macro to assign commands to keystrokes You can write a macro that assigns your own commands to keystrokes, providing your users with shortcut keys. You name the macro AutoKeys. Microsoft Access runs this macro every time you open the database.
Use the Menu Builder to create custom menu bars for forms You can provide custom menu bars and menu commands for your users. To create a custom menu bar for a form, start the Menu Builder. In the property box of the form's MenuBar property, click the Build button beside the property (in the property sheet) to start the Menu Builder.
Separating Your Application's Tables from Its Other Objects
If you're distributing your application to a number of users or if your data is located on a server, you might find it convenient to use one database to hold the data (tables) and another database to hold the application's queries, forms, reports, macros, and modules. You can then base all objects on attached tables from the database that contains the tables.
Users open and use the application database (APP.MDB). Because the objects in the application database are based on attached tables, changes that users make to the data using these objects change the data in the data database (DATA.MDB). You can implement security on all the objects in this application, including both the tables in the data database and the objects in the application database.
When you separate the application's data from its forms and other objects, you can easily distribute upgraded versions of your application. You can distribute upgraded queries, forms, reports, macros, and modules without disturbing the application's data. And if your data is located on a server, you can reduce the network load by having users run the application from their workstations rather than from the server.
If you know from the beginning that you intend to split your application into two database files, you can develop the application with this in mind. Or, you can keep tables and objects together in the same file and split them only when you're finished and ready to distribute the application.
To split an application after creating its objects
1. Create and open a second database file.
You can make this new database either the application database or the data database.
2. To make the new database the application database, import all the objects except your tables, and then attach the tables from the original database to the new database.
- Or -
To make the new database the data database, import all the tables, create the relationships between the tables in the new database, delete the tables from the original database, and create attachments from the tables in the new database.
Once you split your application, you can distribute it to your users. They open and use the application database.
Note The attachments used by attached tables in the application database rely on the path of the data database. If users put the database on a different path, the attachments will fail. You can automate the process of attaching tables for your users by prompting them for the path to the data database when they start your application. Then you can use the Access Basic RefreshLink method to refresh your application's attachments.
