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.

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


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:

Monday, November 17, 2008

Featured Sample: 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:

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.

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

You can find the sample here:

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:

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.