Problem
ACCESS database connection failure on an app in Azure Web Service:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
The 'Microsoft.Jet.Ole8db.4.0' provider is not registered on the local machine.
Background
Microsoft recognizes that the error is by design as we do not install the Microsoft.Ace.we.12.0' driver. The Microsoft.Ace drivers are not supported in server side environments and as of now there is no plans to add these to the Azure Web Sites.
My working solution
The solution is complex, but it is working at least.
Step 1) Separate connection strings for the 64-bit source (on PC) and 32-bit target (Azure)
Connection String for 64-bit environment:
"Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sMdb & ";" & "Jet OLEDB:Database Password=" & sPass & ";" & "Persist Security Info=True;"
Connection String for 32-bit target environment:
"Provider=Microsoft.Jet.Ole8db.4.0;" & "Data Source=" & sMdb & ";" & "Jet OLEDB:Database Password=" & sPass & ";" & "Persist Security Info=True;"
Step 2) Adjust Registry Editor
In the search box on the taskbar, type “regedit” to open Registry Editor and adjust
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion
Add:
DWORD ForceLog set value to 1
DWORD LogFailures set value to 1
DWORD LogResourceBinds set value to 1
DWORD EnableLog set value to 1
String LogPath set value to folder for logs (e.g. C:\FusionLog)
(reference: https://stackoverflow.com/questions/255669/how-to-enable-assembly-bind-failure-logging-fusion-in-net)
Step 3) Switch settings when deploying to Azure
- Compiler settings
Change Project / Property / Compile / Target CPU to “x86”
- Menu bar - Build / Configuration Manager
Change Platform to "x86"
- IIS – Application Pools
Change Enable 320Bit Applications to “True”
Step 4) Switch settings back to 64-bit development PC environment
- Compiler settings
Change Project / Property / Compile / Target CPU to “Any CPU”
- Menu bar - Build / Configuration Manager
Change Platform to "Any CPU"
- IIS – Application Pools
Change Enable 320Bit Applications to “False”