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!