1

I don't have an oracle DB installed on my machine. The DB location is on UNIX machine. I Want to run Insert queries in DB located on other VM. I had written one code iusing VB Script but that throws me an error while running the same. Please help me. Below is my code:

'Save the file with <filename>.vbs

Set dbMyDBConnection = CreateObject(“ADODB.Connection”)
ConnectionString = “(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.16.31)(PORT=1521)))(CONNECT_DATA=(SID=PUNDEV11)))”

strConnection = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ConnectionString & “;Jet OLEDB:Database Password=” & strPassword & “;User ID=” & strUsername & “;Password=” & strPassword & “;”

dbMyDBConnection.Open strConnection

Also, I checked replacing the provider to Microsoft.Jet.OLEDB.12.0 and OraOLEDB.Oracle.

I am using Windows 7 64 bit platform.

Error:

Provider cannot be found. It may not be properly installed. Code : 800A0E7A Source : ADODB.Connection

user692942
  • 14,779
  • 6
  • 66
  • 157
subodh agnihotri
  • 21
  • 5
  • 1
    It never ceases to amaze me how people expect you to help them without detailing the error they received. Just saying *"throws me an error"* isn't enough. – user692942 Mar 09 '16 at 10:03
  • Sorry for less details: error: Provider cannot be found. It may not be properly installed. Code : 800A0E7A Source : ADODB.Connection – subodh agnihotri Mar 09 '16 at 10:04
  • It's fine just [edit the question](http://stackoverflow.com/posts/35888245/edit) and add the extra error details. – user692942 Mar 09 '16 at 10:05

2 Answers2

2

Provider Microsoft.Jet.OLEDB.x is used to connect to an MS Access Database.

You must use the "Oracle Provider for OLE DB" provider. Would be like this:

ConnectionString = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.16.31)(PORT=1521)))(CONNECT_DATA=(SID=PUNDEV11)))"

Set dbMyDBConnection = CreateObject("ADODB.Connection")
dbMyDBConnection.provider = "OraOLEDB.Oracle"    
dbMyDBConnection.Open "Data Source=" & ConnectionString , strUsername , strPassword

The "Microsoft OLE DB Provider for Oracle" should be also possible. However, it has been deprecated for many years and it is available only for 32-bit.

dbMyDBConnection.provider = "MSDAORA"
Wernfried Domscheit
  • 38,841
  • 5
  • 50
  • 81
  • Thanks for response. I checked with your input, but still getting the same issue. I switched the command prompt working directory to SysWOW64 directory in C:/Windows as well, but still it throws me "Provider cannot be found. It may not be properly installed". – subodh agnihotri Mar 09 '16 at 10:13
  • @subodhagnihotri Switching the command prompt to the `SysWOW64` directory will do *nothing* because providers are stored in the Windows Registry. You'll need to check you have the correct provider installed using the `%systemroot%\SysWOW64\odbcad32.exe` *(Otherwise known as the 32 Bit ODBC Data Source Administrator)* program. – user692942 Mar 09 '16 at 10:15
  • I assume you have to take the error message verbatim: Install the provider, you can download it from here: [Oracle Data Access Components (ODAC) for Windows Downloads](http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html) – Wernfried Domscheit Mar 09 '16 at 10:17
  • @Lankymart, ODBC Data Source Administrator shows only the ODBC drivers (or providers), however an `OLE DB` Provider is not ODBC.See [ODBC](https://en.wikipedia.org/wiki/Open_Database_Connectivity) vs. [OLE DB](https://en.wikipedia.org/wiki/OLE_DB). – Wernfried Domscheit Mar 09 '16 at 10:28
  • OLEDB is Microsoft proprietary but I'm fairly sure most OLEDB provider drivers have an ODBC interface under the hood. Usually they are shown in the Data Source Administrator regardless. See [what is the difference between OLE DB and ODBC data sources?](http://stackoverflow.com/q/103167/692942) – user692942 Mar 09 '16 at 10:32
  • @WernfriedDomscheit - I installed the components from the location you mentioned. Can you help me with further settings? I am very new to all these stuff. – subodh agnihotri Mar 09 '16 at 10:32
  • Did you also install an Oracle (Instant) Client? It is required. – Wernfried Domscheit Mar 09 '16 at 11:58
  • With the right architecture? (32 bit vs. 64 bit)? All components (i.e. your VBScript environment, Oracle Client and OLE DB Provider) must be the same! – Wernfried Domscheit Mar 09 '16 at 12:07
  • 64 bit installation and Yes all the components Installed – subodh agnihotri Mar 09 '16 at 13:40
  • Check this: http://stackoverflow.com/questions/659341/the-provider-is-not-compatible-with-the-version-of-oracle-client – Wernfried Domscheit Mar 09 '16 at 15:33
0

Short answer: You need to install a 32-bit driver and need to use that driver and its corresponding connection string from here in a 32-bit terminal. If you get the combination wrong, you will hit with all sort of errors.

Long answer: I faced the same problem and wasted a few days trying to get it working. Here I posted a detailed step-by-step answer on how to get this working.

How to connect to oracle database using VBScript and UFT?

Pramod Yadav
  • 129
  • 12