Item cannot be found in the collection corresponding to the requested name or ordinal

PowerShell Help
1

This is a continuation of my previous question, different topic.

I’m trying to query an access db/mdb file to get info, but get the above error when I open it. I can’t switch to 32 bit (if that would help) because everything I do is 64 bit. I have Office 365, 64 bit, and it won’t let me install the 32 bit accessdatabaseengine.exe.

I’m writing a powershell 5.1 script to query info from a mdb file based on [this article][1]. I get this error message when I try to open/access an mdb file:

Open done
Item cannot be found in the collection corresponding to the requested name or ordinal.

This is what I’m doing:

   $pathViewBase = 'C:\Data\EndToEnd_view\' #View dir. 
   $XML_MDB_Dirs = @('\AppText.mdb') #more files later
   foreach($mdbFile in $XML_MDB_Dirs)
   {
      $pathToMdb = Join-Path -Path $pathViewBase -ChildPath $mdbFile
      if(Test-Path $pathToMdb)
      {
        $cn = new-object -comobject ADODB.Connection
        $rs = new-object -comobject ADODB.Recordset
        Write-Host "MDB Open next" -ForegroundColor DarkCyan
        $cn.Open("Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $pathToMdb")
        Write-Host "Open done" -ForegroundColor DarkCyan
        $rs.Open(“SELECT *
            FROM [TableName]
            WHERE [TableName].[Message Description] = 'ERROR'”,
            $cn, $adOpenStatic, $adLockOptimistic)
        $rs.MoveFirst()
        Write-Host "Message value obtained for ERROR: " $rs.Fields.Item("Name").Value  #this is error line
        $cn.Close()
        Break ##########################
      }#test-Path
}#foreach

I found this regarding not good solution but I know when I switch to the

Microsoft.Jet.OLEDB.4.0;
connection string info, I get an error connecting. But now it isn’t returning any data, or I’m accessing the data incorrectly. I have simplified my query from the following, in case that was the issue, but get the same error.
“SELECT TOP 1 [TableName].[Message Description],
[TableName].[ColumnName]
FROM [TableName]
WHERE [TableName].[Message Description] = ‘ERROR’”

I checked,

(Get-WmiObject Win32_OperatingSystem).OSArchitecture

and I am running 64 bit powershell, so since I installed accessdatabaseengine_x64.exe (and rebooted), that’s correct.

2

I figured it out. I had “Name” in my $rs.Fields.Item(“Name”).Value instead of the actual Column Name. It’s returning the correct value now.

1 Like