Microsoft JET Database Engine error ‘80040e21’ – Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

System Requirements:

  • Windows NT 4.0
  • Windows 2000
  • Windows XP
  • Windows Server 2003
  • Windows Vista
  • Windows Server 2008
  • Windows 7

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:

Microsoft JET Database Engine error ‘80040e21’
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done./path/file.asp, line ##

 

More Information:

There are a number of issues that can cause this error, they are poorly documented elsewhere online but the information is there; connection string, registry values slightly offset and so fourth. This document deals with one specific issue which is related to the idea of this being caused by an incompatible type value.

The chances of you running into this are quite extreme, however it is an issue in JET (Microsoft Access) that doesn’t exist in Microsoft SQL Server and thus if you are dealing with code transitioning you may want to know about the problem in this very extreme and unlikely situation.

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.

SQL Example

The following SQL Statement will execute on Microsoft SQL Server and return a RecordSet however the same SQL on JET 4.0 will result in the error message outlined above.

SELECT
Student.StudentID,
Student.FirstName,
Student.LastName,
Student.EMail,
null AS studentPhotoBLOB,
Left(StudentID,1) AS YearPrefix
FROM Student;

No, it isn’t the T-SQL LEFT() statement that is the problem. The issue lies in the “null AS studentPhotoBLOB”.

The idea behind this is that space has been reserved in the record set. Why would you want to do this? If you want to drop the record set directly into an array, then ADO can do this for you using the GetRecords method. However if you want to change the structure you have to re-parse the array and relocate the data into a new structure. You can inset a null named record and use the space in the GetRecords array later for other purposes.

The problem here is that null is not a data type that is supported by JET; it is supported by Microsoft SQL Server however.

The simplest way to remedy the situation is to either change the SQL or dynamically detect JET to change the variable type to a commonly supported data type, for example a String.

SELECT
Student.StudentID,
Student.FirstName,
Student.LastName,
Student.EMail,
” AS studentPhotoBLOB,
Left(StudentID,1) AS YearPrefix
FROM Student;

This is naughty, however in a weak typed language such as ASP it isn’t a real problem given the situation outlined above. The use of ” instead of null forces the data type of the column into a string and thus it can be parsed by JET.

Warning: Asus P4C800-E Deluxe and using modern Power Supply Units (PSU) with a motherboard earlier than 2005/2006 (Intel Socket 478)

System Requirements:

  • Old motherboard (specifically in my case a Intel Socket 478 motherboard)
  • Asus P4C800-E

The Problem:

So you have a trusty old 478 system that you cant bare to part with, but the PSU has just blow out on it and you need a new one. You know that a good PSU is worth the investment, you get a brand name, a high wattage value and something with a high efficiency value.

You get home, you wire it up and… nothing.

The machine turns on, it may post, it may just hold up on a black screen before the POST. The latter is what happened with my Asus P4C800-E Deluxe when I came to replace the PSU with a £70 Antec TruePower PT-550. It would not start, however if I swapped back a cheap PSU that I borrowed as a stop gap, it would POST fine.

More Information:

Another 15 mile one way trip back to the store with the Antec and we stood around scratching our heads over this one for a few minutes.

It turns out that all (decent) modern PSU’s have a design change which makes them incompatible with boards that require -5v – and probably quite a few others.

The Asus P4C800-E Deluxe requires a -5v rail on the ATX in order to get going. In every brand name PSU box we opened in the store – including the one I was returning – the -5v rain pin was missing from the 20-pin ATX connector.

Net result: the system cannot boot.

Why are PSU’s shipping without this?

Some post-event research leads me to understand that with the release of the ATX12V specification version 1.2 in January 2002, the -5v rail became optional on the ATX specification. With revision 1.3 in April 2003 and the 2.x revision the -5v rail was banned altogether. Look at any modern PSU and you’ll find that pin 20 in the ATX connector is blanked out.

So if you are looking for a decent PSU for a Intel 478 system, you may have a problem. In the end I was forced to get one of those cheap PSU’s that case manufacturers supply with cases – which conveniently has a -5v rail but is unremarkable in every other way aside from probably its inefficiency.

Error: “The procedure entry point UnmapAndCloseFile could not be located in the dynamic link library SETUPAPI.dll” when attempting to run RIPrep.exe on Windows XP or Windows Server 2003

System Requirements:

  • Windows XP Professional, SP2, SP3
  • Windows Server 2003 SP1, SP2

The Problem:

When you attempt to run the Remote Installation Service (RIS) image preparation tool (RiPrep.exe) on a Windows XP or Windows Server 2003 system with the latest service pack you receive the following error message.

riprep.exe – Entry Point Not Found
The procedure entry point UnmapAndCloseFile could not be located in the dynamic link library SETUPAPI.dll

Error: "The procedure entry point UnmapAndCloseFile could not be located in the dynamic link library SETUPAPI.dll"

This error is generated despite having the latest build of RIPrep.exe from Windows 2000 Server SP4 – 5.0.2195.6685 at the time of writing. If you do not have this build of RIPrep you should obtain the current version from Service Pack 4 and update your RIS host to SP4 before continuing.

More Information:

Microsoft’s XP SP2 secure code initiative and several years worth of code development has seen some fundamental changes in the standard setup api used on XP and Server 2003 systems. The problem is caused quite obviously by the lack of the entry point UnmapAndCloseFile in the setupapi.dll.

The Fix

Very simply, you need to give riprep.exe access to a version of the dll with that entry point.

  1. Go to your Windows 2000 Remote Installation Server (ensure that it is SP4)
  2. Navigate to %SystemRoot%\System32
  3. Copy the file setupapi.dll into the <RISPath>\Admin\i386 folder
    e.g. E:\RemoteInstall\Admin\i386
  4. setupapi.dll from Windows 2000 SP4 (version 5.0.2195.6622 as of writing) will now be next to the riprep.exe
    SetupAPI.dll version info
  5. When you run RIPrep from the \\<server>\REMINST\Admin\i386 folder, the version of setupapi.dll in this folder will be used as a higher precedent to the version in the Windows XP or Windows Server 2003 %SystemRoot%\System32 folder, and RIPrep will be able to load correctly.

Cannot install the integrated audio AC97′ hardware from a Asus P4S333 or P4S533 under Windows Vista

System Requirements:

  • Windows Vista SP1
  • Windows Server 2008

The Problem:

So, you get fed-up of trying to get PCI sound cards working under Vista and opted for the integrated sound CPU clogging solution?! I don’t blame you.

The P4S333 may be old kit, but it’s a solid motherboard, very much Vista capable with the right upper end P4 and enough RAM, particularly if you flashed the P4S333 with the P4S533’s BIOS to make use of the 533 MHz FSB. The difficulty we have is however in that the C-Media CMI8738 AC97′ chip that the board uses cannot be matched to any of Windows Updates hardware ID’s and Asus, naturally, do not offer a Vista driver.

More Information:

Thankfully, C-Media do provide generic drivers, and there is a January 2009 driver release for the CMI8738 as of writing. The driver does work once you get it out of the RAR file, however:

  1. The driver does not have the necessary hardware ID to install automatically against the customised chip on the P4S333 / P4S533
  2. The driver is not digitally signed

Despite that, I’ve not seen any problems with it, and despite the CPU connotations, it got rid of irritating buzzing that the Creative Labs Sound Blaster Live! 5.1 was causing with the useless Vista driver set.

The Fix

Very simply, you need to manually install the driver.

  1. Download the latest CMI8738 driver from C-Media (http://www.cmedia.com.tw/). As of writing the driver version was 8.17.33 and it worked a treat
  2. Deflate the RAR into the file system
  3. Open the Control Panel
  4. Open the Device Manager
  5. Find the uninstalled multimedia device in the Other Devices section, right click, Update Driver Software
  6. Click Browse my computer for driver software
  7. Click let me pick from a list of device drivers on my computer
  8. Hit the have disk button
  9. Browse to the path that you inflated the RAR file to, for example if you inflated the RAR file to c:\temp\ you want to browse to the following location:
    c:\temp\PCI-8738-090109-8.17.33(Vista3264-RC-01)\SoftwareDriver\driverNote that if the driver version you are using is newer, then the version in the file name will clearly be different. Ultimately however you are looking to point to the SoftwareDriver\driver folder (there are several INF’s and some CAT files in here)
  10. In the manufacturer list from the available select:
    C-Media
  11. From the Model list select:
    C-Media PCI Audio Device
  12. Click Next
  13. Windows will notify you that the driver has not been digitally signed. You’re going to have to tough this one out I’m afraid and install it.
  14. At that point the audio should just start working.