ISO 3166-1 County & Nation State Data Stores

System Requirements:

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

The Problem:

If you have ever been mildly curious over the country ID listing, or ever engaged in a project where language identification was important having to come up with a digital list of the global nation states 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 country list, you can use my files below as a base for your project.

The data source has been taken from the latest revision of the ISO public states list (ISO 3166-1) registry, so any errors are universally the ISO’s problem if indeed there are any.

I have made the following three files available:

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

The CSV contains:

  1. Country Code
  2. Country Name
  3. 2 digit Alpha Numeric Code
  4. 3 digit Alpha Numeric Code

The Spreadsheet and MDB Database contain:

  1. Country Code
  2. Country Name
  3. 2 digit Alpha Numeric Code
  4. 3 digit Alpha Numeric Code

 

Download: CSV, Excel 2010, Access 2003 (42KB)

You may also be interested in my stores for RFC 1766 International Language Identification Codes.

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.

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!

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.