January 31, 2011 at 10:41 am
Hello,
I want to use Server Objects > Linked Servers to connect a SQL Server 2008 x64 box to tables in an MS Access 2003 database and query the Access data to create a View of the Access data in SQL Server 2008. I tested this on my 32-bit dev SQL Server dev-box and it was easy and worked great. However, on my 64-bit production server the OLE/ODBC drivers aren't there for Access, and so I'm stuck.
All I want is for an item show up in the Server Objects > Linked Servers > Providers list that will enable a connection to MS Access and allow me to query the data.
I found information here:
http://msdn.microsoft.com/en-us/library/ms810810.aspx
...about OLE/ODBC being deprecated and not supported on x64. It recommends using SQL Server Express or Compact editions for complete access. It also recommends using 2007 Office System Driver, linked here,...
...but there are some limitations stated for the 2007 Office System Driver and I can't tell for sure if it will do what I need or not.
Naturally, if I have an installation of SQL Server 2008 I don't want to do a special installation of Express or Compact, if I can avoid it. And I would prefer to avoid randomly installing things on my production server hoping they will work.
Does anyone have experience with this?
Will the Office 2007 drivers be enough, are SQL Server Express/Compact required or is there some other way?
Thanks,
David
February 16, 2011 at 2:44 pm
david.wheelock,
this is my problem too. I have SQL Server 2008 on Windows SBS 2008 (all 64-bit).
I need to export an Access Database (MDB/ACCDB) for each location from SQL Server database which store data for all distributed locations. I tried OPENDATASOURCEand SSIS, but...
First, I tried to execute:
--
SELECT *
FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
'Data Source="d:\Data\Location001.MDB";User ID=Admin;Password=;' )...TableName1 AS TableName2
--
and response was OLEDB driver was not registered.
Then, I found this article:
about "Microsoft Access Database Engine 2010 Redistributable".
It is consisted of: AccessDatabaseEngine.exe and AccessDatabaseEngine_x64.exe.
Then I installed Microsoft Access Database Engine 2010 Redistributable - AccessDatabaseEngine_x64.exe:
and response was:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I googled that, and found nothing!
So, what is solution for that problems:
- how to SELECT with OPENDATASOURCE Access MDB/ACCDB database,
- how to use MDB/ACCDB database in SSIS 2008 in 64-bit environment,
- what should I use AccessDatabaseEngine.exe OR AccessDatabaseEngine_x64.exe in 64-bit environment
July 22, 2016 at 8:46 am
I've got exactly the same problem with SQL Server 2008 32 bits:
'Microsoft.Jet.OLEDB.4.0' was working with .xls, but I needed to try the .xlsx. So I've just installed 'MICROSOFT.ACE.OLEDB.12.0' from the official site with "AccessDatabaseEngine.exe (2007)".
Then the utilization of 'MICROSOFT.ACE.OLEDB.12.0' returns time out every time, and 'Microsoft.Jet.OLEDB.4.0' is broken: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Now I can't find how to uninstall "AccessDatabaseEngine.exe (2007)", the addition of "AccessDatabaseEngine.exe (2010)" doesn't repair it. There is Access 2003 on this server but I can't find AccessDatabaseEngine 2003.
I tried to create linked servers but it's the same.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply