Full Database |
 |
by Raymond P. Woerner |
I've always been fascinated by how things work. In Microsoft Access, the data access objects and MS-Access objects give you access to all the capabilities of the database. I'll briefly discuss some of those capabilities in this article.
Maintaining Your Database
You're probably familiar with the "Corrupted database... Attempt to repair?" message. This object attempts to repair your database when it detects a problem. You can also compact your database to remove unused space in the file and save on disk space. This can be done either from the full version of MS-Access or from the "run-time" version.
Creating Another Database
You can create a new database from within your current database. You could ship part of an application and have the original database create other parts of the application. It would also be possible to save the definition of a database as a text file and recreate the database in a different version of MS-Access from the text file.
Transaction Processing
Many businesses want an individual transaction, such as the sale of a product, to change many other parts of the application. A product sale may need to update company and store year-to-date totals, salesperson month-to-date totals, and match register receipts.
However, if only some of these updates succeed, some parts of your application would not reflect the sale. We need all the updates to succeed or none of them to succeed. Fortunately, you can define all these updates as a single transaction which will succeed or fail together. This prevents any of the updates from taking place unless they can all take place.
Dynamic Record Selection
One of the most useful aspects of modern software is the ability to switch between different levels of information. You can double-click on the name of a group in one list box and have all its members appear in another list box. You can drill down from a high-level view to an individual item in that view.
In MS-Access, you design a table hierarchy which supports the type of view, change the underlying query to produce the selected set of records, run it, and show the result.
Redefining Tables
You don't need to know every last column of your table when you define it, you can let your data automatically redefine the table. If you're selling products, summarizing those sales on a monthly basis, and loading the results into a separate table, you can code your application to automatically add a new column to the table for the products.
Context Sensitive Forms and Reports
Does it make sense for a form to have a "find" command button if there are no records? Wouldn't you like to highlight important information on that monthly sales report? In MS-Access, you look at the records underlying your form or report, determine valid commands for a form or important information for a report, and display the appropriate controls.
Tracking User Actions
Require users to log on to the database and you can use information about the current state of the application to track who is accessing forms and reports.
Ray Woerner, a HAL-PC member, is owner of Sage Database Incorporated. He does applications development in Microsoft Access, Excel and Visual Basic.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.
Back to the Magazine Home Page