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!

Dreamweaver 8.0.2 menu options are grayed out from the view and insert menu when editing ASP or PHP files in either code, design or split view and the spell checker option is disabled

System Requirements:

  • Macromedia Dreamweaver 8.0.2

The Problem:

When you are editing page content in Macromedia Dreamweaver 8.0.2 in an ASP file you are unable to chose many of the menu options from the insert or view menu, such as Server-side Includes, form’s and form objects irrespective of whether you are in code, split or design view.

Changing the view doesn’t influence the situation.

More Information:

This seems to be more of a bug in Dreamweaver 8.0.2 than an intended characteristic.

Of the information that I have seen published on-line, the suggested solution is to just change views, but this doesn’t fix it, at least not when I experienced the problem on a Windows XP Professional SP3 install. It was working fine in the same configuration on a Windows 2000 Professional SP4 install.

Steps to try

  1. Change view : View > Code / Design > Code and Design
  2. Rollback any custom extension installs and re-test
  3. Repair install Dreamweaver by going into Add or Remove programs in the control panel (Programs and Features under Vista / 7), select change and then hit repair
  4. Rename the file as a .html file. For example if your file is a default.asp file rename it to default.html and test in the editor whether the fault is coming from the parser for ASP/PHP etc
  5. This is the one that sorted it for me:
    1. Edit > Preferences > New Document
    2. Check to see what the default document type and DTD being specified are. In my case, these had been changes to set .asp and ASP VB Script as the defaults from the standard ones as specified below.
      Dreamweaver 8.0.2 Default Preferences
      For some reason, if you change these defaults, the behaviour of the editor changes and you will no longer be able to utilise all of the options from the insert menu. In my case, simply putting them back and restarting Dreamweaver re-enabled all of the content that was missing. Bug? Yes, I think so.

ADODB.Connection error ‘800a0e7a’ – Provider cannot be found. It may not be properly installed

System Requirements:

  • Windows NT 4.0
  • Windows 2000
  • Windows XP (x86)
  • Windows Server 2003 (x86)
  • Windows Server 2008 (x86)
  • Windows 7 (x86)
  • Windows Server 2003 (x64)
  • Windows Vista (x64)
  • Windows 7 (x64)
  • Windows Server 2008 (x64)
  • Windows Server 2008 R2 (x64)
  • Windows Server 2012 (x64)

The Problem:

When you attempt to connect to a Microsoft Access database using the ODBC provider Microsoft.Jet.OLEDB.4.0 you receive the error message:

ADODB.Connection error ‘800a0e7a’
Provider cannot be found. It may not be properly installed./path/file.asp, line ##

 

More Information:

For x64 Systems:

The Microsoft.Jet.OLEDB.4.0 is only available as a 32-bit (x86) module. In order to use it via IIS you must change the web site to run in a 32-bit application pool.

Under IIS 7.0, 7.5 or 8.0:

  • Assuming that you will not want to use ASP .net, in the IIS console in the Application Pools section, create a no managed code, integrated application pool. If you do want to maintain access to ASP .net, select the framework version you require.
  • Once created, in its advanced settings set “Enable 32-bit Applications” to true
  • In the Basic Settings… for the web site entry you are experiencing the error on, change the Application Pool to the newly created 32-bit pool.
  • Restart the web site

In most cases where the system is running under an x64 architecture, this should fix the problem.

For x86 and x64 Systems:

We had a server down for an entire day because of this one. It was either an attempt to fix a security database fault, or an update from Microsoft Update delivered in the April 2008 patch cycle, but after the reboot for the patches, none of the JET (Access) databases on the particular server, in any IIS web would operate.

I will simply cut to the fix for you in the beginning, to expedite your likely finding of a resolution to this problem. Something had caused the service provider for the Microsoft.Jet.OLEDB.4.0 provider to become unregistered. To fix the problem issue the following command from a Command Prompt or Run:

regsvr32.exe msjetoledb40.dll

You shouldn’t need to reboot to solve the problem, it didn’t in my case. However, if nothing happens do restart before progressing to the next step.

 

Additional Steps: Step 1

Reregister the components from MDAC, JET and ADO in their entirety. Simply copy the below into a BAT file and run it.

:: ADODB.Connection error '800a0e7a'

:: Provider cannot be found. It may not be properly installed.regsvr32.exe cfgmgr32.dll -s

regsvr32.exe cliconfg.dll -s

regsvr32.exe dasetupr.dll -s

regsvr32.exe dbmsgnet.dll -s

regsvr32.exe dbmsrpcn.dll -s

regsvr32.exe dbnetlib.dll -s

regsvr32.exe dbnmpntw.dll -s

regsvr32.exe expsrv.dll -s

regsvr32.exe msadce.dll -s

regsvr32.exe msadcer.dll -s

regsvr32.exe msadcf.dll -s

regsvr32.exe msadcfr.dll -s

regsvr32.exe msadco.dll -s

regsvr32.exe msadcor.dll -s

regsvr32.exe msadcs.dll -s

regsvr32.exe msadds.dll -s

regsvr32.exe msaddsr.dll -s

regsvr32.exe msader15.dll -s

regsvr32.exe msado15.dll -s

regsvr32.exe msadomd.dll -s

regsvr32.exe msador15.dll -s

regsvr32.exe msADOX.dll -s

regsvr32.exe msadrh15.dll -s

regsvr32.exe mscpxl32.dll -s

regsvr32.exe msdadc.dll -s

regsvr32.exe msdadiag.dll -s

regsvr32.exe msdaenum.dll -s

regsvr32.exe msdaer.dll -s

regsvr32.exe msdaora.dll -s

regsvr32.exe msdaorar.dll -s

regsvr32.exe MSDAOSP.dll -s

regsvr32.exe msdaprsr.dll -s

regsvr32.exe msdaprst.dll -s

regsvr32.exe msdaps.dll -s

regsvr32.exe msdarem.dll -s

regsvr32.exe msdaremr.dll -s

regsvr32.exe msdart.dll -s

regsvr32.exe msdasc.dll -s

regsvr32.exe msdasql.dll -s

regsvr32.exe msdasqlr.dll -s

regsvr32.exe msdatl3.dll -s

regsvr32.exe msdatt.dll -s

regsvr32.exe msdaurl.dll -s

regsvr32.exe msdfmap.dll -s

regsvr32.exe msjro.dll -s

regsvr32.exe msjtes40.dll -s

regsvr32.exe msorc32r.dll -s

regsvr32.exe msorcl32.dll -s

regsvr32.exe msvcirt.dll -s

regsvr32.exe msvcrt.dll -s

regsvr32.exe mswdat10.dll -s

regsvr32.exe mswstr10.dll -s

regsvr32.exe msxactps.dll -s

regsvr32.exe msxml3.dll -s

regsvr32.exe msxml3a.dll -s

regsvr32.exe msxml3r.dll -s

regsvr32.exe mtxdm.dll -s

regsvr32.exe mtxoci.dll -s

regsvr32.exe odbc16gt.dll -s

regsvr32.exe ODBC32.dll -s

regsvr32.exe ODBC32GT.dll -s

regsvr32.exe odbcbcp.dll -s

regsvr32.exe odbcconf.dll -s

regsvr32.exe ODBCCP32.dll -s

regsvr32.exe ODBCCR32.dll -s

regsvr32.exe ODBCCU32.dll -s

regsvr32.exe ODBCINT.dll -s

regsvr32.exe ODBCTRAC.dll -s

regsvr32.exe oledb32.dll -s

regsvr32.exe oledb32a.dll -s

regsvr32.exe oledb32r.dll -s

regsvr32.exe setupapi.dll -s

regsvr32.exe sqloledb.dll -s

regsvr32.exe sqlsrv32.dll -s

regsvr32.exe sqlunirl.dll -s

regsvr32.exe sqlxmlx.dll -s

regsvr32.exe vbajet32.dll -s:: OLEdB

regsvr32.exe %SystemRoot%\System32\msjetoledb40.dll:: Jet

regsvr32.exe %SystemDrive%\Program Files\Common Files\Microsoft Shared\DAOdao360.dll -s

regsvr32.exe %SystemRoot%\System32\expsrv.dll -s

regsvr32.exe %SystemRoot%\System32\msexch40.dll -s

regsvr32.exe %SystemRoot%\System32\msexcl40.dll -s

regsvr32.exe %SystemRoot%\System32\jet500.dll -s

regsvr32.exe %SystemRoot%\System32\msjet35.dll -s

regsvr32.exe %SystemRoot%\System32\msjet40.dll -s

regsvr32.exe %SystemRoot%\System32\msjetol1.dll -s

regsvr32.exe %SystemRoot%\System32\msjint40.dll -s

regsvr32.exe %SystemRoot%\System32\msjter40.dll -s

regsvr32.exe %SystemRoot%\System32\msjtes40.dll -s

regsvr32.exe %SystemRoot%\System32\msltus40.dll -s

regsvr32.exe %SystemRoot%\System32\mspbde40.dll -s

regsvr32.exe %SystemRoot%\System32\msrd2x40.dll -s

regsvr32.exe %SystemRoot%\System32\msrd3x40.dll -s

regsvr32.exe %SystemRoot%\System32\msrepl40.dll -s

regsvr32.exe %SystemRoot%\System32\mstext40.dll -s

regsvr32.exe %SystemRoot%\System32\mswdat10.dll -s

regsvr32.exe %SystemRoot%\System32\mswstr10.dll -s

regsvr32.exe %SystemRoot%\System32\msxbde40.dll -s

regsvr32.exe %SystemRoot%\System32\spmsg.dll -s

regsvr32.exe %SystemRoot%\System32\vbajet32.dll -s

:: ADO
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msader15.dll -s
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msado15.dll -s
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msadomd.dll -s
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msador15.dll -s
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msadox.dll -s
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msadrh15.dll -s
regsvr32.exe %SystemDrive%\Program Files\Common Files\System\ado\msjro.dll -s

Additional Steps: Step 2

Reinstall MDAC 2.8 SP1 (Windows XP SP1 or below only). You will need to perform some registry modifications before you can reinstall MDAC as it is a protected system module.

  1. Open Regedit
  2. Navigate to:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Setup\ExceptionComponents
  3. Look through each class ID for the following friendly name values. Delete the Class ID containing the matching friendly names:
    Microsoft MDAC Response Files
    Microsoft MDAC Setup Files
    Microsoft SQL Server ODBC Drivers
    Microsoft SQL Server Net Libs
    Microsoft SQL Server OLEDB Provider
    WebData Setup Exception Package
    Mdac 2.8 Exception Package
  4. Download and reinstall MDAC 2.8 SP1 from Microsoft which should reinstall in full.

C:Amie’s StopWatch for Windows CE and Handheld PC

System Requirements:

  • Windows CE 2.11, 2.12, 3.0, 4.0, 4.1, 4.2
  • Handheld PC Professional
  • Handheld PC 2000 (HPC2000)
  • VBCE Runtime

About:

StopWatch does more or less exactly what it says on the tin. It is a timer that you can stop… and start… and clear.

The program is designed not to hook the RTC in calculating the time, what is meant by that is that the application does not take note of, nor care about when precisely it was that you started the timer as is the case with some of the other timer applications available for CE.

Why does this matter?

Well, with a timer that hooks the RTC values, when you suspend the device and resume it after x amount of time, the application will re-value the current time against the start time and adjust the value accordingly. This program was designed to maintain counting arithmetic each side of the suspend/resume application.

Why would I want this?

Well, you might not, however if you are looking for this, I am sure that you have a good reason for it. The reason why I wrote the application in this was was because I wanted a visual indicator to time battery depletion from max charge to the point at which CE’s power management forcefully suspends the hardware. Once suspended, when you connect it back to AC ad resume the system, the value on the screen will be the value that the system powered off at, rather than a recalculated value between then and now.

Do however note that the time will continue ticking on resume.

C:Amie's StopWatch

Current Version:

1.0.3

License:

Freeware

Price:

Free

Source Availability:

Closed

StopWatch makes no registry modifications and writes no data into the file system of your Handheld PC (the VBCE RTL does make additions to the registry), it is low footprint, transportable and can be run from main RAM or a CF Storage Card.

Changelog:

The table below contains a list of changes made to StopWatch.

Version Number Changes

1.0.3

  • C:Amie branding added
  • Clear button logic changed so that it no longer stops the timer if it is already running
  • Hyperlink support for CE .net 4.x to www.c-amie.co.uk added
  • Automated interface version numbering added

1.0.2

  • Canvas size reduced
  • Timer Font Size Expanded
  • Hyperlink for HPC2000 systems to www.c-amie.co.uk added

1.0.1

  • Added toggelable Start/Stop button
  • Clear Button Added

1.0.0

  • Initial Release for Thaddeus

Installation:

The following steps outline the installation procedure for the StopWatch.

Visual Basic CE Edition

  1. Download either the VB application from below
  2. Download the VBCE runtime from H/PC:Update (if required)
  3. Upload the VBCE runtime installer to the device and install by double clicking the cab file and following the instructions on the screen
  4. Soft Reset the Handheld PC
  5. Upload the application file (.vb file) to the Handheld PC
  6. Double-tap the .vb to launch the program

.net Compact Framework Edition

  1. Download the .net CF application from below
  2. Download the .net Compact Framework runtime combination from H/PC:Update (if required)
  3. Install the .net Compact Framework using ActiveSync to your Handheld PC
  4. Soft Reset the Handheld PC
  5. Upload the application file ( .exe file) to the Handheld PC
  6. Double-tap the .exe to launch the program

Download:

This application is closed source, its use is entirely down to you the end use, no warranty is implied or offered.

Requirements

StopWatch for CE 2.10+: You will need to have the VBCE Runtime libraries installed (CE2.11+) on your device. The Minimal version of the VBCE runtime from H/PC:Update (link below) will enable operation under CE .net. HPC Pro and HPC2000 users already have the RTL in ROM.

StopWatch.net for CE .net 4.20+: You will need the .net Compact Framework 2.0 SP2 installed. The .net Compact Framework can also be downloaded from H/PC:Update.

Download: C:Amie’s StopWatch 1.0.3 (VB Application only) (5 KB)

Download: C:Amie’s StopWatch 1.0.3 alpha (.net Compact Framework 2.0 Application) (8 KB)

View: H/PC:Update on HPC:Factor