How to add new connection provider to Visual Studio?

0
votes

I'm using Visual Studio 2010 for development in business intelligence (BI). In window Connection Manager, there're 9 native providers :

  • Microsoft Jet 4.0 OLE DB Provider
  • Microsoft OLE DB Provider for Analysis Services 11.0
  • Micorsoft OLE DB Provider for Oracle
  • ...

The provider needed is Microsoft Office 12.0 Access Database Engine OLE DB Provider (Microsoft.ACE.OLEDB.12.0), which is not included in this list. However, this provider does exist in my computer : I've used in Excel 2016, as a Microsoft Access connection provider.

So how can I "link" / "add" this provider to VS2010 and make it recognized ?

2
sql-servervisual-studio-2010ms-accessssissql-server-data-tools
You tag it as VS 2012 but mention VS 2010 in your question...which is it? – user847990
This is a spelling mistake, sorry about it. It is VS 2010 – Mincong Huang

2 Answers

1
votes

This is likely happening because you may have installed the 64-bit version of Microsoft Access Database Engine 2010 Redistributable.

Visual Studio / BIDS is a 32-bit application, and hence cannot "see" any of the 64-bit providers, including the Microsoft Office 12.0 Access Database Engine OLE DB Provider that you installed.

To fix this,

  1. Uninstall the 64-bit redistributable that you previously installed.
  2. Download and install the 32-bit redistributable from here. Note the link shows both 32-bit and 64-bit versions, make sure you select the 1st one (marked below) enter image description here
  3. Now you should be able to see your list of available connection managers.
1
votes

Just for informational purposes...You see the provider via Excel because it is installed within the context of Office Suite of products. It is not a provider that is available system wide or where VS and SQL Server could utilize it; to get this you have to install the distribution pointed out by @Shiva.

An Example: enter image description here

Learn more about 32 versus 64 bit history.