Provider cannot be found when try to open/access mdb

Question

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

MDB Open next
Provider cannot be found. It may not be properly installed.

I'm pretty sure I need to adjust my connection info according to what I have installed. 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.Jet.OLEDB.4.0;Data Source = $pathToMdb") ###error this line
        Write-Host "Open done" -ForegroundColor DarkCyan
        $rs.Open(“SELECT TOP 1 [TableName].[Message Description],
            [TableName].[Column1]
            FROM [TableName]
            WHERE [TableName].[Message Description] = 'ERROR'”,
            $cn, $adOpenStatic, $adLockOptimistic)
        $rs.MoveFirst()
        Write-Host "Message value obtained for ERROR: " $rs.Fields.Item("Name").Value
        Break ##########################
      }#test-Path
}#foreach

I found this regarding odbc connections, and it seems to say I need to adjust my connection info. Looking at what's installed on my computer, I see this, but I'm a little unclear what I need to adjust my open code to use. Would I need to replace 'Microsoft.Jet.OLEDB.4.0' with 'SQLNCLI11.dll'?

Update: I checked,

(Get-WmiObject Win32_OperatingSystem).OSArchitecture

and I am running 64 bit powershell, so since I installed accessdatabaseengine_x64.exe access database engine, per @ Mathias R. Jessen below (and rebooted), that's correct. But I still get the same error. I'm not sure if there's something I could check to see if it's installed correctly, or if I need to use SQLNCLI11.0 as the provider instead of Microsoft.Jet.OLEDB.4.0, or if I need to add "using" at the top? Or do I need to check if a powershell module is installed?

You need to install the appropriate ["redistributable" package](https://www.microsoft.com/en-us/download/details.aspx?id=54920), this will include the JET/OLEDB driver — Mathias R. Jessen, Jan 05 '22 at 15:23
@MathiasR.Jessen - Thanks, I downloaded the 64 bit one, closed powershell and re-opened it, and still get the same error message. Do I need to add something, like using ....? Do I need to adjust my connection string? Restart computer? — Michele, Jan 05 '22 at 15:40
@MathiasR.Jessen - I still get the same error after reboot: Provider cannot be found. It may not be properly installed. I checked, and Office 365 is 64 bit version, so it's good I installed 64 bit redestributable accessDatabaseEngine — Michele, Jan 05 '22 at 15:54
I also tried re-installing and rebooting again and get same error. — Michele, Jan 05 '22 at 16:01
@MathiasR.Jessen - is there something I should change or check or redo that you know of? Should I check for the driver? I'm not sure where/what to look for. — Michele, Jan 05 '22 at 16:35
`Provider = Microsoft.Jet.OLEDB.4.0` is the older 32-bit (only) provider. Since you are running in the 64-bit space you should be using `Provider=Microsoft.ACE.OLEDB.12.0` — Gord Thompson, Jan 07 '22 at 01:51
@GordThompson - Thank you. I already figured it out at this post, which I'll add as an answer. https://forums.powershell.org/t/provider-cannot-be-found-it-may-not-be-properly-installed/18275/4 — Michele, Jan 10 '22 at 12:51

Provider cannot be found when try to open/access mdb

0 Answers0