Access Hacks | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Access is used in many different situations, in many different ways, by a diverse group of people. Some are novices, while others have been using a single custom Access solution for years. Still others are sophisticated users who want to take advantage of the application's bells and whistles, or they are Access developers who make those bells and whistles ring and sing.

One thing all users and developers want is for Access to support their needs as efficiently as possible. This can require a little customization or a down-right minisolution that's implemented inside Access and helps to support the purpose of the overall solution.

This chapter includes a collection of hacks you can implement directly within Access. You'll find hacks designed to help general users by making their experience more rewarding and more efficient. You'll also find hacks a power user can implement to expand the value of his custom Access application. You'll even find hacks that only a developer can implement. A little VBA goes a long way here.

Place shortcuts to pertinent objects in custom groups so that users don't have to wade through all the database objects.

The Access database window can be overwhelming to some users. Tables, queries, forms, reports; determining where to find objects you need within these object collections isn't exactly a user-friendly process. Besides, sometimes a user needs just a handful of objects to complete his work. And yet he might be confronted with considerably more objects than he needs.

Luckily, the Access database window allows you to create custom groupings in which you can place shortcuts to only the desired objects. Just as the Windows desktop has shortcuts to folders, files, and applications, Access lets you make shortcuts to your database objects. And it's a cakewalk to do so!

Your Access application might open to a navigation, or main,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Access is used in many different situations, in many different ways, by a diverse group of people. Some are novices, while others have been using a single custom Access solution for years. Still others are sophisticated users who want to take advantage of the application's bells and whistles, or they are Access developers who make those bells and whistles ring and sing.

One thing all users and developers want is for Access to support their needs as efficiently as possible. This can require a little customization or a down-right minisolution that's implemented inside Access and helps to support the purpose of the overall solution.

This chapter includes a collection of hacks you can implement directly within Access. You'll find hacks designed to help general users by making their experience more rewarding and more efficient. You'll also find hacks a power user can implement to expand the value of his custom Access application. You'll even find hacks that only a developer can implement. A little VBA goes a long way here.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Place shortcuts to pertinent objects in custom groups so that users don't have to wade through all the database objects.

The Access database window can be overwhelming to some users. Tables, queries, forms, reports; determining where to find objects you need within these object collections isn't exactly a user-friendly process. Besides, sometimes a user needs just a handful of objects to complete his work. And yet he might be confronted with considerably more objects than he needs.

Luckily, the Access database window allows you to create custom groupings in which you can place shortcuts to only the desired objects. Just as the Windows desktop has shortcuts to folders, files, and applications, Access lets you make shortcuts to your database objects. And it's a cakewalk to do so!

Your Access application might open to a navigation, or main, form. From there, users click their way through the application. But not all applications are made in this way. Figure 1-1 shows the plain database window in all its unimpressive glory. Some applications open to this functional but inefficient window.

Figure 1-1: The standard Access database window

Of course, you can get to all the objects you need from here: click the Queries tab to find the queries you want to run, or click the Reports tab to find the reports you want to run. But you can avoid this drudgery. One great thing about the database window is the ability to make your own groups. In fact, it is clear that the database window does separate objects from groups. In Figure 1-1, on the left side of the database window, you can see a clear distinction of groups in the bottom half of the window.

By default, there is one Favorites group, in which you can place shortcuts to objects. It's easy to do; just find the object wherever it exists within the various tabs, and then click and drag it to the Favorites group. Figure 1-2 shows the result of doing just that. The Favorites group has been filled with shortcuts to some of the database objects. Note that these are

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Build personalization functionality so that users can set up the application in ways that work best for them.

There is no reason to limit all users to using an Access application in the same way. It's easy to overlook this capability because Access allows you to designate only one opening form in its startup options—that is, unless you tap into its database-opening events. Then, you can choose which form will open, what its properties are, and more. You can effectively make all facets of the application unique to a particular individual or profile. Here are a few items you can tailor this way:

Forms: Specify the opening form, how it's displayed, and what functionality it includes
Data sources: Specify which personalization tables, internal or external, are needed for each user's tasks
Reports: Show or hide details

This hack shows you how to use the


               AutoExec

macro to run an opening function that delivers a personalized interface to the user. For this to work, you must first create a database table to store user preferences, and then, when the database starts up, you must be able to identify the user to the database. You can do this in a number of ways: for instance, a pop-up input box can ask for a name or initials (possibly with a password), a command-line switch can provide the user identity, or, if the Access security model is in use, the user ID can be made available through the CurrentUser property.

User preferences are stored in a table that has a field for each personalization facet. You determine which features to personalize. For example, a Long datatype field can store the preferred background color, a text field can store the name of the preferred opening form, and so on. Figure 1-4 shows such a table, aptly named Customized, with a few preferences filled in. The field name indicates the preference, and the actual value in the field is the setting.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Save time and avoid mistakes by using simple keystrokes for entering the date, time, or other commonly used entries.

The mouse is nice, but nothing beats getting around an application faster than keyboard shortcuts. Ctrl-C for copy, Ctrl-V for paste, and so on, are pretty familiar. How about keyboard shortcuts for entering the date, time, and other data? Using these shortcuts will save valuable time when you are in a rush to finish a project. And how often are you not in a rush?

Table 1-1 summarizes useful keyboard shortcuts to use within your Access applications. This isn't an exhaustive list of keyboard shortcuts—not by a long shot! You can use the Access Help system to find all the shortcuts. The ones presented in Table 1-1 are specific shortcuts for entering data.

Table 1-1: Keyboard shortcuts for entering data
Action	Keyboard shortcut
Enter the current time.	Ctrl-:
Enter the current date.	Ctrl-;
Insert data from the same field in the previous record.	Ctrl-'
Insert the default value for the field.	Ctrl-Alt-spacebar
Insert a new line in a text or memo field.	Ctrl-Enter
Enter a new record.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Avoid having to propagate data changes manually throughout related tables by establishing cascading updates and deletes.

The one constant you can count on is change. Why not plan for this eventuality in your applications? Take a real example: a customer changes her name. If you are in business long enough, this is something you will need to accommodate in your database.

Data changes need to be propagated in two ways. If the data isn't used as a table key or a foreign key, you need to change the data in all the places it resides in your tables. Hopefully, your data resides in only one place! A correctly modeled database holds a piece of data, such as a customer name, in just one place. If you do have such a piece of data in a few places, however, you presumably have a reason for doing this. Applications grow over time and often are handled by a succession of developers. It happens.

If you have database applications in which the same data is found all over the place, a brush up on data modeling is in order.

What about data that exists in table keys? This can be a frustrating change to propagate if many child tables use the data as the foreign key. That is, it will be frustrating unless you plan your relationships with cascading updates.

When creating relationships between tables, one option is to establish cascading updates. Figure 1-9 shows two tables of data. The tblCustomers table on top has customer information. The values in the key field, CustomerID, are the initials of the actual company names. In the lower tblInvoices table, the CustomerID serves as the foreign key.

Figure 1-9: Related tables

Figure 1-10 confirms the relationship between the tables. A line leads from the CustomerID field in tblCustomers to the CustomerID field in tblInvoices. The number 1 on the tblCustomers table side of the line indicates that tblCustomers is the parent table. The infinity symbol (∞) above where the line meets the tblInvoices table indicates that tblInvoices is the child table. This is a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Say goodbye to version incompatibility issues.

Microsoft has released more than half a dozen versions of Access over the years. Some people and organizations buy into each upgrade, some skip around, and some hold on for dear life to the one they have been using since the previous century! The version doesn't matter when you or your organization work in a vacuum, but when you exchange data with external companies, version incompatibility can rear its ugly head.

Let's say you have Access 2003 and you send a database filled with your orders to a vendor. The vendor has Access 95. Uh-oh! The vendor can't open your database.

One of the recent data technologies initiated throughout the computing world is the use of XML and other platform-neutral protocols. This purportedly removes data incompatibility. XML is nice, but only the most recent versions of Access can read XML.

The way to share data is via a tried-and-true, low-key, low-tech method: export and save your data as text. Although they vary in terms of how text can be saved—delimited, type of delimiter character, text qualifier, fixed-width, and so on—all versions of Access can read and write text files. Figure 1-12 shows the Export Text Wizard, in which you set your text export options. When you initiate to export an Access table or query and select text as the type, the wizard starts up.

Figure 1-12: Using the Export Text Wizard

Admittedly, exporting and importing text isn't an ideal approach, especially when you have to export or import many tables of data. But it sure beats losing business because your client can't open your database.

XML has paved the way for easy data exchange among versions and systems. XML support is decent enough in Access 2003, less so in Access 2002 and Access 2000. If working with text files just doesn't seem right for your needs, you can always use XML. A separate external XML parser does the trick.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Optimize and reduce the number of macros using the optional name and condition columns.

Macros are often used for small automations—usually for tasks that aren't too complex or sophisticated because VBA is available to handle the heavy processing. Let's think that way no more. Actually, macros can handle a decent amount of intelligent processing and, in fact, have a condition-testing ability similar to the If…Then structure in VBA. This hack shows you how to transform a single macro into a multipurpose workhorse.

Macros have but a single mandatory column: the Action column. A macro can have one or more actions. However, macros also have an optional Condition column, in which a little entry can go a long way toward adding some punch to the process. When you're designing a macro, use the View menu to display the Condition column.

A condition can test a field value, evaluate the result returned by a function, and even use the returned value from a message box. Conditions also can use Boolean logic, incorporating and/or-type logic in the condition testing.

Figure 1-13 shows a macro in which a series of actions occur when the macro is run. A few of the actions run only when their condition is met. For instance, the End of Month function and the End of Month report are included in the processing only when it is the first day of the month (presumably tallying up figures about the month that just ended). Using the Day and Now functions takes care of testing for the first day of the month.

The Employee Bonus report runs only when a condition tested with a DLookup function is true.

The unconditional actions in the macro always run. Even when the actions with unmet conditions are passed over, the macro continues to run and doesn't stop prematurely.

Macros can also be organized into groups, known as macro groups. By creating macro groups, you can reduce the number of overall macros and keep similar macro actions together in one place. The key difference between a macro and a macro group is the use of the optional Macro Name column.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Implement an object-use log to clean up an overloaded database by analyzing user actions and then deleting never-used objects.

Some Access database applications just get plain ugly. If you have ever browsed through a database with dozens and dozens of forms and reports, you know what I am referring to. This is often the result of a user community turned loose: forms for every point and purpose; a report for each day of the week; and then some.

Adding insult to injury, you can't easily tell which objects the users are actually using. Luckily, there is a way to reign in the application and reduce the clutter.

The goal is to find out which objects are no longer being used. Often, users create forms or reports that they use once and never look at again. Once you've identified which objects are no longer being used, you can delete them from the database. This will likely improve the performance of the database and certainly reduce its memory footprint after you compact it. The trick to deleting unused objects is to create a list of objects that are being used and then to delete the objects that didn't make it on the list.

All forms and reports contain an open event. By putting a simple code routine into all open events, you can populate a log with the names of the objects being opened. Before you do this, you need to create a log table to store the object names. This doesn't need to be fancy; indeed, the log table can have just a single field to store the names. Optional fields can store a timestamp, the type of object, and so forth.

Figure 1-15 shows the design of such a table. It comprises two fields: one captures the object name, and the other captures the object type. The table receives a record each time an object is opened.

To append a record to the log table, an object must have a little bit of code in its open event. Here is a snippet that would go into the open event of a form named

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Protect your data using the read-only command-line switch so that users can't edit the data.

Creating a desktop shortcut to a database provides a behind-the-scenes benefit. Specifically, you can use command-line switches that are unseen by all but the technically curious.

In this manner, it is easy to set up a shortcut to open a database in read-only mode and, thus protect your data and design elements. To do this, add the /ro switch at the end of the target string in the desktop shortcut. Note that the full target string isn't just the path to the database; it needs to start with the path to the Access executable, followed by the database path, followed by the switch.

Using Access 2003, which by default is in the standard Program Files/Microsoft Office/Office 11directory, the full target string looks like this:

	"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
	"C:\Sales Summaries\Sales2005.mdb" /ro

When the desktop shortcut is clicked, the database opens in read-only mode. A confirmation message is presented at startup, shown in Figure 1-17. Data can't be added, deleted, or edited.

This is a great way to disseminate information without concern for data integrity issues. Distributing the database application in such a way that a desktop shortcut is created or updated guarantees that the database opens in just the way you intended.

Figure 1-17: A reminder about the read-only status

Of course, a half-savvy user can just start up Access and open the database via the Open dialog, thus bypassing the desktop shortcut. The database is then opened in full read/write mode, unless a gotcha is in place to prevent this.

To handle this, you can place a simple SQL


                  Insert

operation in the database's opening routine, and you can include an extra table in the database for just this purpose. If the operation succeeds, the user is warned to use the desktop shortcut (as shown in Figure 1-18), and the database closes.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Plan a multiple-database architecture to house any amount of data—gigabytes, even terabytes!

The only size limit in Access is that a table can't contain more than 1GB of data. Well, if that's it, there is a lot of opportunity here. Access isn't cut out for incredibly large stores of data, granted, but that's not the point. If SQL Server or Oracle isn't going to be installed at your place of business for another year, take advantage of Access's flexible architecture to work with any amount of data.

The technique is simply to make a plan for how to structure the data among multiple database files and tables. There is no rule that says an Access application must reside completely in a single Access file. An Access application can be split into a front end and a back end. That is, the forms, reports, and queries stay in the front end, and the data itself is put into a separate file. The data tables are then linked to the front end. This is standard fare, the quintessential client/server in its simplest execution, shown here in Figure 1-19.

Figure 1-19: A simple front-end/back-end configuration

There is no reason to be limited to a single file on the back end. The organization of and facts about the data will drive the decisions concerning how it can be parsed into smaller data stores. For example, if you are working with a large customer base, you can split the data from one data table into 26 tables—one for each letter of the alphabet. Figure 1-20 shows such a configuration.

Figure 1-20: Using multiple databases on the back end

An alternative is to split a customer list by city, state, province, or other geographic delimiter. Again, this allows you to take an overwhelmingly large set of data and turn it into manageably smaller (albeit still large) stores of data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the description property to prevent users from being overwhelmed by sifting through cryptic-sounding forms, queries, and reports.

Many of us follow naming conventions when creating database objects. Among the developer community, we have come to recognize and take for granted that tbl, frm, rpt, and other prefixes are part and parcel of our work. For example, tblStaff is a table, frmAdmin is a form, and rptContacts is a report.

However, when you complete a database with several objects that are named in this way, it's a challenge to the average database user to understand the names. Figure 1-21 shows a perfect example of a database with several forms.

There is a way to resolve this dilemma, and it doesn't mean developers have to change their naming habits. All database objects can be given a description. The best thing is that you can enter descriptions for objects directly in the database window without having to open an object in Design mode.

In the database window, just right-click an object, and from the menu that appears, click Properties. A small dialog box opens for you to enter a natural-sounding description, as shown in Figure 1-22.

After you enter descriptions for all the objects, just be sure to list the database objects in List view instead of Icons view. This makes the descriptions visible. Figure 1-23 shows how the group of forms in Figure 1-21 is now understandable.

Figure 1-21: Cryptic form names that can stump a user

Figure 1-22: Entering a description

A neat thing about this approach is that you can even use a warning message so that users know not to open an object. This is particularly helpful in the case of subforms. Users shouldn't open subforms directly because they appear inside other forms. The description tells users not to open them.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Correctly model a many-to-many relationship.

It's easy to fall into the trap of assuming all relationships are of the one-to-many type. It's true that many data relationships do follow the one-to-many paradigm. For example, one person has zero or more telephone numbers. However, not all data is meant to be modeled in this way.

A perfect example of data that appears to fit the one-to-many model, but doesn't, is the relationship between instructors and students. On the one hand, one instructor does have many students, thereby proving a one-to-many relationship exists. On the other hand, one student has many instructors—which is also a one-to-many relationship. So, what is the problem?

Figure 1-24 shows one way to model instructors and students. The instructor table occupies the one spot and the student table occupies the many spot. Instructors and students get together for appointments. This model works but emphasizes that instructors are of a different level than students, which might not be true.

Figure 1-24: An inefficient one-to-many relationship

In Figure 1-24, the student table is also required to have the instructor ID as the foreign key. This is acceptable, but now look at the appointments table; it considers appointments as belonging to students, but appointments belong to both instructors and students.

Figure 1-25 shows how to resolve the dilemma in the data model. Because appointments belong to both instructors and students, that is how the model should look. The appointments table serves as a junction table between instructors and students.

A junction table becomes the many table for two or more other tables. All the key fields of the one tables become foreign keys in the junction table. Any other pertinent fields are included in the junction table. In this example, the junction table has fields for the date and time the instructor and student will meet. Also, the student table no longer has the instructor ID as a foreign key. In this example, instructors and students have no hierarchy; therefore, it makes sense that one doesn't serve as a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use the Compact on Close option to keep a database from getting too big.

Access databases are notorious for their ability to grow in size. This is especially true as data is moved in and out. For example, when a database application regularly imports data, processes the data, and then exports it back out, the database can become huge, on the order of several megabytes in size. This can be the case even when the data moving in and out is of a reasonable size.

To shrink the database back to the size it should be, you need to compact the database. However, expecting users to compact their databases isn't a great idea, especially if your users aren't technically savvy. Luckily, Access includes an option to compact a database when it is closed. This option was not available in older versions of Access, but it is available in Access 2002 and Access 2003.

Figure 1-26 shows the Options dialog box (Tools → Options) with the General tab on top. Note the Compact on Close checkbox.

Access is unlike other products, such as SQL Server, in that it doesn't allow you to control the size of the database. Setting the database to compact each time it closes removes what has traditionally been a recurring problem with Access.

Figure 1-26: Selecting to compact on close

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Were it not for tables, we would have no place to store data! Tables are straightforward; they comprise rows and columns, or records and fields. So, what is there to hack?

Table design is one area in which a little customization goes a long way. Changing the default datatype and other properties speeds up development time. Without this intervention, text fields default to 50 characters. Is this a good size? That depends on your project.

Have you ever wished you could implement triggers the way SQL Server does? You can! Use the inherent events available to forms to get the same results.

Need to hide data? There's a hack for that, too!

The AutoNumber field doesn't need to begin with a value of 1. You can override Access's default autonumbering scheme to better suit your requirements.

A great feature that Access brings to the table-creation process is the AutoNumber field. This field type places a value of 1 in the first record and automatically increases the value by 1 as records are added. It doesn't contain any significant or meaningful data. Its basic purpose is to become the key field and thereby provide uniqueness to the data records.

Just plop a field into the table design, and designate it as an AutoNumber field. Typically such a field has a name with ID or Num in it, such as CustomerID or RecordNum. Note that a table can have only one AutoNumber field.

All in all, AutoNumber is a great feature, but there is one gotcha: the value always starts at 1. Often, this isn't an issue because the field value really is unimportant. The fact that the values are unique is what matters more. But what if you need to use a self-incrementing number that starts at a different value? Can you do this? Of course!

The AutoNumber field type doesn't have a property to specify the starting value. Figure 2-1 shows a table design. As you can see, the first field is an AutoNumber field, and its addressable properties fill the lower-left area of the table design window. Note that you have nowhere to input a default start value.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Were it not for tables, we would have no place to store data! Tables are straightforward; they comprise rows and columns, or records and fields. So, what is there to hack?

Table design is one area in which a little customization goes a long way. Changing the default datatype and other properties speeds up development time. Without this intervention, text fields default to 50 characters. Is this a good size? That depends on your project.

Have you ever wished you could implement triggers the way SQL Server does? You can! Use the inherent events available to forms to get the same results.

Need to hide data? There's a hack for that, too!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The AutoNumber field doesn't need to begin with a value of 1. You can override Access's default autonumbering scheme to better suit your requirements.

A great feature that Access brings to the table-creation process is the AutoNumber field. This field type places a value of 1 in the first record and automatically increases the value by 1 as records are added. It doesn't contain any significant or meaningful data. Its basic purpose is to become the key field and thereby provide uniqueness to the data records.

Just plop a field into the table design, and designate it as an AutoNumber field. Typically such a field has a name with ID or Num in it, such as CustomerID or RecordNum. Note that a table can have only one AutoNumber field.

All in all, AutoNumber is a great feature, but there is one gotcha: the value always starts at 1. Often, this isn't an issue because the field value really is unimportant. The fact that the values are unique is what matters more. But what if you need to use a self-incrementing number that starts at a different value? Can you do this? Of course!

The AutoNumber field type doesn't have a property to specify the starting value. Figure 2-1 shows a table design. As you can see, the first field is an AutoNumber field, and its addressable properties fill the lower-left area of the table design window. Note that you have nowhere to input a default start value.

Figure 2-1: AutoNumber, an incrementing field type

To be clear, the table does contain a New Values property, but all it tells you is whether new values are incremented or are random. It tells you nothing about starting the increment at a value of your choice. So, the first record will have a value of 1 in the field, the second record will have a value of 2 in the field, and so on.

To override the default starting value of 1, you can use an

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use Paste Append to easily copy data across tables.

Access users often use an Append query to append records from one table to another. In a production environment in which data is always being shuffled around, using Append queries can become tedious. Each time you design one, you have to match the fields of the destination table with the fields of the source table. This is easy when the fields have the same name, but it takes manual intervention when the field names differ.

If you have designed and saved an Append query definition, and the source and destination tables never change in name or structure, all is well for you. However, if even a single extra character is misplaced or is missing in the field names, the query either bombs or asks you to fill in the value for the unidentifiable field. Neither is an option you can live with.

How can you deal with these accidents waiting to happen? Fortunately, you can copy data between tables in another way: use Paste Append.

A paste method unique to Access, Paste Append appends the contents of the clipboard to a database table. The data has to match the table in structure, but it does not need to have matching field names. That right there improves on the tedious data entry involved when using the query grid. To be fair to Append queries, they do have an advantage of their own: an Append query can use criteria to append filtered sets of records. Paste Append, on the other hand, just appends everything. However, if the need to apply criteria isn't an issue, Paste Append has the advantage.

Figure 2-5 shows two tables: one contains existing customers, and the other contains a list of leads that have to be added to the list of existing customers. The records in the tblLeads table need to be added to the tblCustomers table. The field names aren't the same, although the field types and purposes match.

The simplest thing to do is to select all the records in tblLeads (Ctrl-A). Copy the records, go to the tblCustomers table, and use the Edit

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Avoid incorrect results by leaving system tables out of your table count and definition routines.

How many tables are in your database? You might think finding this out is as easy as counting how many tables are listed on the Tables tab of your database window. To that I respond, "Try again!"

Access uses a number of system tables to control its own internal workings. Usually, these additional tables are hidden, but they are there nonetheless. Figure 2-8 shows a database with some tables.

Figure 2-8: Tallying the tables

It looks like this database contains eight tables, doesn't it? Let's try getting a count in a different way. In the VB Editor, activate the Immediate window (Ctrl-G). Then, enter the following code snippet and press the Enter key:

	?Application.CurrentData.AllTables.Count

Figure 2-9 shows the code and its results in the Immediate window. For the database in Figure 2-8, the result is 15, so Access is telling us the database actually contains 15 tables, although only eight are visible on the Tables tab.

Figure 2-9: Counting all the tables

The code snippet tells the truth, however: this database does indeed contain 15 tables. The ones you couldn't see before are the system tables. Let's display them!

Back in the database proper (not the VB Editor), use the Tools → Options menu to display the Options dialog box. Select the View tab. As shown in Figure 2-10, one of the options in the Show area is to display system objects. Select this checkbox to make the system tables visible.

Figure 2-10: Selecting to show system objects

Now, looking at the Tables tab in Figure 2-11, you can see the system tables.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Name tables with the USys prefix to prevent them from being visible.

Here's a quick and easy hack to hide data from prying eyes. Of course, any Access guru with enough notches in his belt will figure this one out. But ordinary users? Not likely. You can hide your tables using this approach and still retain full functionality in your application. Queries, forms, reports, macros, and code will still work, but anyone viewing the Tables tab won't find the tables you designate as hidden.

To do so, prefix your table names with USys. This acts as a flag to Access to treat the tables as a quasi-mix of system and user tables, and the ability is built in to hide or display them. Figure 2-13 demonstrates this procedure: a form is open and is clearly displaying data, but the Tables tab in the database window has no tables!

Figure 2-13: A form based on a hidden table

The form in Figure 2-13 has the record source property set to the USysClients table. In the Tools → Options → View menu, you'll find a setting for displaying system objects, as shown in Figure 2-14. Note that checking to display system objects makes USys tables visible.

Figure 2-15 shows all the system objects in their glory. The USys tables are there, as well as the MSys tables [Hack #15] .

The prefix isn't case-sensitive. You can use USYS, USys, usys, and so on; they all work to differentiate a table.

Another way to hide objects in your database is to right-click a database object, which then displays a menu that includes a Properties option. Selecting this displays a Properties dialog, as shown in Figure 2-16. Checking the Hidden checkbox hides the object.

Figure 2-14: Selecting to display USys-prefixed tables

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Incorporate the same functionality as SQL Server or Oracle in your Access application.

Access 2003 and earlier versions don't support table events. A trigger is a table event that you can fire on an insert, an edit, or a delete action—a valuable function. A useful example is to catch an edit before it completes and to store the original data—that is, store the original record somewhere else, such as in a backup table. This leaves you with a data audit trail. If for some reason the edited data is problematic, you can recall the original data.

This logic applies to deletes as well. Using triggers, you can hook into a delete and archive the data instead of just discarding it. In the case of inserts (such as new records being added to a table), data can be validated before being allowed into the table.

Unfortunately, Access doesn't let you do any of this directly from the point of view of the table itself. But you can do all of this when working through forms. Forms have plenty of events to hook into, and you can handle similar functionality as traditional triggers by working through forms instead of tables.

To demonstrate how all this works, let's add a new table to a database to mirror an existing data table and create an audit log of changes to the data table. We'll do this by using two additional fields: one to store the type of operation and one to store a timestamp. Figure 2-17 displays two tables: the data table (tblClients) and a table to store records from the first table just prior to them being edited or deleted (tblClientsAuditLog).

Here are a couple of points to consider:

The log table contains two additional fields: Action and Timestamp.
The ClientID field is the primary key in the data table, but it is purposely not set as a primary key in the log table. This is because the log table might hold multiple records that pertain to the same client (and therefore the same ClientID).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Optimize table design by changing the design defaults to match your needs.

A Text field is 50 characters. A Number field is a long integer, and the default value is 0. Sound all too familiar? How often have you gone out of your way to alter these defaults? Well, with this hack, you no longer need to.

In the Options dialog box (Tools → Options), on the Tables/Queries tab, you'll find settings for selecting the default size for text fields, the default number type (integer, long, single, etc.) for number fields, and even the overall default type. Figure 2-22 shows this dialog box and the settings.

Figure 2-22: Changing field defaults

In Figure 2-22, the default Text field size has been changed to 100. This means that as new text fields are added to the design of a table, they will default to a size of 100. Also, the default Number type has been set to Single. As new number fields are added, they default to the Single datatype. The overall default field type is set to Number; therefore, as new fields are entered into a table design, they default to a Number field type—and that type will be of the Single number type.

Altering these design defaults can be quite useful. If, for example, you are designing a table that predominantly contains dates, set the default field type to Date/Time, and save yourself a lot of field-type selection. As you enter new fields, they will default to Date/Time. You will need to adjust only the minority of fields that aren't of this type.

The settings in the Options dialog box control field type settings but offer nothing to indicate default values. In other words, you can select Single as the default number type, but you can't specify that the field defaults to a value of 1.25 (for example) as new records are added to the table.

However, a setting is available in which you can indicate a default value. The field in the third row of the table being designed in Figure 2-23 has been manually set to 1.25, and this becomes the default value for the field.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

An application's success often rests on user acceptance. With this in mind, it makes sense to plan how to make your application's front-end experience as visually pleasing and user-friendly as possible.

Sometimes user experience is overlooked. Developers can spend oodles of time designing fields and tables, setting up relationships, writing tricky SQL, and so on. Does this mean anything to the typical user? Not one whit!

Let's face it. Access is more than just a database. It is a database with built-in front-end tools. Tables are the core of a database, but the ability to create forms and reports is the main job of a development platform. Access has both, so let's make the best of both.

The hacks in this chapter have been drummed up with the ordinary user in mind. Entering data is a major user activity, and these hacks make this often mind-numbing activity a little more pleasant.

Use Page Break controls and command buttons so that users won't have to scroll through long data-entry forms.

Information is wonderful. The more you know, the more you can do and plan for—unless you are the one stuck entering the data. Then, all you can plan on is a lot of typing and mousing around.

Figure 3-2shows an entry form, and a rather long one at that. This entry form contains more entry fields than can reasonably fit onscreen, as evidenced by the scrollbar on the right of the form. Anyone using this form will need to scroll or tab through to the fields on the bottom.

Figure 3-1: A form that takes a lot of entries

A tab control is great for managing a lot of controls on a form. However, this hack is based on a real-life situation. I once worked on a project in which data-entry operators were entering information from legal-size forms. The entry screen had to match the layout of the form.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

An application's success often rests on user acceptance. With this in mind, it makes sense to plan how to make your application's front-end experience as visually pleasing and user-friendly as possible.

Sometimes user experience is overlooked. Developers can spend oodles of time designing fields and tables, setting up relationships, writing tricky SQL, and so on. Does this mean anything to the typical user? Not one whit!

Let's face it. Access is more than just a database. It is a database with built-in front-end tools. Tables are the core of a database, but the ability to create forms and reports is the main job of a development platform. Access has both, so let's make the best of both.

The hacks in this chapter have been drummed up with the ordinary user in mind. Entering data is a major user activity, and these hacks make this often mind-numbing activity a little more pleasant.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Use Page Break controls and command buttons so that users won't have to scroll through long data-entry forms.

Information is wonderful. The more you know, the more you can do and plan for—unless you are the one stuck entering the data. Then, all you can plan on is a lot of typing and mousing around.

Figure 3-2shows an entry form, and a rather long one at that. This entry form contains more entry fields than can reasonably fit onscreen, as evidenced by the scrollbar on the right of the form. Anyone using this form will need to scroll or tab through to the fields on the bottom.

Figure 3-1: A form that takes a lot of entries

A tab control is great for managing a lot of controls on a form. However, this hack is based on a real-life situation. I once worked on a project in which data-entry operators were entering information from legal-size forms. The entry screen had to match the layout of the form.

The Page Up and Page Down keys on the keyboard make it easy to scroll up and down through the form, but you can't control how much scrolling will occur. The odds that pressing Page Up or Page Down will leave the form right where you need it are indeed small.

Luckily, you can get this to work properly. All you need to do is add Page Break controls to the form. What are Page Break controls, you ask? Figure 3-2shows the Toolbox with the Page Break control pointed out. By placing page breaks strategically in the form design, you can get the Page Up and Page Down keys to scroll the form right to where you need it.

Figure 3-2: The Page Break control on the Toolbox

Figure 3-3 shows the form in Design mode. A Page Break control has been placed directly above the Personal Information section. This is a rather unobtrusive control. It simply appears as a string of dots in Design mode, and in View mode, you don't even see the control.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Place the insertion point at the end of the text in a text box so that additional entries land just where they should.

This technique makes so much sense, and yet it is often overlooked. Have you ever noticed that when you're editing data in a form, and you tab into a text box, the entire text is selected? Unfortunately, this default behavior makes the data vulnerable to accidental overwriting. Figure 3-6 shows the address text box fully selected. Assuming an edit is needed to add additional text (not to replace the text), the user must move his mouse to the end

Table of Contents