Hack 9. Work with Any Amount of Data

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.

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

1.10.1. Splitting Up Data

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

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.

1.10.2. Working with Split Data

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.