Ken Bluttman - Access Hacks

Plan a multiple-database architecture to house any amount of datagigabytes, 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. 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 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.

Splitting the data is the key to this hack. Analyze the data, and come up with a game plan. Perhaps the data is date-based. You can split it up by month, day, or whatever makes sense.

There is an unwelcome side effect to splitting data. In a relational system, you lose the simplicity of relying on the established relationships when data is split out to additional tables. Picture this: you have a master table of customers and a related table of customer purchases. You split the customers into 10 smaller tables. What happens to the relationship? You can work around this problem in two ways.

One way is to relate the purchases table to all 10 master tables. The other is to leave out the relationships altogether and instead incorporate behind-the scenes processing to wed data back together as needed by front-end activity.

This isn't as complicated as it might sound. In a nutshell, VBA and ADO work together to find customers and purchases that match based on whatever criteria are being selected in the front end. A workable approach to find purchases that match a criterion is to create a recordset or array of records from the purchases table, and then run these records against the 10 customer tables while looking for a match on the key fields. No, this isn't an eloquent or particularly efficient way of processing data. However, it enables Access to work with gigabytes or more of data, and that is the measure of success in this case.

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. 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 .

After you enter 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.

Figure 1-23. Selecting a form by its description