Error ORA-03106 When using VB6 to access Oracle database in Windows 7 64bit

Question

I am receiving an ORA-03106 error when I try to access the database using MS ADO. This problem is only occurring on Windows 7 64 bit machines. Apparently there were issues with XP in the past, but they were fixed by some registry edits. These registry edits did nothing when applied to the Windows 7 machine.

I don't have much information about the database because that is handled by a different department, I am an intern and this problem was just thrown at me with very little background info. Here is the code, I get the error on the MyConnection.Open line.

Function db_fetch(sIButtonID, ByRef lstatus, sdescr) As tIBRec

 Dim myRecordSet As ADODB.Recordset

 'Dim sConnectString As String
 Dim sSQL_Statement As String
 Dim this_ibutton As tIBRec

 Set myRecordSet = New ADODB.Recordset

  If Not CheckForNetDrive("O:") Then
    lstatus = Shell("net use O: \\host\oracle /PERSISTENT:YES")
    If Not CheckForNetDrive("O:") Then lstatus = MapNetworkDrive("O:", "\\host\oracle")
    If lstatus = 0 Then
        If InStr(GetEnvironmentVar("PATH"), "orawin95\bin") = 0 Then
            lstatus = SetEnvironmentVariable("PATH", GetEnvironmentVar("PATH") & ";O:\orawin95\bin")
        End If
    End If
  Else
    If InStr(GetEnvironmentVar("PATH"), "orawin95\bin") = 0 Then
        lstatus = SetEnvironmentVariable("PATH", GetEnvironmentVar("PATH") & ";O:\orawin95\bin")
    End If
  End If


  'sConnectString = "Provider=msdaora;Data Source=(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=12)(SERVER=DEDICATED)));User Id=username;Password=password;"

  'sConnectString = "Provider=msdaora;Data Source=(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=03)(SERVER=DEDICATED)));User Id=username;Password=password;"
  'sConnectString = "Provider=msdaora;Data Source =(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=03)(SERVER=DEDICATED)))User Id=username;Password=password;"

  'On Error GoTo err:

  If myConnection.State = 0 Then
      myConnection.Mode = adModeReadWrite
      myConnection.Open sConnectString
  End If

Answer 1

I assume the ADO (without .NET), i.e. the "Oracle Provider for OLE DB" is not available on your machine.

Solution:

• Install Oracle client 64Bit as usual - I assue this has been done already.
• Download provider Oracle Provider for OLE DB according your client version in 32Bit, Xcopy version, can be found here: 32-bit Oracle Data Access Components (ODAC) Downloads
• Unpack the software and install the provider with Install.bat oledb c:\Oracle\OLEDB_11.2_x32\ OraClient11g_home1 true. Assume "OraClient11g_home1" is your Oracle home name. Ensure that the given directory is different to your Oracle client directory!
• Go to your environment settings and add directory c:\Oracle\OLEDB_11.2_x32\ and c:\Oracle\OLEDB_11.2_x32\bin\ to your PATH environment. Ensure that these directories are placed before your Oracle client home directory.
• Add environment variable TNS_ADMIN = {Oracle client home diretory}\network\admin

Try if it works.

One note: You can install only one single version of "Oracle Provider for OLE DB" on one machine, even if you have several Oracle clients installed.

Following this procedure I managed to make all working for Windows 7, 64Bit, Oracle 11.2 and my old VBA Excel Macros which are running on Win7 x64 and old XP at the same time.

Sorry I missed that you try to use the Microsoft OLE DB Provider (msdaora), this solution is for the Oracle Provider. I don't know how different they are, perhpaps you only have to adapt the connection string.