Designing Applications Access 2002 is mostly a very intuitive and easy to use application. From the early days of Access, usability has always been one of the primary development focuses behind Access. In fact it was this ease of use that was a major factor in the incredible speed with which Access came to be accepted as the definitive desktop database development tool. But Access has always appealed to a wider audience than simply end users and inexperienced developers.
Chapter 1 What Is an Access Application? An Access application is just the same as any other kind of application, but one built using Access tools. It is a collection of interrelated objects working together to achieve a specific objective, usually business-orientated.
Designing Applications The Development Process There are many skills involved in the development and delivery of successful Microsoft Access 2002 applications. The database designers need to be able to understand the principles of relational database design, so that they can design the tables that will hold the data and the relationships between those tables.
Chapter 1 Preliminary Analysis High-Level Design Detailed Design Detailed Design Detailed Design Coding Coding Coding Testing Testing Testing Documentation Documentation Documentation Review Acceptance Acceptance Acceptance Review Review Review The Analysis Phase Irrespective of the type of project lifecycle mode, the first stage, and one of the most important to get right, is inevitably one of analysis.
Designing Applications ❑ How many users will be using the system at the same time (concurrently)? How many in total? ❑ What is the anticipated mix of insert and update activity compared to query and reporting activity? The problem is that the only people who can answer these questions are the customers who will use the finished application, and sometimes it can prove difficult to get answers out of them.
Chapter 1 Ironically, the importance of sound requirements analysis is most clearly seen in its absence. When requirements are not properly defined or documented, one of two consequences almost inevitably follows. Either the requirements remain unmodified, with the result that the application fails to achieve its client's objectives; or the requirements are modified later in the development cycle.
Designing Applications ❑ What type of network will connect the computers? Will lack of available bandwidth prove a problem? ❑ What security policy will the application need to operate? ❑ What type of fault tolerance or recovery issues will need to be considered? The purpose of the technical analysis should be to produce an application architecture and implementation framework within which the resultant application will nestle.
Chapter 1 The principles behind sound data analysis are straightforward enough: ❑ Identify all of the data entities you will be dealing with ❑ Establish the attributes of these entities ❑ Define the relationships between these entities ❑ Document, document, document… As with the requirements analysis and the technical analysis, a variety of methods and tools can be employed to assist in the task of data analysis.
Designing Applications ❑ Performance Considerations ❑ Calculation Methods But design is not just about establishing an immutable set of blueprints. Successful applications are normally those where the application designers have designed for change. Designing for Change The concept of "Designing for Change" was first discussed by David Parnas in the early 1970s.
Chapter 1 Employ a Change Plan As well as information hiding, there are other techniques that can assist in reducing the impact of change, and these should be prescribed in a change plan. For example, the change plan might specify that: ❑ Named constants should be used wherever possible in place of hard-coded values. ❑ If the application is to be multi-lingual, then care must be taken to identify and separate out all the text to be used so that it can be easily localized (translated).
Designing Applications The purpose of testing is to break code, to determine ways of making an application misbehave, to expose flaws in either the design or execution of the development process. For this reason, many developers dislike the testing phase (in the same way that many authors dislike the editing phase).
Chapter 1 Yes, I know it is important. I know that I am as likely to benefit from it as anyone else when I revisit my code later. I know that the users have paid for it! I know it makes the difference between a good application and a great application. That's why I do it and why I make sure that everyone working with me does it and does it well.
Designing Applications The purpose of the post-implementation review is not just to give everyone a chance to whine and moan about what went wrong. Instead, the purpose is to identify the changes that need to be made to your project methodology and practices to make sure that the same problems don't happen again next time. At the same time, it is an opportunity to identify the successes and to make sure that the benefits of these can be reaped by future projects.
Chapter 1 Designing the Ice Cream Shop Database As its name suggests, the Ice Cream Shop database is an application that has been designed to track stock and sales for an ice cream wholesaler called Dave and Rob's Ice Cream Shop.
Designing Applications A more detailed analysis has revealed the attributes of the five entities that we need to record and our preliminary entity relationship diagram (ERD, or more simply, database design) looks like this: tblCustomer CustomerID CompanyName Address City State ZipCode Country Phone Fax Email Web ContactName tblSales SalesID fkCustomerID fkIceCreamID Quantity DateOrdered DateDispatched DatePaid AmountPaid ContactName tblIceCream IceCreamID IceCream Description Price Picture tblSupplier 1
Chapter 1 One-to-many relationships between the entities have been denoted by creating straightforward one-to-many relationships between the tables: tblCustomer (1) (n) tblSales A sale can only involve one customer, but a customer can have more than one sale tblIceCream (1) (n) tblSales Only one type of ice cream can be sold in a particular sale, but an ice cream can be sold more than once Many-to-many relationships have been handled by creating two intermediate tables (tblIceCreamIngredient
Designing Applications Secondly, note the fact that the tblSupplier and tblCustomer tables have identical structures. Whenever we see this in a database structure it should alert us to the fact that what we have represented as two discrete entities might instead be represented as a single entity. So what is it that differentiates a supplier from a customer? Obviously, we buy from suppliers and customers buy from us.
Chapter 1 ❑ It will allow you to achieve a measurable improvement in performance, with a minimal increase in administrative overhead, or ❑ It will allow you to achieve a measurable reduction in administrative overhead, with a minimal degradation of performance Another aspect of the tblCompany table that required careful consideration was the question of how to hold address information. Look at the structure of the table.
Designing Applications Choosing a Storage Engine Another choice which developers of Access 2002 applications will now need to make is which database engine they will use to store the application's data in. Traditionally, Microsoft Access has always used JET as its native database engine. Additionally, however, Access developers are offered the choice of using a second desktop database engine, the Microsoft SQL Server 2000 Desktop Engine (henceforth MSDE).
Chapter 1 OK. Let's get started! 30 1. Load up the database file IceCream.mdb. In the Database window, select the Tables tab and then the tblCompany table: 2.
Designing Applications 3. Access will now generate a form with all the fields from the tblCompany table and display the first record: This is OK, but it's not perfect. There are several things that we can improve: ❑ The form caption is tblCompany, which isn't very instructive to the user. ❑ We will probably want to hide the CompanyID field, as it's of little relevance to the user. ❑ Some of the fields are the wrong shape.
Chapter 1 32 2. Now switch to Design view for the newly saved form by selecting Design View from the View menu or by clicking the Design View button: 3. We can now attempt to make the changes that we highlighted earlier. To change the form's caption, you bring up the form's property sheet by double-clicking the Form Selector (the small gray box in the upper left corner of the form where the rulers meet), or by clicking the Properties button on the toolbar: 4.
Designing Applications 5. Next we must delete the CompanyID textbox and its label. To do this, we must select the textbox on the form by clicking it once, and then hitting the Delete key. The CompanyID textbox and its label will be deleted: 6. Next we'll change the size of the Address textbox. To do this, first select all of the controls on the form below the Address textbox.
Chapter 1 There is an alternative method for moving controls around on a form once they are selected: Use the arrow keys on your keyboard whilst holding down the Ctrl key. This method is slower, but can be more precise. 8. Once you have created some space, resize the Address textbox. To do this, we select the Address textbox by clicking it and then click the resizing handle (it looks like a black square) at the bottom center of the text box.
Designing Applications We've now made the first three changes we decided on, and our form certainly looks a little more professional. But what about the other change? We still need to put more manageable navigation buttons on the screen. This is where things get a little more advanced! Creating Navigation Buttons To make the form easier to use, we can place some command buttons on the screen to replace the present navigation buttons. We can then use macros to move through the records behind the form.
Chapter 1 3. 36 Once you have done this, you can also set the following form properties: Scroll Bars Neither Record Selectors Dividing Lines No No 4. Now you can add the first of your own navigation buttons. We'll start by creating a Next Record button. 5. Check that the Toolbox is visible. If it isn't, then click the Toolbox button on the toolbar: 6.
Designing Applications 7. Draw the button a suitable size on the footer: 8. Now go to the property sheet and change the Name property of the button (found under the Other tab) to cmdNext and its Caption property (found under the Format tab) to Next. Note that when you name controls on a form it is a good idea to use a prefix that matches their type (like cmd for a command button).
Chapter 1 10. This will, in turn, bring up the Choose Builder dialog. For the moment, we want to use a macro, so select Macro Builder and hit the OK button: 11. We've said that we want a macro behind the button, so Access now helps us to build it. It displays the macro design window and prompts us for the name that we want to give the macro. We will call it macNextButton, so you should type in macNextButton and then press OK. 12.
Designing Applications 14. Now close the macro window and choose Yes when prompted to save the macro you have just created. Then change the frmCompany form to Form view and save the changes you made to it. When you open the form in form view, there should be a navigation button on it that allows you to move forward through the records in the form. If you look at the button's properties, you will see that the name of the macro is listed in the On Click property on the Event tab.
Chapter 1 The form looks better, but it's still not perfect. If you haven't already done so, try clicking the First button to move to the first record. Now try clicking the Previous button to move to the previous record.
Designing Applications In this case of course, we know exactly what caused the problem: the user, right? Wrong. Ask the user: sloppy programming caused the problem! Rule #1 of programming – the user is always right because they pay the bills. While we cannot guard against absolutely every single incorrect action the user may take, we really ought to try to do something against a common possibility like this.
Chapter 1 Using VBA makes your database easier to maintain. Macros are completely separate from the objects that call them. Although we created the navigation button macro from within the form, the macro is actually stored as a separate object in the database window. Click the Macros tab and you'll see it's there. In contrast, you can save VBA code with the form itself. This means that if you want to move the form into another database, the code automatically goes with it.
Designing Applications But, apart from these, you'll find that with VBA you can do all that you could with macros and lots more besides. In early versions of Access, you also had to use macros if you wanted to create custom menu bars or attach custom functionality to buttons on toolbars. However, from Access 97 onwards, both of these tasks are now achieved from the Customize… dialog box available from Toolbars on the View menu.
Chapter 1 The lower pane of the macro window lists the arguments that you can pass to the PrintOut action to define exactly how it should operate. For example, we've specified Selection as the Print Range argument. This causes Access to only print out those records that were selected when the key combination Ctrl+P was pressed.
Designing Applications If you want to perform an action whenever the database is opened, but want to get the benefits of using VBA rather than macros, then you should write a procedure in VBA and call the procedure from the Autoexec macro. You can do this by using the RunCode action in your Autoexec macro: In this situation, when the database opens, the Autoexec macro is executed and this causes the MyCode() procedure – written in VBA – to be executed.
Chapter 1 Try It Out Converting a Macro to VBA Your first sight of the Visual Basic Editor can be a bit daunting. Don't panic! We'll lead you through all the details as we continue through the book. 46 1. Load up the database file IceCream.mdb. In the Database window, select the Macros tab and then the macNextButton macro that we created earlier. 2. Select File and then Save As… from the main menu. 3. When the Save As dialog appears select As Module and click OK: 4. Another dialog appears.
Designing Applications 7. The Project Explorer will open the module for us, and we can view the code it created: 8. You might be able to recognize that the line that does all the work is the one that starts DoCmd.GoToRecord. Most of the rest is (necessary) padding and error handling code.
Chapter 1 Summary In this chapter, we've worked our way through the process of creating part of an Access application with one aim in mind – to deliberately hit a brick wall. That brick wall is the implementation of the intelligent navigation buttons. We just can't implement them properly using macros. Instead, we need VBA… So, in brief, we have covered: ❑ How to go about designing an application ❑ An introduction to the application that forms the basis of this book – IceCream.
Designing Applications 49
Chapter 1 50
