Returning multiple rows from a SQL generated record set as a concatenated string value using SQL in Microsoft Access

System Requirements:

  • Microsoft Access 97, 2000, 2002 (XP), 2003, 2007

The Problem:

If you have a one to many relationship in your database and you want to query the following (rudimentary and fairly poor) information:

table_Year

ID YearCode
1 2008
2 2009
3 2010

table_Semster

YearCode Semester
2008 A
2009 A
2009 B
2010 A
2010 B
2010 C

The query of:
SELECT table_Semester.YearCode, table_Semester.Semester FROM table_Semester WHERE table_Semester.YearCode = table_Year.YearCode AND table_Year.ID = 2;

Returns:

YearCode Semester
2009 A
B

But what if you (for some reason, the why here is your own business) want to return:

YearCode Semester
2009 A,B

or even just

2009 AB

 

More Information:

Larger database management systems such as MySQL, Oracle and Microsoft SQL Server can utilise built-in functions, stored procedures or cursor based logic to create a string from multiple rows, however Access is not powerful enough to support this.

However, it is possible (although not necessarily ideal) to retrieve a single string value from a one-to-many record set in access under one condition:

It only works IF there is a finite and definable number of “many” values.

Take the example above. An academic year has three semesters. Semester A, B and C. We have three values which we can enumerate as being true. As a consequence we can create a concatenated string output using a SQL SELECT statement similar to the following:

SELECT DISTINCT (

SELECT tblYear.YearCode, table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’A’)

) & (

SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’B’)

) & (

SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’C’)

) AS strSemester
FROM table_Year tblYear
WHERE tblYear.ID = 1 ;

What we are doing here is defining that there are three known options, searching for each option against the record sets join. Note the redefinition of table_Year as tblYear.

Each sub query returns a single permissible value from the many relationship and concatenates it to a string named “strSemester” (using the & character). The result of executing the query above would be:

YearCode Semester
2009 AB

If you wanted comma’s to separate the Semesters you would have to use

) & “, ” & (

This on its own would of course return excess commas on the example above, i.e.

YearCode Semester
2009 A,B,

Now, I can hear database administrators across the planet groaning right now, and yes I concur… but like I said above, the “why” that prompted you to do this is your own business. So I have no qualms in making this worse than it already is.

If you wanted to return the above without the stray comma’s at the end you would have to augment the SQL statement thusly:

SELECT DISTINCT (

SELECT tblYear.YearCode, table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’A’)

) & (SELECT “, ” as strComma FROM Faculty WHERE (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’A’)) <> NULL;
) & (

SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’B’)

) & (SELECT “, ” as strComma FROM Faculty WHERE (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’C’)) <> NULL;
) & (

SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’C’)

) AS strSemester
FROM table_Year tblYear
WHERE tblYear.ID = 1 ;

The horror of the above isn’t as complicated as it looks. We test for Semester A where the ID = 1 and send the result OR Null to the string. We then test it again, but this time we only send a result – the comma – back when the result is not Null, that means that if there is an instance of A the result is written out as:

A,

Repeat for B, but this time only if there is an instance of C the result is written out as:

B,

Giving us a not quite flawless answer (more tests are needed to cover every combination).For the example however the result would be:

YearCode Semester
2009 A,B

With no trailing comma!

“Server Application Error” error message when attempting to run ASP page under IIS 5.0

System Requirements:

  • Windows 2000 & IIS 5.0

The Problem:

Yet another hpcfactor.com server failure later and we have some new problems. When attempting to parse asp pages, the browser retorts with an error headline “Server Application Error” and populates the event log with Event ID 36:

The server failed to load application ‘/LM/W3SVC/1/Root’. The error was ‘No such interface supported’

Note that the 1 in /LM/W3SVC/#/Root can be any number representing the identifier for any virtual web on your server e.g. ‘/LM/W3SVC/2/Root’, ‘/LM/W3SVC/3/Root’, ‘/LM/W3SVC/23/Root’.

HTML pages will display correctly, however anything that is running through the ASP interpreter will fall foul of this error message.

An additional symptom that you may have noticed is that the error will only occur if your Application Protection settings in IIS 5.0 are set to Medium or High.

More Info:

In the case of hpcfactor.com the problem began quite innocently with Teoma (Ask Jeeves’ crawler) starting a run over the site root, and suddenly stopping seemingly randomly when the server kicked back a 500 error:

Out-of-process ISAPI extension request failed

From that single request on, for another 6.5 hours before an administrator noticed every single request to the ASP interpreter on the particular server returned another 500 error:

Out-of-process ISAPI extension has exceeded crash limit.

Rendered in MSIE as:

The server has reached the maximum recovery limit for the application during the processing of your request. Please contact the server administrator for assistance

In playing around with the server for a little while, restoring a metabase backup and repairing some lost NLS files, we were able to get the server to perform again without the ISAPI errors, however the server would not output any ASP, returning “Server Application Error” every time and for each page request writing Event ID 36 to the System log.

I quickly discovered that if I lowered the Application Protection settings from Medium, to Low, the site would correctly load ASP pages, however there is an inherent problem in doing this – it does not lead to good stability; in fact you are essentially running IIS 5.0 as if it were IIS 3.0.

Low (IIS Process): This is the way IIS ran prior to IIS 4; all requests are performed in the InetInfo.exe process. This is the fastest option, but it is also the least robust because a rogue application could crash the InetInfo.exe process.

Medium (Pooled): This is a new option in IIS 5 and is the default setting for all new Web applications. In this model, all parts of the Web space marked as Medium (Pooled) run in the same process external to InetInfo.exe. As already mentioned, the process name is DllHost.exe and this process does not run as LocalSystem as InetInfo.exe does. Instead, DllHost.exe runs under the identity of an account controlled by IIS. By default, the identity is IWAM_machinename.

High (Isolated): This option, introduced in IIS 4 and shown in Figure 5-33, executes each Web .application in its own instance of DllHost.exe that runs in the context of the IWAM_machinename account. In doing so, it offers the highest degree of robustness, but it’s not as fast as Medium.

Source: MS Press Book Sample

In case you have read anyone else’s ideas over doing this – this is NOT a solution, it’s madness unless you are hosting your own little photo album for Billy Bob and the rest of the clan.

The common link between the higher process modes is obviously the IWAM_machinename account on the system, there is some excellent advice around the Internet on troubleshooting it already, and unless the next line matches your case, I suggest for follow it – start by re-synching the IWAM account using the AdminScript for IIS 5.0.

The next line: Check to see whether IIS is still registered in the COM+ registration database.

MMC the console applet found at %SystemRoot%\system32\Com\comexp.msc

If you see this, troubleshoot IWAM
ASP COM+ Registered
If you see this, you know why ASP doesn’t work (It should look like the image above)
ASP COM+ Failure

Effectively it is not possible to utilise Application Protection without the COM+ registrations. To fix them:

  1. Backup your server
  2. Backup the IIS 5.0 metabase
  3. Stop IIS Admin and associated services
  4. Pull up a command prompt
  5. Enter (Case sensitive):
    rundll32 %windir%\system32\inetsrv\wamreg.dll, CreateIISPackage
  6. Hit OK
  7. Enter:
    regsvr32 asptxn.dll
  8. Hit OK
  9. Restart IIS Admin and associated services
  10. Return to the comexp.msc view and check whether the three IIS registrations have returned, if they have start returning your web sites to Medium (Pooled). You will need to restart IIS in order for the Application Protection change to take effect.

RFC1766 International Language Identification Downloadable Stores

System Requirements:

  • A Language Code Problem
  • Text/CSV Viewer, Excel 2003, Access 2003

The Problem:

If you have ever been mildly curious in the language ID listing, or ever engaged in a project where language identification was important having to come up with a digital list of the language can be a time consuming business.

This article simply saves you the time, having had me spend the time compile the list for you!

The Fix:

If you need the language list, you can use my files below as a base for your project.

The data source has been taken from Microsoft Windows XP’s registry, so any errors are universally Microsoft’s problem if indeed there are any.

I have made the following three files available:

  • Comma Separated Variable (CSV) Text File
  • Office Excel 2003 Spreadsheet
  • Office Access 2003 Database

The CSV contains:

  1. Hex Code
  2. Language Code
  3. Language Description

The Spreadsheet and MDB Database contain:

  1. Hex Code (Key)
  2. Decimal Code
  3. Octal Code
  4. Language Code
  5. Language Description

 

Download: CSV, Excel 2003 and Access 2003 (22KB)

You may also be interested in my stores for ISO 3166-1 Country Codes.

Error: Activex componet can not create object: “WScript.Shell” when running WScript application

System Requirements:

  • Windows 95, 98, 98SE, Millennium
  • Windows NT 4.0, 2000, XP, 2003, Vista

The Problem:

When you run a .vbs file with a call to CreateObject(“WScript.Shell”) the script/application terminates with the follow error message:

ActiveX componet can not create object:”WScript.Shell”
Code: 800A01AD

The script then exits

More Information:

Your Windows Scripting Host has a mis-registered control. If you have just Installed Microsoft Internet Explorer <anything>.<anything> chances are the install went wrong. Check the install log in %SystemRoot% and check for failures.

I would recommend reinstalling IE properly to be safe, but chances are the problem will still be there afterward (it was with this particular muse and MSIE 7.0).

The fix is very simple however (assuming that your error is down to Windows Scripting Host and not bad programming; you are on your own on that one).

From cmd, or from a .bat run:

regsvr32 dispex.dll
regsvr32 jscript.dll
regsvr32 scrobj.dll
regsvr32 scrrun.dll
regsvr32 vbscript.dll
regsvr32 wshcon.dll
regsvr32 wshext.dll
regsvr32 wshom.ocx

I recommend that you start with wshom.ocx and test . No reboot is required for shell initiated .vbs files. If the problem is with IIS, you should restart the IIS Administrative and WWW services.

Be sure that you are using WScipt 5.6 (Unless running Vista or XP with IE7). You can download the latest release here: