ACE Provider 32 and 64 bit

[stanl]

This might already be well-known in this group, but gave me a burst of happiness yesterday. I had installed the AccessDatabaseEngine_x32 which registers the ACE 12.0 Provider (used to transfer data between sources). But my SQLServer Express is 64 bit, so ACE is not on Provider list meaning no linked Servers to Access or Excel. So trycc

Download 64 bit from: https://www.microsoft.com/en-us/download/details.aspx?id=54920.
Open the command prompt [to be sure, as Administrator].
Type the file path and file name for your Access Database Engine install file.
Follow it with a space and then "/quiet" and then hit Enter.
Delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths

I then opened SQL Server Management Studio 64 and ACE Provider 12.0 and 16.0 both appeared on Provider list. Then ran 32-bit WB script using Ace 12 32 bit and no problems.

[stanl]

Kind of a lame Postscript [read previous post first, please]. The code below is Powershell. In a 64 bit system you will have links to a PS ISE in both 32 and 64 bit.  Open each ISE and execute the code to see both 32/64 bit Providers. If similar lists can be compiled with native WB ignore the PS.

Code Select Expand



function Get-OledbRegistered
{
    [CmdletBinding()]
    [OutputType([System.Collections.Generic.List[PSObject]])]
    param ()


    Process
    {
        $list = New-Object ([System.Collections.Generic.List[PSObject]])


        foreach ($provider in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator())
        {
            $v = New-Object PSObject       
            for ($i = 0; $i -lt $provider.FieldCount; $i++)
            {
                Add-Member -in $v NoteProperty $provider.GetName($i) $provider.GetValue($i)
            }
            $list.Add($v)
        }
        return $list
    }
}


$list = Get-OledbRegistered
$list