Let's get two-tier out of the way
Having said earlier that we will assume you want to do it tiered (I must get some T-shirts printed: Real Developers Do It In Tiers), I'll start off with two-tiers. I have a good reason for this-to cover choices between DAO, ODBCDirect, Remote Data Objects (RDO), and ActiveX Data Objects (ADO), and also to look at the most successful paradigm for this kind of development. Understanding two-tiers will also give us a basis for noting how familiar Visual Basic practices have to change when you tier.
Where's the data?
This might strike you as a straightforward question-it's in the database, isn't it? Well, the architecture we choose decides where the data is, partly because it will tell us what parts we will have. One way or another there will be data in all the parts (or, at least, data will be required by all the parts). So let's look at some architectures, find out where the data might be, and examine how to access and manipulate it.
Let's consider a typical client/server usage scenario and see how it suggests itself for database usage. A typical scenario, from the user interface perspective: a customer phones your company and wants to place an order. The order clerk has to first find the customer's details (let's assume this customer is already in the system) by inputting some values. The relevant customer details are displayed (perhaps a picklist of possible customers comes back, and the clerk has to do a little narrowing down to get the right customer and customer number to attach the order to). Once the customer number is available, the customer starts ordering stock items. Each item's availability has to be checked before an order line can be added to the order. Once all the order lines are in place, the clerk is supposed to quote the order number and the total, and if the customer is cool with it all, the clerk places the order.
From the server perspective, a customer picklist has to be created based on some user input. Relevant customer details have to be returned when the customer is positively identified. Stock items and their availability have to be checked for each order line. Finally the order has to be placed. This entire transaction has involved four distinct interactions with the database. The first three are trivial and straightforward in that they are read-only. Let's examine these first.
An ADO Tip
The number of rows that ADO fetches and caches is decided by the Recordset object's CacheSize property. If you are looking at rows in the cached row range, ADO gets that data from the cache. As soon as you scroll out of the range of cached rows, ADO releases the cache and fetches the next CacheSize number of rows back for you and puts them into the cache. Once you have data flowing back from a production size database and being used, you can watch how your application uses data. What you see can lead you to tune the CacheSize property, and this can reduce the number of network trips for data. Reducing network trips is almost always a cool thing to do.