Monday, December 29, 2008

What Is Normalization: Part III

Putting It Back Together

In What Is Normalization, Part I, I defined normalization and the negative consequences of not normalizing your database. In Part II , I talked about how to normalize your tables to remove as much redundant data as possible. Now, it isn't much good to split your data into separate tables without the ability to put it all back together. For that, we need to talk about relationships.

Relationships store the information about how normalized data can be put back together. They are the heart and soul of a relational database like Microsoft Access. If you don't have relationships in your database, you don't really have a relational database.

Continuing our example from Part II, we have two tables: Employee and Salary History.

Figure 1: Employee

Figure 2: Salary History


So how do we create a relationship between them? In a relational database, tables are related on common data elements -- specifically the primary key of one table and the foreign key in the other. I've defined primary key elsewhere, but foreign key is new. A foreign key is simply a field in another table that has the common data elements needed to create a relationship.

In our example, SS# is the primary key of Employee. In the Salary History table, SS# is the foreign key to the Employee table. If you've normalized your database properly, your related table should have a foreign key. Figure 3 shows how the data in our two tables will be related.

Figure 3: The tables related

So how do we create this relationship? In Access, the easiest way is to use the Relationship Window. In Access 2003 and before, it can be found on the Database Toolbar or on the menu choosing Tools > Relationships. In Access 2007, go to the Database Tools tab on the Ribbon and choose Relationships.

To create your relationship, add both tables to the Relationship window. I usually RIGHT-click on the empty relationship window and choose Add Table. Your window should look like Figure 4.

Figure 4: Relationship window with tables added.


Next, Click on the primary key (SS#) in Employee and drag it over to the corresponding field in Salary History (also SS#). When you finish, the Edit Relationships dialog box will pop up. Figure 5 shows the dialog box.

Figure 5: Edit Relationships dialog box


It will already have the fields filled in. In order to actually make this a relationship, however, you MUST click the Enforce Referential Integrity check box. If this box is not checked, it it NOT a relationship.

If you cannot click the Enforce Referential Integrity box, then there is something wrong with your relationship. More than likely, it will say Indeterminate as a Relationship Type. Usually, this means you have attempted to create a relationship without one of the fields being a primary key. Don't simply uncheck the box and continue. Go back and fix the fields.

Click Create to finish the relationship.

Figure 6: Completed Relationship


You can tell that Referential Intergity has been enabled because the relationship line has a 1 on one end and an infinity symbol on the other. This indicates it is a One-to-Many relationship. That is, each record in the Employee table is related to one or more records in the Salary History table. The 1 will be next to the table where the link field is the primary key. If you do not have those symbols on your relationship, you do not have a relationship.

Here, I showed the One-to-Many relationship, which is the most common type in a relational database. But there are two others: One-to-One and Many-to-Many. I'll talk about those next in What is Normalization: Part IV.

.

Wednesday, December 24, 2008

What Is Normalization, Part II

Break it up. Break it up.

In What Is Normalization, Part I, I discussed various ways to represent data in a tabular form. All of those methods have problems -- from not storing enough data, to storing redundant data, to allowing data anomalies.

The Relational Database Model was created by Edgar F. Codd specifically to solve those problems. The foundation of the Relational Model is a process Codd dubbed "Normalization". It is the process of grouping related data elements into separate tables and then relating those tables on common data elements.

Since an example is worth a thousand cliches, let's look again at the example from Part I.

Figure 1: Salary History
Figure 1 shows the salary history of a small company. As is, it represents the lowest level of data organization a table must have in a database. Codd called this level the First Normal Form (1NF). (Don't worry, I'm not going to explain all the normal forms.)

Another rule of Normalization says that all of the fields in a table should be about just one "thing". But a look at Salary History reveals that some of the fields are not really about the salary history at all.

How do I know? Well, we can't talk about normalization without discussing primary keys. (See What is a Primary Key for a more detailed discussion.) The way that we can tell if all of the fields are about the same "thing" is by seeing if all the fields in the record depend on the entire primary key.

In Figure 1, we can see that the fields necessary to uniquely identify the record are SS# and SalaryDate (the date on which the salary increase was given.) But do FirstName and StartDate depend on the ENTIRE primary key? No, they do not. FirstName or StartDate do not change if the SalaryDate changes.

Therefore we need to move them to a different table. What table? Well, what are the fields about? In this case, they are about the employee, so we will create an employee table composed of FullName, SS#, and StartDate. SS# is the most obvious choice for a primary key in this example. (Note: Social Security number is NOT usually the best choice for a primary key in real life.)

Do FullName and StartDate depend on SS#? Yes they do. The SS# represents a single, real-life person and that person can have only one name and one start date.

Now we are left with three fields in Salary History: SS#, SalaryDate, and Salary. SS# and SalaryDate remain the primary key. Does Salary depend the entire primary key? Again, yes. Each person can have only one Salary increase on a particular date.

So normalizing the table structure requires two tables: Employee and Salary History, like so:

Figure 2: Employee


Figure 3: Salary History


Of course, now we have to somehow put the data back together. For that, we need Relationships. We'll look at that next time in What is Normalization, Part III.


.

Thursday, December 18, 2008

What Is Normalization, Part I

Why Normalization?

What is normalization? Normalization is a methodology for removing redundant data from a database WITHOUT losing information.

There are many ways to represent data. Some of the most common are: spreadsheets, flat files, and relational databases. Each of these ways have their own advantages and disadvantages.

For instance, in a spreadsheet, it's acceptable to represent the data like this:
Figure 1
This is perfectly readable to a human. It is obvious that the row beneath Gina Fawn also belongs to her. Unfortunately, this is impossible for a database to interpret. The database must have all the information it needs on a single row. It cannot refer to another row to get the information it needs.

One way to correct this, would be to fill in the missing information.

Figure 2

Unfortunately, this requires storing a lot of redundant data. What's the big deal? It's only a couple of fields, right? But that's only in the example shown. What if we were storing all of the demographic data (name, address, phone, city, state, etc.) for a lot of people? This would waste a lot of storage capacity.


But wasted storage is not the worst problem. What if the SSN of Gina Fawn's first record was changed to 215-87-7854? Perhaps this was through operator error or maybe a programmatic update. It doesn't matter, the data has been changed. Now, which SSN is really Gina's? The database has no way of knowing. Worst still, the SSN matches Tony Jones. So, does that SSN represent Gina or Tony? Again, no way to know.

This same problem holds true for all the fields which hold redundant data. This is called a Data Anomaly error. Once you start having data anomalies, you cannot trust the integrity of your database.

One way to solve this problem is with Repeated Columns. This is a common solution in spreadsheets. With repeated columns, the non-redundant information (like row two in Figure 1) are stored as columns. We get something like this:

Figure 3

Now we don't have problems with redundancy, but we have additional problems. First of all, we have to decide how many repeated columns to create. In Figure 3, I only show one salary increase for Gina and Tony, but is that reasonable? What if Gina has five wage increases and Tony had seven? Is seven sets of columns enough? Do I cap it at the largest record? Or do I add more columns to accommodate growth? If so, how many?

Secondly, such a table structure requires a lot of manual modification and becomes untenable when you have a lot of data. Perhaps instead of just the date and salary, we are also storing the job description, pay grade, status, and so forth? The structure would be come so large and unruly that it would be impossible to maintain.

Fortunately, Normalization comes to the rescue. In What Is Normalization, Part II, I'll explain how. Thank Codd.

.

Monday, December 15, 2008

New Sample: Form_DynamicSizingOfSubform

Author: A.D. Tejpal

This sample db demonstrates run time manipulation of subform control height. It covers both views for the subform, i.e. continuous form as well as datasheet. The views can be toggled from one to the other by clicking a command button.

Depending upon the layout for a specific form, it is preferable to specify the maximum number of rows upto which the subform height should expand. This figure can be entered in text box named TxtMaxLines. Default value for max lines is set at 6. While stepping through the records on main form, if the records in linked subform happen to exceed MaxLines, further increase in the height of subform is kept in abeyance. At this stage, vertical scroll bar is inserted. As and when the subform records are again within limits, the vertical scroll bar is removed and the height of subform control adjusts suitably.

In case of datasheet view, care has been taken to dynamically adjust the column widths so that no awkward blank space is visible at right in lieu of scroll bar when it is not needed.

Two alternative ways are available for navigating through records on the main form. The user can either use the normal navigation buttons or the unbound combo box for jumping to any desired record. The combo box is suitably synchronized so that even while using normal navigation buttons, the value displayed in combo box matches the current record.

Version - Access 2000 file format

You can find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=441

New Sample: Form_DynamicSizingOfSubform

Thursday, December 11, 2008

DH Query By Form

DH Query By Form

Author: Duane Hookom

The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys.

The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (datasource) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow he user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields.

***NEW FEATURE***
An all Access mail merge feature was added Mar 6, 2004. This allows users to create simple mailmerge output without using a word processing program such as MS Word.

You can find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=12

DH Query By Form

Monday, December 8, 2008

What is a Primary Key?

If I have a small company, any individual employee can be distinguished by a combination of First Name, Middle Initial, and Last Name. This combination uniquely identifies each employee.

If I have a larger company, the chances increase that I could have two employees that have the same name. So the name can no longer be used to uniquely identify a record. So instead, I could use a number like Social Security Number.

A primary key is a special kind of index (see What is an Index?) that is composed of a field (SSN) or combination of fields (First/Middle/Lastname), which uniquely identify a record.
A primary key has a number of useful properties.

First, the value in the primary key cannot be duplicated. If it is a single field, that value cannot be repeated. If it is composed of multiple fields, that combination of values cannot be duplicated. So in the samples above, having a primary key would mean that I could not put two "Roger J Carlson"s in my database.

Secondly, the primary key cannot be NULL and no portion of the primary key (in the case of a multiple field key) can be NULL. (See What does NULL mean?). The NULL means the value of the field is unknown. Obviously, if we don't know the value of the fields, we can't guarantee the value is unique. So disallowing NULLS guarantees we have a valid value in the key fields.

Thirdly, in order to create relationships, there must be a unique index on the field. Since by definition, a primary key is a unique index, a primary key makes an ideal join field. I will discuss this in a later post.

There are two basic types of primary keys: Natural Keys and Surrogate Keys.

Natural keys

A natural key is one composed of a field or fields that already exist in the table. In my examples above, both Social Security Number and Firstname/Middle/Lastname are natural keys. Natural keys can be composed of a single field or multiple fields.

It is important to note that a table can only have a *single* primary key. It is incorrect to say that a table has multiple primary keys. In the case of a multi-field primary key, it has a single primary key composed of multiple fields.

Surrogate keys

A surrogate key is an artificially created number. It has no real-world meaning, and is used mostly in relationships with other tables. In Access, you use the Autonumber datatype to create a surrogate key. This number is system-created and is guaranteed to be unique.

The disadvantage of a surrogate key is that it does not have real-world uniqueness. It would be possible to enter two records for Roger J Carlson, each with a different system-created number. To protect against that, you should also create a unique index on those fields that would otherwise create a natural key.

The advantage of a surrogate key is that it will never be affected by real-world changes to the database. It is also much more efficient to join tables on a single number than on multiple text fields.

Opinions differ, but I prefer a surrogate primary key with a separate unique index. This separates the functions of the primary key: the surrogate key for relationships and the unique index to control real-world uniqueness. In this way, if the conditions that effect the real-world uniqueness (as in the case of moving from a small business to a large business mentioned above), the table relationships will not be disturbed.

Creating a primary key in the Access User Interface is easy. Just open the table in Design View. For a surrogate or single field primary key, select a single record and click the Primary Key button.


For a multi-field primary key, just hold the Control [Crtl] key as you select the fields and then click the primary key button.

Thursday, December 4, 2008

New Sample: Report_HideGrpHdrsIfNoDetail

Report_HideGrpHdrsIfNoDetail

Author: A. D. Tejpal

This sample db demonstrates run time hiding of group headers and footers in access reports, if there are no longer any printable records for the group as a result of conditional cancellation of detail section's format event.

This functionality is achieved by using a set of two classes. The main class (C_CondHeaders_Main) determines the maximum existing number of group levels and report sections, apart from building up a list of unique group keys. This class also builds up a collection of objects conforming to sub-class (C_CondHeaders_Sub).

Each instance of the subclass represents a group header or footer section. Instead of trying to pass report section object as an argument, simply the section index is supplied, thereby circumventing the hurdle otherwise faced. Corresponding report section object (WithEvents) is generated within the sub-class.

List of unique group keys generated in ShowDetal method of main class gets pushed to the sub-class via the latter's ShowGroups property. Action for hiding the group header / footer as applicable, is carried out in the sub-class.

With the combination of two classes as outlined above, the arrangement becomes completely generic, capable of handling any number of grouping levels. At the same time, the code becomes drastically shorter and compact.

IMPORTANT:

  1. The report must have a calculated text box having [Pages] as part of its expression.
  2. Each group header section should have a text box bound to that level's ControlSource field
  3. If there are any sorting/grouping levels above the last header which do NOT have headers, then for each such level, the report must include a textbox (may be hidden) located in Detail section, bound to the field serving as group's ControlSource.
Version - Access 2000 file format

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=440

Wednesday, December 3, 2008

Access 101: What is an Index?

An index is a method of cataloging the records in a table to increase the speed and efficiency of retrieving them. You can think of it as a library card catalog.

If the stacks in a real-world library represent your table, the library card catalog is the index. The library card catalog stores just the information necessary to identify a particular book: author, title, and genre, plus a number that identifies where the book can be found in the stacks.
With this number, you can go directly to the location of your book. Without it, you would have to start at the beginning of the stacks and look at each book until you found the one you wanted. (This assumes that the books are not stored in any particular order.)

An index works the same way. It stores the value of a field or combination of field and the location of each record that match it. With this information, a query that sorts or searches on the indexed field(s) can go directly to the records. Without it, it has to start at the beginning of a table and look at each record until it finds the ones it wants.

There are a number of different kinds of indexes: simple indexes, multi-field indexes, unique indexes, and clustered indexes.

Simple indexes are indexes on a single field. They may or may not allow duplicate values. Their main use is for searching and sorting records in a table.

Multi-field indexes are indexes across multiple fields. These also may or may not allow duplicate values. This is different than having simple indexes on multiple fields. In a multi-field index, the combination of records is indexed.

Unique indexes do not allow duplicate values. They may be either simple or multi-field. If it is multi-field, it will allow duplicates in individual fields within the indexed fields, but it will not allow a duplicate across the all of the fields in the index. This is useful to make sure you don't have duplicate records in your table.

Clustered indexes control how the records are stored in the database. If you have a clustered index, the records will actually be stored in that order. A table can have only one clustered index. This makes sense since records in a table can only be physically stored in one order.

Primary Keys are a special type of index. In Access, the primary key is both a unique index and a clustered index. It has one additional property in that no field in the index can be NULL. (see What does NULL mean? ). There can be only one primary key in a table. For more on primary keys, their creation and uses see: What is a Primary Key?

How do you create an index?

For simple indexes, it's easy. Just go to the Design View of the table and select a field. In the Properties, you'll see an Indexed property. Select one of the "Yes" options. To make it a unique index by selecting Yes (No Duplicates).

For multi-field indexes, it's a little more complicated. The simplest thing to do is create a simple index on the first field in your index.


Then on the line directly below it, add another field WITHOUT giving it an Index Name. If the Index Name field is blank, Access will assume the field is part of the index directly above it.


Add as many fields to the index as you want. From the Indexes Window, you can also set the Unique, Primary, and Allow Nulls properties.

A word of warning about indexes. While they do speed up the retrieval of records, they also slow down the insertion of records. Every time a new record is added, all the indexes need to be rebuilt. If you have too many indexes and a lot of records, this can be a problem. Therefore, it is best to just index those fields that really need indexing. These would include fields that you will be sorting or searching on or fields that participate in Relationships or Joins.

Thursday, November 20, 2008

Access 101: Why Do I Get the "Ambiguous Outer Join" Error

To see a video of this article, click here: Ambiguous Outer Join Error

Thanks to Webucator for creating this video. https://www.webucator.com/microsoft-training/access.cfm

The Outer Join can be a powerful tool for querying data in Microsoft Access. When you have only two tables, there is usually no problem. When there are more than two tables, however, using an Outer Join becomes more complicated. Sometimes Access allows it, and sometimes it gives you the not-very-descriptive "Ambiguous Outer Join" error.

Why? Well, first we'll look at what an Ambiguous Outer Join is, and then see how to correct it.

Microsoft Access has three types of joins: the Inner Join, the Right Join and the Left Join. Both the Right and Left joins are known as Outer Joins. An Inner Join shows only those records that exist in both tables. However, an Outer Join (both Right and Left) shows all of the records from one table (the Base Table) and just the matching records from the other (Secondary Table).

When Access processes a multiple table query, it needs to determine the order in which to join the tables. Should it join Table1 to Table2 first and then join Table3? Or should it do it in some other order? This is part of the Rushmore technology of the Jet engine. It tries to determine the most efficient way to process the query.

In the case of standard Inner Joins, no matter what order the tables are joined the result set will be the same. However, this is not the case with Outer Joins. There are times, when using an Outer Join, that the result of the query will be different depending on the order the tables are joined. When this happens, you have an Ambiguous Outer Join.

So how do you know when an Outer Join will result in an error? The easiest way to understand it is in terms of what you see in the Query Builder grid.

A table which participates in an Outer join as a Secondary Table (that is, the arrow is pointing *towards* it) cannot participate in either an Inner Join, or as a Secondary Table in another Outer Join. Figure 1 shows two types of queries that will result in an Ambiguous Outer Join error.

Figure 1: Two illegal Outer Join Queries

However, the table participating in the Outer Join as a Secondary Table can participate in another Outer Join if it is the Base table of the other Outer Join (that is, the arrow points *away* from it). Figure 2 shows a query that will not result in an Ambiguous Outer Join error.

Figure 2: A legal Outer Join Query

So what do you do if you need to create a query like case 1 or 2? You have to split the query into a stacked query, that is, two queries, the second of which uses the first. This is exactly what the Ambiguous Outer Join error message suggests.

Create a query joining the first two tables with an Outer Join and save it as a named query (i.e. Query1). Then, in a second query, join the first query to the third table. Figure 3 shows how to build a stacked query.
Figure 3: Shows how to create a query, which will not have an Ambiguous Outer Join by splitting it into two queries.

So the Ambiguous Outer Join error is not really all that confusing. It simply means that the database wants you to decide which join it should create first. In Access, you do this by spitting the query into a stacked query.

Tuesday, November 18, 2008

New Sample: Form_ControlGroups_2K3

Form_ControlGroups_2K3

Author: A.D. Tejpal

This sample db demonstrates handling of form controls in distinct groups in such a way that actions meant for a given group get performed across all controls belonging to that group, without having to process any loop for tackling individual controls. This functionality is achieved via class module named C_ControlGrps. Grouped controls get assigned to a collection, as instances of this class.

Following styles of grouping are shown - covering two distinct groups in each case:
(a) Permanent grouping via control's tag value assigned at design stage.
(b) Dynamic grouping allowing the user to add / remove controls to any of the groups as desired, at run time.

For current illustration, following actions performed on the groups at run time, are demonstrated:
(a) Manipulation of default values group-wise.
(b) Locking / unlocking of controls group-wise.
(c) Restricting tabbing to a particular group as desired.
(d) Hiding / un-hiding of groups as desired.
(e) Manipulation of back colors group-wise, via slider bars for R/G/B color components.
(f) Special effects - This involves a continuous cycle of smooth transition of shades for the back color, interspersed with periodic swapping of back colors between the two groups.

You can find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=438


Monday, November 17, 2008

Featured Sample: AppointmentsAlert

AppointmentsAlert

Author: A.D. Tejpal

This sample db demonstrates an appointments planner featuring audiovisual alarm for events becoming due.

The Planner opens with appointment grid pre-selected for today's date and the cursor moves to current time slot.

If any other date (not older than yesterday) is selected in the Date Picker control, fresh appointment grid for that date is presented (if not already existing).

When put into standby mode, the utility goes into minimized state and keeps scanning the status of scheduled appointments at specified time intervals.

As & when an event becomes due, an audio alarm is sounded accompanied by a pop-up form displaying the current status of various appointments.

The time in advance of an event for sounding the alarm, as well as the frequency of scanning is amenable to customization by the user.

Audio alarm is governed by the *.wav file located in the same folder as that containing the sample db. If it is desired to use a different sound, simply replace this file by the one desired.

The zip file contains two versions (Access 2000 file format), developed as follows -
(a) Access 2002 installed on Windows XP
(b) Access 2000 installed on Windows 98

Caution -
(a) An activex control named 'Microsoft Date And Time Picker Control' has been used in this database. This control is required to be registered in the access installation on user's computer, before attempting to open & use the sample db.
(b) For registration - file mscomct2.ocx should be available in System32 folder of windows operating system. Open any access database other than this db (even a blank one will do), click Tools -> Activex Controls -> Register. Navigate to the location of above .ocx file and click OK.

Versions - XP & 2K (both in Access 2000 file format)
References -
(a) XP Version -
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft Office 10.0 Object Library DAO 3.6
(a) 2K Version -
Visual Basic For Applications
Microsoft Access 9.0 Object Library
Microsoft Office 9.0 Object Library DAO 3.6

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=141

Thursday, November 13, 2008

Access 101: How Do I Find The Path and Filename For My Database?

Access 2000 and Later

In Access 2000 and later, this is easy. Access provides an object called CurrentProject, which refers to the currently opened database. This object as two properties (among others) called Path and Name.

So to return the path and name, you can do this:

'declare a couple of variables
Dim strPath as String
Dim strFile as String
Dim strPathName as String

'call the properties
strPath = CurrentProject.Path
strName = CurrentProject.Name
strPathName = strPath & "\" & strName

Of course, if you only want the whole path and file name, you don't actually need the path and name variables. You can do this:

strPathName = currentproject.Path & "\" & currentproject.Name

Access 97 and Earlier

Access 97 doesn't have the CurrentProject object, but it does has the CurrentDb object. This can be used in a similar fashion.

CurrentDb also has a property called "Name", but unlike CurrentProject.Name, CurrentDb.Name returns the entire path and file name.

'create string variables
Dim strPathfile as String
Dim strPath as String
Dim strFile as String

Next we set the database variable to the current database. Access provides an object called CurrentDb to do this:

strPathfile = CurrentDb.Name

Since this returns both the path and the file name, you need to separate them:

strPath = (Mid(strPathfile, 1, Len(strPathfile) - Len(Dir(strPathfile))))
strFile = Dir(strPathfile)

Wednesday, November 12, 2008

Featured Sample: Excel to Access 2000-XP Converter Utility program

Excel to Access 2000-XP Converter Utility program

Author: Peter Hibbs

This utility program allows you to convert an Excel spreadsheet file which contains 'flat-file' data into one or more tables in an Access database.

The program creates primary and foreign keys in the tables so that 'One to Many' or even 'Many to Many' related tables can be created automatically. You just import the Excel file into the program, create a template which tells the program which columns in the spreadsheet should be copied to which fields in which tables, click a button and the program will extract the data into a series of separate tables which can then be exported to a new database via .csv files.

The program can also be used to create properly 'normalised' tables from data in MS Works or any other database by first exporting the data to an Excel spreadsheet (or .csv file which can then be imported into Excel). Full instructions are included.

Version.
This version runs under Access 2000 and Access XP (2002).

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=182

Monday, November 10, 2008

Access 101: How Do I Replace System Error Messages With Custom Messages?

Access provides a number of error messages, but many are confusing to the users. I find it sometimes useful to replace the system error messages with custom error messages. The Null primary key is a case in point.

When you try leave a record on a form without a primary key value, the form will display the following error.

But you might want to replace this with your own error message, identifying the actual field.

At it's simplest, you can use the Form_Error event to trap for this and otherrecord-level errors. Something like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3058 Then
MsgBox "You must give Account Number a value."
Response = acDataErrContinue
Else 'msgbox DataErr
Response = acDataErrDisplay
End If

End Sub

3058 is the error number for the null primary key. The "Else" section will let any other error display the normal system error message.
If you want to know the number for another error, just un-comment the msgbox and you'll get the error number. Add any additional errors as an ElseIf.

Other errors you can trap:

Limit to List: 2237
Input Mask: 2279
Required field: 3314
Validation Rule: 2107
Bad Data Value: 2113

Tuesday, November 4, 2008

Access 101: How Do I Bypass Start Up Options?

In a previous post, ( How Do I Configure My Database Start Up? ) I discussed how to set Start Up options for Access. But what if you want the database to open without running those options?

Access provides a Bypass Key to do this. Simply hold the Shift Key while opening the database, and the start up options will not run. Depending on your security settings, you might see one or more security messages. You must continue to hold the Shift Key down while you answer the security messages.

It is also possible to disable the Bypass Key if you don't want your users to be able to bypass the options. To do this, set the AllowBypassKey property to False in a macro or VBA module.

Here are some of good, advanced resources: http://www.databasedev.co.uk/disable_shift_bypass.html
http://www.tek-tips.com/faqs.cfm?fid=1429
http://www.mvps.org/access/general/gen0040.htm

But be careful. It's possible to lock yourself out if you don't know what you're doing. In general, I discourage novice users from turning off the bypass key until they know more about the implications of doing so. At the very least, test it on a copy of your database.

Tuesday, October 28, 2008

Access 101: How Do I Run A Process Automatically Once A Day? Part I

There are two ways to do this: 1) from within Access, and 2) externally with Windows Scheduler. I talk about method 2 here: How Do I Run A Process Automatically Once A Day? Part 2 .

Suppose you have a macro (mcrImportFiles) that imports data and you want to schedule it for the middle of the night each night -- say 1 am. How would you do it?

First of all, to do this, you need to leave your Access database open all the time (or at least start it every night) because it must be open to work.

Next, you need to create a form that will stay open all the time. Have this form open automatically at Start Up (see How Do I Configure My Database Start Up? ). It can be hidden if you want. (see How Do I Hide a Form But Leave It Running? ).

Every form has at Timer Property and an OnTimer event. Set the Timer property to 60000 (60 seconds). This will cue the form to run the OnTimer event once each minute.

Now, add some code to the OnTimer event. Something like this:

Private Sub Form_Timer()
If Time() >= #1:00:00 AM# And Time() < #1:01:00 AM# Then DoCmd.RunMacro mcrImportFiles End If End Sub This will run the macro each day at 1 am. To make this process even more robust, convert the macro to VBA code (see How Do I Convert A Macro to VBA Code? ) and insert it directly into the OnTimer event. This way, you can trap for any errors and handle them gracefully instead of simply letting your application hang as it would with a macro.

Monday, October 20, 2008

New Sample: FormParametersForRecordsets

FormParametersForRecordsets

Author: A. D. Tejpal

SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string.

Concatenation of hard values into SQL string suffers from the following drawbacks:
  • (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data).
  • (b) If text data has embedded quotes, these need to be fixed before concatenation.
  • (c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format.
  • (d) Care needed in handling Null values.

The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=437

Tuesday, October 14, 2008

New Sample: ModulesAddIn_BulkImportFromFiles

ModulesAddIn_BulkImportFromFiles

Author: A. D. Tejpal

The ModulesAddIn_BulkImportFromFiles for MS Access is a utility that enables bulk import of .bas or .txt files as general modules.

You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=434

Saturday, October 11, 2008

New Sample: Query_RowNumbersAndSumLargeData

Query_RowNumbersAndSumLargeData
Author: A.D. Tejpal

Subqueries (or user defined functions based upon recordsets) for calculation and display of row numbers and running sum, are found to be slow in case of large data sets.

Use of increments to global variables offers a faster alternative. This method is preferably used through an action query, as calculated values directly displayed via select query tend to be volatile (the results keep on changing as one navigates up and down the records.

Two alternative methods based upon incrementing global variables are demonstrated as follows. In each case, two styles are covered, namely (a) Straight simple sequence and (b) Group-wise sequence. If it is simple sequence, the user defined functions have provision for specifying the starting number or sum - if desired:
1 - Row number and running sum - via update action.
2 - Row number and running sum - via make table action.

For academic interest, depiction of row numbers through generation of autonumbers has also been demonstrated as follows (this approach is however not as fast as that involving increments to global variables):
3 - Row number - via append action..
4 - Row number - via make table action.

Note:
(a) Use of incrementing global variables (items 1 and 2 above), apart from being the fastest approach, has the added benefit that it is not dependent upon availability of primary key.
(b) Four user defined functions utilized in implementing this method are given in the general module.
(c) Use of these functions in WHERE clause of pertinent queries is meant to initiate the sequence in desired manner.
(d) If filter has been applied to displayed data, generation of row number and running sum gets implemented on the filtered data, as demonstrated in the sample db.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=433&PID=429#429

Thursday, October 9, 2008

* Index to Access 101 *

Access Basics
Compacting Databases
Domain Functions Demystified
Date Stuff
 Database Design Basics
What’s Wrong With Repeated Columns?
Normalizing Repeating Columns

Queries
Select Queries Series:
Data Definition Language (DDL) Queries:

Top Queries Revealed:
Count Distinct In Access Series:
Miscellaneous
Reports
VBA/Programming
Web Databases

  • The Application

Monday, October 6, 2008

Featured Sample: Crosstab Query in Flex Grid

Crosstab Query in Flex Grid

Author: Peter Hibbs

This demo program shows how to display a Crosstab query in a Flex Grid control on a form. The problem with displaying Crosstab queries on a form (say in a ListBox control) is that some queries have a variable number of columns as well as rows. Using a Flex Grid control allows you to set the number of columns at run time as well as giving all the other facilities that you get with Flex Grid controls such as colouring cells, fixing columns or rows, etc, etc.

See Peter's other demo for more information on Flex Grid commands.

This demo works with Access 2000 onwards.

Find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429

Friday, October 3, 2008

Access is Icky and Gross

A few days ago, I asked a colleague of mine, a SQL Server DBA, for some advice with the SQL Server Bulk Insert command. He gave me some tips, and I said I'd try them first with SSMS (SQL Server Management Studio) and then as an Access Pass-through query. (This is part of an Access application I am developing.) This was his response to me:

"Please keep Access out of this. Native via SQL tools only. No pass through. Sorry, but it's icky and gross in this case."

Of course, this is simply prejudice and ignorance on his part. Properly created, an Access application works perfectly well against a SQL Server back-end database. And running an Access Pass-through query is no different than executing a query in SSMS or the older Query Analyzer. Microsoft spent quite a lot of time and effort to make sure this is true.

But, I can't honestly say I'm all that surprised. I've encountered this attitude before, often from Oracle DBAs, but surprisingly also from SQL Server DBAs. And this got me to wondering where this antipathy comes from.

I think it comes from two sources.

First of all, creating an application for SQL Server in the same way you create one for Jet, can indeed get you into trouble. Filling a bound form with an entire dataset and filtering the result (as less experienced Access developers tend to do), can lead to very slow performance. In a multi-user environment, bound forms can place a burden on the database server by holding open too many connections and record locks.

Fortunately, there are methods around these and other problems. It has nothing to do with Access, but the way in which the application is created. As with anything, different problems require different solutions, and using the wrong solution causes problems.

But the second reason for this antipathy goes deeper than the technical reasons. I think there is a natural tendency for people who use a more powerful/complex product to dismiss those who use one that is easier to learn/use. C++ developers look down on VB developers who look down on Access developers.

This is unfortunate and really does nothing positive for anybody. It's time to stop sniping at each other and treat each other as professionals, each competent in their own areas.

Tuesday, September 30, 2008

Featured Sample - Form_SearchByMultipleListBoxes

Form_SearchByMultipleListBoxes

author: A. D. Tejpal

This sample db demonstrates application of search criteria based upon selections in three multi-select list boxes. For a given list box, the search criteria is deemed True if the field value happens to match any of the selected items. Overall criteria is determined by application of And operator between the outcomes pertaining to different list boxes.

For sake of illustration, three different data types are covered by the three list boxes in this sample, namely date, text and number. In case of text type, handling of embedded quotes if any, within the field values, has also been demonstrated.

Three different styles of displaying the contents of list boxes have been shown as follows:
(a) No cascading - The full list is always under display.
(b) Simple cascading (hierarchical) - LstDate controls the contents of LstType and LstSupp, while LstType controls the contents of LstSupp.
(c) FreeStyle cascade - Whichever list box happens to be updated, causes filtering of contents of other two list boxes accordingly.

For user's convenience, facility for selecting or de-selecting the whole list box has been provided.
Three different styles of applying the search criteria are demonstrated for each style of cascading:
(a) Permanent saved query with embedded criteria clause.
(b) SQL built at run time.
(c) Filter string applied at run time.

Selection results for each list box are stored in corresponding unbound text boxes as comma separated strings. For text type data, each element of such a string is enclosed within outer single quotes, after having replaced each embedded single quote if any, by double single quotes (As the outer enclosing character is single quote, embedded double quotes if any, don't require any special treatment). For date type data, each element of such a string is enclosed within outer hashes.

This arrangement eliminates the need for repeated cycling through the list boxes while testing the field values. The value is simply checked against the contents of corresponding unbound text box, using IN operator. In case of permanently saved query with embedded criteria clause, EVal() function is used for facilitating interpretation of superstring having IN operator.

Version: Access 2000 File Format

Find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=403

Thursday, September 18, 2008

New Sample: A.D. Tejpal's ExportToExcelNamedMultiSheets

ExportToExcelNamedMultiSheets

Author: A.D. Tejpal

Description: This sample db demonstrates a drastically simplified method for exporting multiple access tables/queries to custom named worksheets in the given excel workbook. No automation code is needed.

Find out more here: http://rogersaccesslibrary.com/forum/forum_posts.asp?TID=432

Wednesday, September 17, 2008

What is Microsoft Access?

Microsoft Access® is the most popular database product on the planet. People argue about what this means exactly, but it is clear that more people use Access (the actual product) than any other database management system.

But many people don't realize that Access is not just one product, but a suite of tools integrated into one product. In other databases, these tools are separate programs, some of which must be purchased from a third-party. Together, these tools make Access one of the most powerful database products in existence.

Now, some may scoff at this. They would say that Access is just a tinker toy compared to a "real" database like Oracle or SQL Server. But that is just ignorance speaking. It is true that the database component of Access (the Jet database engine) is limited in terms of size, speed, and number of users. However, the Jet engine is only one part (and not the most important part, in my opinion) of Access.

So let's look at the component parts of Access:

First of all, as I already mentioned, there is the Jet database engine. In fact, there are two different versions of Jet. There is the traditional version (mdb) which has been around since version 2.0. With Access 2007, there is a new version, the Access Database Engine (accdb). Although it has limitations, it is nonetheless a fully functional relational database management system.

Secondly, there is the Access User Interface (AUI). This is the part of Access that the user sees when they open a table. With the AUI, a user can create tables, modify tables, add data, modify data, and create filters to view data. In SQL Server, this functionality is done in the SQL Server Management Studio (SSMS) or the older Enterprise Manager. In fact, the original Enterprise Manager was patterned after the Access UI.

Third, there is the Query Builder. This is an ad hoc querying tool. Queries can be built in the graphical user interface (gui) or directly in standard SQL. Access does not have "Views" like SQL Server does, but it does have "Select queries" that server the same function. In addition, Access has Data Definition Language (DDL) queries that create and modify table structure, Data Manipulation Language (DML) queries that display or modify data. In SQL Server, you would need a tool like Query Analyser or SSMS to accomplish this.

Another type of query Access uses is the Pass-through Query. A pass-throrgh query bypasses the Jet engine altogether and passes the query on to a linked source like SQL Server. The versatility of Access connet to and manipulate data from various data sources is truly astounding.

Fourth, Access has a built-in application generator. This itself consists of several parts.
  1. Forms builder. Access allows you to create form in much the same way as Visual Basic. However, Access forms are much easier to create and the controls available are specific to database development.
  2. Report builder. At some point you have to report the data in your database. In Access, you do this in the report builder. In SQL Server, you would have to use a separate product like SQL Server Reporting Services or Crystal Reports depending on how you want them published.
  3. VBA modules. All database applications require a procedural language at some point. There are just some things that can't be done in SQL. In Access, this capability is provide in Visual Basic for Applications modules, both general (global) modules and programming behind the forms and reports (class modules).

Creating an application in another database, SQL Server for instance, would require multiple products: SQL Server as the data engine, SSMS to create and modify tables, Query Builder to run ad hoc queries, Visual Basic to create the application, and Crystal Reports for reporting. In Access, you get it all in one.

So you can see that Access is really a Rapid Application Development (RAD) product than it is a database. In fact, Access might more accurately be called an application generator with a complementary database provided.