Today, I'm in Australia, speaking at the Office DEVCON 2010. I'm very excited to be asked to present here and it's a wonderful opportunity to see Australia.
My topic is: Data Warehouse in Access. Ridiculous? Not so!
The premise is that there is nothing inherent in the definition of a data warehouse that excludes the use of Access as an implementation platform. Certainly, Access has limitations of size and speed, and I'm not proposing its use as an Enterprise Data Warehouse. What I am proposing, however, is you can use the principles of data warehousing (dimensional model, calculated columns, etc) to make a super fast reporting database for your transactional system, especially with respect to aggregate data reporting.
There are two sessions. Part 1 discusses the structure of an Access data warehouse (or data mart if it makes you more comfortable). Part 2 discusses various methods of reporting from your warehouse/mart.
For those interested, the session PowerPoint presentations and accompaning articles can be downloaded here:
Datawarehouse_in_Access_Part_1.zip (3 MB)
Datawarehouse_in_Access_Part_2.zip (3.7 MB)
Office Automation Samples:
ExportToExcelCharts.mdb
AutomatingWordFromAccess.mdb
.
Friday, November 5, 2010
Thursday, October 14, 2010
New Sample: Form_TreeView3TierCascaded
by A.D. Tejpal
This sample db demonstrates three tier cascaded treeview control for student tests and grades, with two way synchronization visa-vis associated subforms.
Treeview at left displays classes and students while the second treeview shows different levels of tests for various subjects - for the class in question.
Third treeview displays test results for current student, showing date of current test and marks obtained. Grades can be entered / edited conveniently just by clicking the pertinent check boxes.
For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed.
For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.
You can find the sample here:
http://www.rogersaccesslibrary.com/forum/Form-treeview3tiercascaded_topic548.html
.
This sample db demonstrates three tier cascaded treeview control for student tests and grades, with two way synchronization visa-vis associated subforms.
Treeview at left displays classes and students while the second treeview shows different levels of tests for various subjects - for the class in question.
Third treeview displays test results for current student, showing date of current test and marks obtained. Grades can be entered / edited conveniently just by clicking the pertinent check boxes.
For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed.
For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.
You can find the sample here:
http://www.rogersaccesslibrary.com/forum/Form-treeview3tiercascaded_topic548.html
.
Thursday, October 7, 2010
New Sample: Form_DsContSetDisplayPos
Form_DsContSetDisplayPos
by A. D. Tejpal
This sample db demonstrates positioning of selected block of rows in desired manner in the display window. Datasheet as well as continuous forms are covered, with the option to set start position of first row of selected block at top, middle or bottom of display screen.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/Form-dscontsetdisplaypos_topic547.html
.
by A. D. Tejpal
This sample db demonstrates positioning of selected block of rows in desired manner in the display window. Datasheet as well as continuous forms are covered, with the option to set start position of first row of selected block at top, middle or bottom of display screen.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/Form-dscontsetdisplaypos_topic547.html
.
Tuesday, October 5, 2010
Normalizing City, State, and Zip
Recently, I ran into a question on the internet about normalization that I thought would be good to repeat.
THE QUESTION:
I'm toying with the idea of starting a new project, so I'm in brainstorming mode for table design. I'll be recording customer information in this application. Typical stuff: First and Last Names, Company, Street, Apt, City State and Zip, Phone numbers(s) and extensions, E-mail.
How do you guys recommend setting up the tables for City State and Zip? I was thinking that I would have:
TBL_General_State
PKStateID
StateAbbr (Limited to 2 letters)
StateName
TBL_General_City
PKCityID
FKStateID (Lookup to TBL__State)
CityName
TBL_General_Zip
PKZipID
FKCityID (Lookup to TBL__City
ZipCode
My customer information then would record only the zip code (PKZipID). And I could then use queries for the state, city, and zip information for forms, reports, etc.
Or is this beyond overkill?
ANSWER:
By strict normalization theory, having City, State, and Zip in the same table violates the 3rd Normal Form because there are functional dependencies between those fields. However, functional dependencies are not all the same. There are strong dependencies and weak dependencies.
A strong dependency is one in which the value of a dependent field MUST be changed if another field is changed. For instance, suppose I have Quantity, Price, and ExtendedPrice, where ExtendedPrice is a calculation of the other two. If I change either Quantity or Price, the ExtendedPrice MUST be changed.
A weak dependency is one in which the value of a dependent field MAY be changed if another field is changed. City, State, and Zip are examples of weak dependencies. If I change a person's city, I may not have to change their state. They may have moved within the same state. Likewise, if I change the state, I may not have to change the city. There is, after all, a Grand Rapids, Michigan and Grand Rapids, Minnesota. The relationship between city and zip is even more complicated.
Now, it is possible to represent these fields in a fully normalized fashion, but I contend that it is more trouble for very little gain. There are two main reasons for normalizing data: minimize redundant data and maximize data integrity. Both of these can be achieved by using lookup tables for City and State without trying to represent the relationship between the two. A zip code could be mis-typed, of course, but it could also be mis-selected from a list, so to my mind there's no real reason to have a lookup table.
If you did normalize these fields, you could have a selection process that would present all possible combinations of values if you selected the City. For instance, if you had a combo box for City, you could have cascading combo boxes to select only the appropriate States and Zip codes. But it would be just as easy to mis-select the right value from this list as it would be to mis-select from independent lookup tables. And, of course, you'd have to create and maintain these relationships.
Therefore, normalizing City, State, and Zip adds a complication to your data model for very little gain, and in my opinion, is a good example of when to denormalize.
.
Tuesday, September 28, 2010
Help! My Database is Corrupted, and I Can't Repair!
As a follow up to my recent series on compacting databases, I thought I should say a word about the repairing part of Compact and Repair.
As a file-based database system, Access is more susceptible to corruption than a server-based database like SQL Server. However, Access databases are not quite as fragile as many believe. The most common causes are external to Access: PC hardware problems, network problems, users shutting down Access, or killing the Access process when it is accessing the disk. A few are internal: use of the Name Autocorrect feature, multiple users using the same file, or memo fields. These last do not cause corruption, but corruption is more likely when used.
In my experience, most of the time a corrupt database can be fixed by Compact and Repair. I personally have had only one database that was not. That one happened when I lost network connection while I was compacting the database.
But although it doesn't happen often, Access databases can be corrupted beyond repair, or at least beyond the ability of Compact and Repair to fix. In some cases, you've no alternative but to resort to your backup. (You DO back up your database regularly, don't you?) But there are things you can try before you have to resort to that.
The following links are some of the standard references for recovering a corrupt database.
Jerry Whittle's Fix Corrupt Access Database v4.5
Allen Browne's Corruption Tips
Tony Toew's Corrupt Microsoft Access MDBs FAQ
.
As a file-based database system, Access is more susceptible to corruption than a server-based database like SQL Server. However, Access databases are not quite as fragile as many believe. The most common causes are external to Access: PC hardware problems, network problems, users shutting down Access, or killing the Access process when it is accessing the disk. A few are internal: use of the Name Autocorrect feature, multiple users using the same file, or memo fields. These last do not cause corruption, but corruption is more likely when used.
In my experience, most of the time a corrupt database can be fixed by Compact and Repair. I personally have had only one database that was not. That one happened when I lost network connection while I was compacting the database.
But although it doesn't happen often, Access databases can be corrupted beyond repair, or at least beyond the ability of Compact and Repair to fix. In some cases, you've no alternative but to resort to your backup. (You DO back up your database regularly, don't you?) But there are things you can try before you have to resort to that.
The following links are some of the standard references for recovering a corrupt database.
Jerry Whittle's Fix Corrupt Access Database v4.5
Allen Browne's Corruption Tips
Tony Toew's Corrupt Microsoft Access MDBs FAQ
.
Monday, September 27, 2010
New Sample: Form_Resize
Form_Resize
by AD Tejpal
This sample db demonstrates resizing of access forms so as to suit current screen resolution. In addition, user has the option to carry out custom resizing - if desired.
Five styles of demo forms are included as follows:
(a) Simple controls - all free to float and resize.
(b) Simple controls - with certain controls having tag property settings for locking their position and / or size.
(c) Combo box and list box.
(d) Nested subforms - Continuous.
(e) Nested subforms - Datasheet.
(f) Tab control having (i) Nested subforms and (ii) Option group.
Tag property settings for steering the behavior of individual controls are as follows (more than one setting (separated by ;) can be included in the tag string):
(a) LockLeft: The control retains a fixed distance from left edge of the form.
(b) LockRight: The control retains a fixed distance from right edge of the form.
(c) LockTop: The control retains a fixed distance from top edge of the form.
(d) LockBottom: The control retains a fixed distance from bottom edge of the form.
(e) LockLeftRight: The control expands in such a manner that its left edge retains the original distance from left edge of the form, while its right edge retains the original distance from right edge of the form.
(f) LockTopBottom: The control expands in such a manner that its top edge retains the original distance from top edge of the form, while its bottom edge retains the original distance from bottom edge of the form.
(g) LockSize: The control retains its original size.
(h) LockWidth: The control retains its original width.
(i) LockHeight: The control retains its original height.
Demo forms have all been designed for a screen resolution of 800 x 600. On opening any of these forms, it resizes automatically to suit the screen resolution currently in force (if it happens to be different from the designed one).
You can find the sample here: Form_Resize
.
by AD Tejpal
This sample db demonstrates resizing of access forms so as to suit current screen resolution. In addition, user has the option to carry out custom resizing - if desired.
Five styles of demo forms are included as follows:
(a) Simple controls - all free to float and resize.
(b) Simple controls - with certain controls having tag property settings for locking their position and / or size.
(c) Combo box and list box.
(d) Nested subforms - Continuous.
(e) Nested subforms - Datasheet.
(f) Tab control having (i) Nested subforms and (ii) Option group.
Tag property settings for steering the behavior of individual controls are as follows (more than one setting (separated by ;) can be included in the tag string):
(a) LockLeft: The control retains a fixed distance from left edge of the form.
(b) LockRight: The control retains a fixed distance from right edge of the form.
(c) LockTop: The control retains a fixed distance from top edge of the form.
(d) LockBottom: The control retains a fixed distance from bottom edge of the form.
(e) LockLeftRight: The control expands in such a manner that its left edge retains the original distance from left edge of the form, while its right edge retains the original distance from right edge of the form.
(f) LockTopBottom: The control expands in such a manner that its top edge retains the original distance from top edge of the form, while its bottom edge retains the original distance from bottom edge of the form.
(g) LockSize: The control retains its original size.
(h) LockWidth: The control retains its original width.
(i) LockHeight: The control retains its original height.
Demo forms have all been designed for a screen resolution of 800 x 600. On opening any of these forms, it resizes automatically to suit the screen resolution currently in force (if it happens to be different from the designed one).
You can find the sample here: Form_Resize
.
Friday, September 24, 2010
How Can I Compact my Access Database Less Often?
So far in this series, I have talked about what compacting is, how it works, and several ways to compact, both through the Access Interface and in VBA code. However, one subject that's rarely discussed is how to reduce the need to compact the database in the first place. The number one reason for compacting is due to "database bloat", where records are added, deleted, and the space is not recovered until compacted. Reducing bloat will reduce the frequency of having to compact your database.
Here are some strategies:
Split the Database (FE/BE)
A split database is one in which the application (queries, forms, reports, macros, and code) are in a separate physical database file (known as the Front-End or FE) from the tables (which file is known as the Back-End or BE). Since bloating affects the tables most, having them in their own file means that only the BE needs to be compacted. As I showed last time, this can be initiated from the FE fairly easily.
There are other good reasons besides this to split your database, including improved multi-user access and less corruption. Splitting the database is such a good idea, in fact, that I recommend it for almost every application.
Importing Data
Just as bloat is the number one reason for compacting, importing data is the number one reason for bloat. A common scenario is importing at text file to a temporary table, massaging or formatting the data in some way, appending it to the permanent table, then deleting the temp table. As we've seen, while Access with grow the database dynamically, it will not automatically shrink it when the data is deleted. If you import a lot of data this way, you can find yourself needing to compact often. There are a couple of ways around this:
Linking Files
Instead of importing files to a temporary table, consider linking them. You can link many kinds of files including external Access tables, Excel files, text files, or other external sources. These linked tables do not contribute to bloat nearly as much as importing them, although as we'll see later, processing them may cause some database growth. Of course any time you add records to your database, the file size will grow. Nothing can be done about that. But minimizing the amount of temporary data you import to your database will keep growth within normal bounds.
Importing to temp database
If you must import data to a table for some reason, consider creating a temporary database to store the data and link those tables into your database. After you're done with the import process, the temporary database can be deleted. You an even create, populate, link, process, and delete temporary databases programmatically from your FE application. On my website is a small sample database called ImportToTempDatabase.mdb which illustrates how.
Don't obsess - some "bloat" is okay
Not all bloat is bad. Some of it is necessary. Aside from inserting and deleting records, databases can grow due to internal processing. When the database engine runs a query or a recordset based on a query, it has to process and store it somewhere, and that "somewhere" can cause your database to grow. This growth is not a cause for concern.
If you find that immediately after compacting your database grows dynamically at first, but then slows to minimal growth, that first, dramatic growth is just Access creating the working space it needs.
.
Here are some strategies:
Split the Database (FE/BE)
A split database is one in which the application (queries, forms, reports, macros, and code) are in a separate physical database file (known as the Front-End or FE) from the tables (which file is known as the Back-End or BE). Since bloating affects the tables most, having them in their own file means that only the BE needs to be compacted. As I showed last time, this can be initiated from the FE fairly easily.
There are other good reasons besides this to split your database, including improved multi-user access and less corruption. Splitting the database is such a good idea, in fact, that I recommend it for almost every application.
Importing Data
Just as bloat is the number one reason for compacting, importing data is the number one reason for bloat. A common scenario is importing at text file to a temporary table, massaging or formatting the data in some way, appending it to the permanent table, then deleting the temp table. As we've seen, while Access with grow the database dynamically, it will not automatically shrink it when the data is deleted. If you import a lot of data this way, you can find yourself needing to compact often. There are a couple of ways around this:
Linking Files
Instead of importing files to a temporary table, consider linking them. You can link many kinds of files including external Access tables, Excel files, text files, or other external sources. These linked tables do not contribute to bloat nearly as much as importing them, although as we'll see later, processing them may cause some database growth. Of course any time you add records to your database, the file size will grow. Nothing can be done about that. But minimizing the amount of temporary data you import to your database will keep growth within normal bounds.
Importing to temp database
If you must import data to a table for some reason, consider creating a temporary database to store the data and link those tables into your database. After you're done with the import process, the temporary database can be deleted. You an even create, populate, link, process, and delete temporary databases programmatically from your FE application. On my website is a small sample database called ImportToTempDatabase.mdb which illustrates how.
Don't obsess - some "bloat" is okay
Not all bloat is bad. Some of it is necessary. Aside from inserting and deleting records, databases can grow due to internal processing. When the database engine runs a query or a recordset based on a query, it has to process and store it somewhere, and that "somewhere" can cause your database to grow. This growth is not a cause for concern.
If you find that immediately after compacting your database grows dynamically at first, but then slows to minimal growth, that first, dramatic growth is just Access creating the working space it needs.
.
Subscribe to:
Posts (Atom)