Error message when you use an ADODB.Connection object with an ODBC driver on a Windows x64-based computer: "Provider is not specified and there is no designated default provider"


Source: Microsoft Support

RAPID PUBLISHING


RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Symptom




When you use an ADODB.Connection object with an ODBC driver and do not specify an OLEDB provider in your connection string, the following error may be reported on a 64 bit platform.


Error Number: 800A0EA9


Error Message: Provider is not specified and there is no designated default provider.


Source: ADODB.Connection

Cause


This error occurs because no default provider is designated on a 64 bit platform. On 32bit platforms, MSDASQL is designated as the default provider and hence you will not run into this problem.

Resolution




To resolve this issue, you must install the 64 bit MSDASQL provider and explicitly designate it in your ADODB.connection.
  1. Download and install 64-Bit OLEDB Provider for ODBC (MSDASQL).
  2. Specify your ODBC connection in your ADO connection as follows:

    "Provider=MSDASQL; DSN=MyDSN"


Note If your application is a 64 bit application, you need to have a 64 bit ODBC driver from the vendor. The 64 bit MSDASQL driver cannot load a 32 bit ODBC driver in the same process.

More Information




You may also run into the same problem with 32 bit ADO applications using an ODBC DSN connection and running in WOW environment. To resolve the error in that environment you must create the DSN using 32 bit ODBC administrator that can be found at %windir%\SysWOW64\odbcad32.exe.


Review the following four connection strings for SQL Server, that do not explicitly specify 'Provider=MSDASQL'. After each connection string, see the table that follows it for the expected behavior on 64 bit platforms.

Connection string 1



conn.Open “Driver={SQL Server};Server=servername;UID=userid;PWD=strongPassword ;database=master;”


Note MSDASQL is not specified.








Platform





 


Windows 2003 SP2



 


Windows 2003 SP2 with 64 bit MSDASQL installed



 


Vista SP1



 


Windows7



 


 


X64 or IA64



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider(1)



 


Success, default driver=msdasql



 


Success, default driver=msdasql

Connection string 2



conn.Open “User ID=userid;Password=strongpassword;Data Source=servername;Initial Catalog=master;”


Note SQLOLEDB provider is not specified.





Platform

 





 


Windows 2003 SP2



 


Windows 2003 SP2 with 64 bit MSDASQL installed



 


Vista SP1



 


Windows7



 


 


X64
or IA64


 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Error: Microsoft OLEDB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified



 


Error: Microsoft OLEDB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Connection string 3



conn.Open “Integrated security=SSPI;Data Source=<Servername>;Initial Catalog=master;”


Note Using integrated authentication without specifying SQLOLEDB.








Platform






 


Windows 2003 SP2



 


Windows 2003 SP2 with 64 bit MSDASQL installed



 


Vista SP1



 


Windows7



 


 


X64 or IA64



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Microsoft OLE DB Service Components: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done



 


Microsoft OLE DB Service Components: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

Connection string 4



conn.Open " Driver={SQL Server};server=Servername;database=master;Trusted_Connection=True":


Note Using integrated authentication without specifying MSDASQL provider.








Platform





 


Windows 2003 SP2



 


Windows 2003 SP2 with 64 bit MSDASQL installed



 


Vista SP1



 


Windows7



 


 


X64 or IA64



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Error: ADODB.Connection: provider is not specified and there is no designated default provider



 


Success



 


Success

References

  • 195913 How To Generate ODBC & OLEDB Connection Strings with Data Links
  • 193332 FILE: MDACCON.EXE Using Connection Strings with ODBC/OLEDB/ADO/RDS
  • 942976 The 32-bit version of the ODBC Administrator tool and the 64-bit version of the ODBC Administrator tool display both the 32-bit user DSNs and the 64-bit user DSNs in a 64-bit version of the Windows operating system
  • 957570 The Microsoft OLE DB Provider for Jet and the Microsoft Access ODBC driver are available in 32-bit versions only
  • Data Access Technologies Road Map (http://msdn.microsoft.com/en-us/library/ms810810.aspx)

DISCLAIMER


MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.


TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.