I have a difficult situation trying to set up a linked server to access (no pun intended) some MDB files that we use to transfer report catalogs from our development location to the remote production server. The remote Windows Server 2012 is owned by a customer who does not want to open SQL Server up for direct remote access...thus the MDB files. We load the MDB files with table data, transfer them to our customer's server and then import them into the SQL tables with the linked server. We have 30 customer installations where this scenario works just fine.
However in this particular instance the customer has installed SQL Server 2012 STD 32-bit on the 64-bit Windows OS.
The Jet4.0 driver does not work in the 64-bit environment.
When I install the 64-bit Microsoft.ACE.OLEDB.12.0 driver and try to create the linked server, SQL Server does not recognize this driver as "registered".
When I install the 32-bit Microsoft.ACE.OLEDB.12.0 driver and then create the linked server, it fails to connect to the MDB in the same way that the Jet.4.0 driver fails.
Please Note: I have had success on servers where both SQL Server and Windows are 64-bit by using the 64-bit Microsoft.ACE.OLEDB.12.0 driver
I am assuming that this current issue is related to 32-bit SQL residing on 64-bit Windows.
Does anyone have any suggestions?