May 1, 2012 at 8:29 am
Anyone have any experience with connecting to Access 2010 databases using a Linked Server on a 64-bit SQL Server instance installed on the same 64-bit Windows 7 Enterprise computer?
I am unable to replace the local Office 2010 Professional Plus 32-bit with 64-bit due to corporate standards, and thus cannot install the 64-bit version of the Access 2010 Database Engine (AKA ACE). I have installed the 32-bit version, but can't see it, even using a .UDL file. Anyone?
Steve
(aka sgmunson)
:w00t::w00t::w00t:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2012 at 8:43 am
I think you just need to install the 32-bit JET driver to do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2012 at 8:59 am
You shouldn't have any problems connecting to SQL Server either with an .ADP or .MDB version database. We do it all the time. If you are using ODBC, then you will want to get the appropriate SQL Server Native Client - unless you are running Win7 on workstations (it already has the latest version for all except SQL Server 2012. (BTW, the 64 bit version of Access 2010 is less stable than the 32-bit version.)
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
May 1, 2012 at 9:08 am
maybe things have changed, but i had to uninstall office 32 bit completely, and then install the AccessDatabaseEngine_x64.exe so that i could connect via a linked server.
No big deal for me, my server doesn't need Office, but it certainly needs Linked servers to various resources.
I'm still under the impression that ODBC drivers for 64 bit must be used form a 64 bit SQL installation ; no 32 bit allowed?
Lowell
May 1, 2012 at 9:28 am
I've searched the internet high and low for a 32-bit JET driver, but can't find one, unless I'm just using the wrong search terms... Do you have a link? The other possibility is that it's already installed, although it appears inaccessible. I have a Windows 7 Enterprise 64-bit computer with SQL Server 2008 64-bit, with Office 2010 Professional Plus as well as Access 2010 installed - where all the Office products are 32-bit due to corporate standards. I don't have the option of installing the 64-bit version of Office and/or Access.
I'm hoping to find a provider that works for OLEDB or ODBC on that SQL instance so I can import data from MS Access directly instead of via SSIS. Doing the same from within the local SQL instance doesn't appear to be possible, as I can't even use the OLEDB Provider for ODBC, as it gives me an architecture mismatch. I've installed the 32-bit version of the Access 2010 Database Engine (allegedly, ACE), but I haven't been able to see it exist beyond the listing in the Programs and Features within Control Panel, that says it's installed.
If you have a link to a 32-bit JET driver, that will function in the 64-bit SQL instance, let me know. Any and all assistance will be appreciated...
Steve
(aka sgmunson)
:w00t::w00t::w00t:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2012 at 11:33 am
sgmunson (5/1/2012)
...I'm hoping to find a provider that works for OLEDB or ODBC on that SQL instance so I can import data from MS Access directly instead of via SSIS. Doing the same from within the local SQL instance doesn't appear to be possible, as I can't even use the OLEDB Provider for ODBC, as it gives me an architecture mismatch. I've installed the 32-bit version of the Access 2010 Database Engine (allegedly, ACE), but I haven't been able to see it exist beyond the listing in the Programs and Features within Control Panel, that says it's installed.
...
I'm curious as to why you don't want to use the SSIS import process - we use it regularly to import data from Access and Excel into SQL Server. If you don't use that process, are you wanting to link to the Access tables? The other option would be to use the Access Upsizing Wizard that will move an entire table from Access to SQL Server. We have used it extensively with good success. As far as I can tell, the Jet ODBC driver is what you are using to connect to Access with SSIS, and the 32-bit Professioinal version of Office 2010 installs that.
Wendell
Evergreen, CO
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
May 1, 2012 at 11:41 am
found this over at stack overflow, which says there's a bit of configuring to do, but you can use 32 bit drivers with 64 bit SQL:
10 down vote accepted
It turns out that you can create 32-bit ODBC connections using C:\Windows\SysWOW64\odbcad32.exe. My solution was to create the 32-bit ODBC connection as a System DSN. This still didn't allow me to connect to it since .NET couldn't look it up. After significant and fruitless searching to find how to get the OdbcConnection class to look for the DSN in the right place, I stumbled upon a web site that suggested modifying the registry to solve a different problem.
I ended up creating the ODBC connection directly under HKLM\Software\ODBC. I looked in the SysWOW6432 key to find the parameters that were set up using the 32-bit version of the ODBC administration tool and recreated this in the standard location. I didn't add an entry for the driver, however, as that was not installed by the standard installer for the app either.
After creating the entry (by hand), I fired up my windows service and everything was happy.
Lowell
May 1, 2012 at 12:03 pm
Interesting concept, but as the article didn't present it with much in the way of specifics, it's too risky for me to hope to get it right. As I can't even see the 32-bit Access DB Engine even in the 32-bit ODBC Administrator (at least as an OLE DB Provider, anyway), not sure it would do any good.
In any case, I decided to rehost the data into the SQL instance from the Access DB, and that eliminates the need for the linked server that goes FROM SQL to ACCESS, and instead, I can just run my query on the SQL instance without the linked server, as the table is now local to the instance.
Based on all the things I ran across - and the number of posts in various places decrying MS's apparent failure to understand the needs of their customers (or, perhaps, at the minimum, considerable failure to explain either their reasoning or the proper suggested alternative), I have to conclude that MS is primarily at fault for this conundrum. Not much point in creating 64-bit software if you're going to invalidate the 32-bit stuff we all still HAVE to use - especially when in this case, you're forced to choose one or the other (Office 32-bit vs. Office 64-bit), instead of being able to have a practical migration path. Oh well... problem worked around...
Thanks for all the help, folks. Much Appreciated...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 1, 2012 at 12:25 pm
Thanks for letting us know you found a workaround. As a rule of thumb we always try to put the data in SQL Server (except in very specific situations), and use Acces as the front-end for users.
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply