November 25, 2012 (Modified December 7, 2012, May 22, 2015, May 11, 2016)
Compatibility problems? Visual Basic 6.0, released in 1998, is not officially compatible with Windows 8 Pro 64 bit… or Windows 7, or Windows Vista. But I still like the language a lot for its simplicity, rapid development, and significant pre-existing code base within my company. Of course, Oracle Database 11.2.0.3 and the Oracle Client 11.2.0.3 are not officially supported on Windows 8, so maybe the quest is an exercise in futility.
Roughly a month ago a thread appeared on the OTN forums asking how to connect Visual Basic 2010 to Oracle Database Personal Edition on Windows 7 Pro 64 bit running on the same computer. Several people offered very good advice to guide the OP. The OP eventually asked how to connect Visual Basic 6 to Oracle Database Personal Edition running on the same Windows 7 Pro 64 bit computer. As I mentioned, Visual Basic 6 is not officially compatible with Windows 7, and the fact that it is a 32 bit application running on a 64 bit Windows computer means that the 32 bit Oracle Client must also be installed on the computer.
Visual Basic 6.0 (note that you should install service pack 6 for Visual Basic 6, even if the installer locks up at the very end) will run just fine on Windows 7 Pro 64 bit (and probably on Windows 8 Pro 64 bit also), although drawing form objects is a little slow. The automatic Windows updates may also pose an issue. From time to time it may be necessary to re-register the 32 bit MSCOMCTL.OCX file to avoid error messages when opening Visual Basic projects. To re-register that file, from a Windows command prompt (Run – cmd.exe) , enter the following:
cd \windows\syswow64 regsvr32 MSCOMCTL.OCX
Using Visual Basic 6.0, or any development environment for that matter, requires that certain components be selected for installation when installing the Oracle Client. At a minimum, Oracle ODBC Driver 11.2.0.x.0 (for ODBC type connections), and Oracle Provider for OLE DB 11.2.0.x.0 (for OLE DB type connections) must be installed with the Oracle Client.
Let’s set up a simple Visual Basic project to demonstrate connecting to Oracle Database 11.2.0.3 running on the same Windows 8 computer. First, when installing Visual Basic 6, you may want to install and register a couple of additional uncommonly used controls. Those controls are located in the \COMMON\TOOLS\VB\CONTROLS folder on the Visual Basic install CD. On a 64 bit computer, the controls should be copied to the C:\Windows\Syswow64 folder and then registered in that location using regsvr32 as demonstrated above with the MSCOMCTL.OCX file. In some cases, a license file must also be imported into the Windows registry – those files have a .REG extension in the same location on the CD:
On to building the project. First, we need to add a reference to allow the project to use ADO and the Oracle Provider for OLE DB that was installed with the Oracle Client. From the Project menu, select References…:
Next, we need to add the most recent version of the Microsoft ActiveX Data Objects Library, version 6.1 for Windows 7 and Windows 8, version 6.0 for Vista, or version 2.8 for Windows XP. After selecting the correct version, click the OK button:
Now let’s add a couple of additional controls to the project. I first started using the Microsoft Grid Control in Visual Basic 2.0, so for old time’s sake let’s add that control to the project. We may also want to add a status bar to the project’s form, so let’s also add Microsoft Windows Common Controls 6.0 (SP6). Then click the OK button:
Now, draw the form controls on the form. We need three command buttons named cmdLateBinding, cmdEarlyBinding, and cmdQueryDatabase, a grid control named grdOutput, and optionally a status bar named stbStatus with its Style property set to Simple:
Let’s add the code to the Late Binding (cmdLateBinding) button (this is essentially the same code that I provided in the OTN thread, and does not require the addition of the Microsoft ActiveX Data Objects Library in the references for the project – so this code is a good simple test to make certain that everything is installed correctly):
Dim intResult
Dim strDatabase
Dim strUserName
Dim strPassword
Dim strSQL
Dim dbDatabase
Dim snpData
'On Error Resume Next
strDatabase = "MyDB" 'From tnsnames.ora
strUserName = "MyUserID"
strPassword = "MyPassword"
Set snpData = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";"
dbDatabase.Open
If (dbDatabase.State = 1) And (Err = 0) Then
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " SYSDATE CURRENT_DATE" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " DUAL"
snpData.Open strSQL, dbDatabase
If snpData.State = 1 Then
If Not (snpData.EOF) Then
Do While Not (snpData.EOF)
MsgBox snpData("current_date")
snpData.MoveNext
Loop
End If
snpData.Close
End If
Else
intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
End If
dbDatabase.Close
Set snpData = Nothing
Set dbDatabase = Nothing
Let’s run the project and click the Late Binding button to see what happens. We should expect to see some sort of connection failure message, unless there is an entry in the tnsnames.ora for MyDB:
Well, that error message certainly was unexpected (the OP in the OTN thread may have encountered the same error message)! Let’s click on the Debug button:
ADODB cannot find the OraOLEDB.Oracle provider. I am sure that I installed that component when installing the 32 bit Oracle Client on the computer. The Visual Basic code that was added to the Late Binding command button is very generic. In fact, as written the code can be placed in a plain text file with a .VBS extension and executed as a VBScript file with either the wscript or cscript command. Copy the code from the Late Binding command button into a plain text file (start the Notepad program and paste the code), then save the file as “LateBinding.vbs” (including the quotes).
Now to test the script that was just created. Open a Windows command prompt (Windows key and R, type cmd, press the Enter key). Change to the folder where the LateBinding.vbs file was saved, then type:
wscript LateBinding.vbs
Note that this time the computer indicated an ORA-12154: TNS:could not resolve the connect identifier error, rather than a Provider could not be found error – we were hoping to obtain the same ORA-12154 error as we did when working in Visual Basic 6. What changed? Well, this is a 64 bit computer, so the 64 bit wscript program was used, which used the 64 bit Oracle client (actually the 64 bit Oracle Database home files).
Let’s try again, this time with the 32 bit version of the wscript program. To execute the script with the 32 bit wscript, execute the following:
c:\windows\SysWOW64\wscript LateBinding.vbs
Notice that the error now appears as Provider could not be found, which is the same error message that was observed in Visual Basic 6. We reproduced the problem! Now how do we fix it? I suppose that we should check with the Oracle Client installer to verify that the Oracle Provider for OLE DB 11.2.0.x.0 component was in fact installed. Let’s re-run the Oracle installer to check the Inventory of the installed components. Just open the Start menu, click the Oracle – OraClient11g_home1 folder, then expand Oracle Installation Products, then click Universal Installer:
Uh, sure. Windows 8 and its tablet-like Start menu… good luck finding the Universal Installer for the Oracle Client. (Edit November 26, 2012: I was trying to be humorous here. If you have a touch screen, you can swipe your finger up from the bottom of the screen to reveal the option to display all items that normally appear on the pre-Windows 8 Start menu, separated by folder/Oracle home. If you do not have a touch screen, hold down the Windows key and press the W key while in this new Windows 8 start screen to display the same list of items separated by folder/Oracle home.) I always change the base install folder to C:\Oracle when installing Oracle components, so to start up the Universal Installer for the Oracle Client, I can just run:
C:\oracle\product\11.2.0\client_1\oui\bin\setup.exe
Oracle Provider for OLE DB 11.2.0.3.0 is in the list of installed components (click the Installed Products button in the Oracle Installer to see the above list), so why doesn’t the Oracle Provider for OLE DB from the 32 bit client work as expected? That component requires that the OraOLEDB11.DLL file is found in the expected location, in my case, here:
C:\Oracle\product\11.2.0\client_1\bin\OraOLEDB11.DLL
Yes, the file is in that location. Is the Oracle Client 11.2.0.3 just not compatible with Windows 8 Pro? The Oracle Client 11.2.0.3 seemed to work as expected on a computer that was upgraded from Windows 7 Pro to Windows 8 Pro, including the OLE DB functionality, so why will that functionality NOT work on a new computer with Windows 8 Pro installed?
While comparing the Windows registry entries on a Windows 7 Pro 64 bit computer with the Windows registry entries on the Windows 8 Pro computer where the OLE DB functionality did not work, I noticed a problem – missing entries in the Windows 8 Pro’s Windows registry. From the Windows 7 Pro 64 bit computer, I exported the three missing Windows registry sections:
3F63C36E-51A3-11D2-BB7D-00C04FA30080.reg:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}]
@="OraOLEDB.Oracle"
"OLEDB_SERVICES"=dword:ffffffff
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors]
@="Extended Error Lookup Service"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\OLE DB Provider]
@="Oracle Provider for OLE DB"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ProgID]
@="OraOLEDB.Oracle.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\TypeLib]
@="{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.Oracle"
3FC8E6E4-53FF-11D2-BB7D-00C04FA30080.reg:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\ProdID]
@="OraOLEDB.ErrorLookup.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.ErrorLookup.1"
0BB9AFD1-51A1-11D2-BB7D-00C04FA30080.reg (just the win32 section was missing):
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\TypeLib\{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}\1.0\win32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
After verifying that the Oracle Client’s home on the Windows 8 Pro computer was in fact located in C:\Oracle\product\11.2.0\client_1 (it may not be on your computer, change the above registry files as necessary for your computer – use two \\ characters for each \ character that normally appears in the path to the Oracle Client’s home), I imported the registry entries into the Windows 8 Pro computer’s registry by double-clicking each of the files that were exported from the Windows 7 Pro computer.
The result after importing the registry entries and re-running the script with the 32 bit wscript:
Well, that is not good, another error message. But wait, that was the same error message displayed when the 64 bit wscript was used to execute the script file. Maybe we are making progress. Let’s fix the LateBinding.vbs script file so that the strDatabase variable is set to a valid database name found in the 32 bit client’s tnsnames.ora file (I will use a database named sample, which has the Oracle sample schema loaded), the strUserName variable is set to a valid database username in the database (I will use the sh user), and the strPassword variable is set to the correct password for the username.
Let’s try executing the script again:
Oh, another error message. ORA-28000: the account is locked. I guess that some Oracle rules still apply. Start up a SQL*Plus session in another Windows command prompt, connect to the database as the SYS AS SYSDBA user, and unlock the SH account (edit November 26, 2012: note that this account should probably be locked again once we finish experimenting):
ALTER USER SH ACCOUNT UNLOCK;
Let’s try executing the script again:
It Worked! The 32 bit version of wscript on Windows 8 Pro 64 bit was able to connect to the 11.2.0.3 64 bit database running on the same computer and select the current date and time from the database. But, does the Late Binding example code in Visual Basic 6 work? Let’s check:
Nice!
—
[Headache Induced Rant] You may notice a nearly 3 hour time difference between the time output by wscript and the time output by Visual Basic 6. That is not an error – I spent nearly three hours trying to put this blog article together in WordPress (total time putting together this article was close to 5 hours) on the Windows 8 computer AFTER successfully connecting to the database using the 32 bit version of wscript. The Sony laptop designer who thought it would be a good idea to place the laptop’s left and right mouse buttons underneath the corners of the touch pad and then thought that it would be a good idea to enable zoom control when one finger is barely touching the location of the left mouse button while the other finger is attempting to scroll up and down the WordPress editor’s page should be taken out behind the building and slapped a couple of times. I quite literally had to undo an unintended page zoom very close to 50 times while putting together this blog article. The darn laptop has a touch screen… if I really want to zoom the screen, I will either manually select the zoom feature in the application or put two fingers on the screen and then separate those fingers. (Edit November 26, 2012: Sony does a good job of burying the option to disable this touch pad feature – it is found in the same application that disables the touch pad’s tap to left-click “feature”.)
And which Microsoft developer thought that it would be a great idea to have a large clock pop up on screen in the lower left (taking away the typing entry focus from the application that was being used) on occasion when someone is trying to type on the computer? That developer should also be taken out behind the building and slapped. [/Headache Induced Rant]
—
The above example code used early late (edit: July 24, 2014) binding, which defines variables as variants and then later assigns object types to the variables. That technique, which now does work and is compatible with wscript, tends to result in poor performance. Poor performance should be considered an application bug, so let’s build an example that uses early binding (note that the References mentioned earlier in this article must have been selected). Add the following code to the Early Binding button (note that the code is attempting to connect to the sample database as sh user):
Dim intResult As Integer
Dim strSQL As String
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim snpData As ADODB.Recordset
Dim dbDatabase As ADODB.Connection
On Error Resume Next
Set dbDatabase = New ADODB.Connection
strDatabase = "sample" '"MyDB" 'From tnsnames.ora
strUserName = "sh" '"MyUserID"
strPassword = "sh" '"MyPassword"
'Connect to the database, specify that the default array fetch size should be 100 rows
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open
If (dbDatabase.State <> 1) Or (Err <> 0) Then
intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
Else
Set snpData = New ADODB.Recordset
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " COUNT(*) NUM_ROWS" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " CUSTOMERS"
snpData.Open strSQL, dbDatabase
If snpData.State = 1 Then
If Not (snpData.EOF) Then
MsgBox "Number of rows in the Customers table: " & Format(snpData("num_rows")), vbInformation
Else
MsgBox "No rows returned from the query.", vbCritical
End If
snpData.Close
Else
MsgBox "The query could not be executed.", vbCritical
End If
End If
'When finished, clean up
If dbDatabase.State = 1 Then
dbDatabase.Close
End If
Set snpData = Nothing
Set dbDatabase = Nothing
Let’s see the result of executing the above code:
Wow, 55,500 rows in that sample table, that is a lot of rows.
Finally, let’s add the code to the Query Database button. This code will use early binding, bind variables, and present the result rows in the grid control.
Dim i As Integer
Dim intResult As Integer
Dim strSQL As String
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Dim strOut As String
Dim snpData As ADODB.Recordset
Dim comData As ADODB.Command
On Error Resume Next
Set dbDatabase = New ADODB.Connection
strDatabase = "sample" '"MyDB" 'From tnsnames.ora
strUserName = "sh" '"MyUserID"
strPassword = "sh" '"MyPassword"
'Connect to the database, specify that the default array fetch size should be 100 rows
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open
If (dbDatabase.State <> 1) Or (Err <> 0) Then
intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
Else
Set comData = New ADODB.Command
With comData
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " CUST_ID," & vbCrLf
strSQL = strSQL & " CUST_FIRST_NAME," & vbCrLf
strSQL = strSQL & " CUST_LAST_NAME," & vbCrLf
strSQL = strSQL & " CUST_GENDER," & vbCrLf
strSQL = strSQL & " CUST_YEAR_OF_BIRTH," & vbCrLf
strSQL = strSQL & " CUST_MARITAL_STATUS," & vbCrLf
strSQL = strSQL & " CUST_STREET_ADDRESS," & vbCrLf
strSQL = strSQL & " CUST_POSTAL_CODE," & vbCrLf
strSQL = strSQL & " CUST_CITY," & vbCrLf
strSQL = strSQL & " CUST_STATE_PROVINCE," & vbCrLf
strSQL = strSQL & " COUNTRY_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " CUSTOMERS" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " CUST_FIRST_NAME= ?"
.CommandText = strSQL
.CommandType = adCmdText
.CommandTimeout = 30
.Parameters.Append .CreateParameter("first_name", adVarChar, adParamInput, 20, "")
.ActiveConnection = dbDatabase
End With
comData("first_name") = "Charles"
Set snpData = comData.Execute
If Not (snpData Is Nothing) Then
If snpData.State = 1 Then
If Not (snpData.EOF) Then
grdOutput.Cols = snpData.Fields.Count
grdOutput.FixedRows = 0
grdOutput.FixedCols = 0
grdOutput.Rows = 1
strOut = ""
For i = 0 To snpData.Fields.Count - 1
strOut = strOut & snpData(i).Name & vbTab
Next i
grdOutput.AddItem strOut 'Add the new heading row
grdOutput.RemoveItem 0 'Remove whatever is on the first row
Do While Not (snpData.EOF)
strOut = ""
strOut = strOut & Format(snpData("cust_id")) & vbTab
If Not (IsNull(snpData("cust_first_name"))) Then
strOut = strOut & snpData("cust_first_name") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_last_name"))) Then
strOut = strOut & snpData("cust_last_name") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_gender"))) Then
strOut = strOut & snpData("cust_gender") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_year_of_birth"))) Then
strOut = strOut & Format(snpData("cust_year_of_birth")) & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_marital_status"))) Then
strOut = strOut & snpData("cust_marital_status") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_street_address"))) Then
strOut = strOut & snpData("cust_street_address") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_postal_code"))) Then
strOut = strOut & snpData("cust_postal_code") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_city"))) Then
strOut = strOut & snpData("cust_city") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("cust_state_province"))) Then
strOut = strOut & snpData("cust_state_province") & vbTab
Else
strOut = strOut & vbTab
End If
If Not (IsNull(snpData("country_id"))) Then
strOut = strOut & snpData("country_id") & vbTab
Else
strOut = strOut & vbTab
End If
grdOutput.AddItem strOut 'Add the new data row to the grid control
snpData.MoveNext
Loop
Else
MsgBox "No rows were returned by the query.", vbInformation
End If
Else
MsgBox "The query could not be executed.", vbCritical
End If
Else
MsgBox "The query could not be executed.", vbCritical
End If
dbDatabase.Close
End If
If grdOutput.Rows > 1 Then
grdOutput.FixedRows = 1
End If
Set snpData = Nothing
Set dbDatabase = Nothing
What does the output look like when the above code is executed?
Nice! Now the problem. It is a very bad habit to repeatedly connect to and disconnect from the database, and such a habit may result in a variety of problems, including performance problems. Ideally, the code to connect to the database should be relocated to a different part of the program, possibly the Form_Load subroutine.
—
Added December 7, 2012:
I was able to reproduce the above mentioned problem on a Windows 7 Pro computer when the 11.2.0.3 client was installed (without first installing the 11.2.0.1 or 11.2.0.2 client). Even after importing the above mentioned registry entries, the program (and the VBScript) reported that the “Provider cannot be found”. As mentioned, after verifying that the above indicated Oracle components were installed, I imported the following registry script (save as a plain text file, rename with a .reg extension, then double-click the file), which combines the three above mentioned registry scripts into a single file:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}]
@="OraOLEDB.Oracle"
"OLEDB_SERVICES"=dword:ffffffff
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors]
@="Extended Error Lookup Service"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ExtendedErrors\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\OLE DB Provider]
@="Oracle Provider for OLE DB"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\ProgID]
@="OraOLEDB.Oracle.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\TypeLib]
@="{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.Oracle"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}]
@="Oracle OLE DB Error Lookup"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\InprocServer32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\ProdID]
@="OraOLEDB.ErrorLookup.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3FC8E6E4-53FF-11D2-BB7D-00C04FA30080}\VersionIndependentProgID]
@="OraOLEDB.ErrorLookup.1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\TypeLib\{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}\1.0\win32]
@="C:\\Oracle\\product\\11.2.0\\client_1\\bin\\OraOLEDB11.DLL"
The missing element that I neglected to mention in the original version of this article is that the Oracle 11.2.0.3 Client installer apparently forgets to register the OraOLEDB11.DLL file that is in the Oracle home. The simple work-around for this problem is to manually register the DLL file, with a command similar to the following executed at a Windows command line (replace C:\Oracle\product\11.2.0\client_1 with the correct location of the Oracle client):
regsvr32 C:\Oracle\product\11.2.0\client_1\bin\OraOLEDB11.DLL
—
Edit May 22, 2015:
I have not yet verified whether or not the same issue exists with the 11.2.0.4 client, however that client version may be downloaded here once you have logged into the My Oracle Support website. The above method fixed the problem with the 11.2.0.3 client (even on the Windows 10 beta versions) without any identified side effects.
—
Edit May 11, 2016:
Answering Vani’s comment stating that the ODBC drivers are not available with the 12c client. It is typically necessary to perform a custom install of the Oracle Client or Database for the ODBC drivers to be installed. Because Visual Basic 6.0 is a 32 bit program, the 32 bit Oracle Client (or Database) software must be installed, even if the database itself is running on a 64 bit server in a 64 bit Oracle Database install.
If during the install “Oracle Windows Interfaces 12.1.0.2.0” is selected, you are then able to install the Oracle ODBC drivers – I suggest installing the other items also that are checked in the below picture.
On 64 bit Windows 7.0 and 8.0 you must use the 32 bit ODBC Administrator to add ODBC connections that may be used in Visual Basic 6.0. You will find the 32 bit ODBC Administrator in the C:\Windows\SysWOW64 folder, as shown in the picture below. It has been a couple of months since I last checked, but I think that with Windows 10 32 bit and 64 bit ODBC configuration is setup using just one tool that is available from the Windows Settings applet.
I have always set up System DSNs for ODBC connections to an Oracle database. As shown in the below picture, there are two Oracle database 11.2.0.x ODBC connections configured on this computer.
Charles Hooper's Oracle Notes 
I’d like to THANK YOU VERY MUCH !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
excelent post, very thorough and extremelly helpful
thank you
Anisio Fernandes
Hi, am getting the error “ORA-12154 could not resolve connect identifier specified”when i use conn.ConnectionString = “Provider=OraOLEDB.Oracle;Data Source=” & strDatabase & “; User ID=” & strUserName & “;Password=” & strPassword & “;ChunkSize=1000;FetchSize=100;” instead of conn.ConnectionString = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=” & strHost & “)(PORT=1526))” & _
“(CONNECT_DATA=(SID=” & strDatabase & “))); uid=” & strUser & ” ;pwd=” & strPassword & “;”
i using this codding excel 2010 in VBA i marked microsoft activex library what u mentioned even i verified the regedit files also.the same files in my regediter what u mentioned so am not excute the .reg file what u shared
i dnt know y the error message still triggering even i verify the DB uname pwd schema all r fine i added port number also but i got same error message
conn.Open—>here the cursor showing while debugging like what u said in above
note:-am using excel 2010 and Win 7 os
please help me i am going to mad what i have to do now
“ORA-12154 could not resolve connect identifier specified” is a better error message than the “Provider cannot be found” error message. The ORA-12154 error simply means that the Oracle client could not locate the database to which your code requested to connect. I can reproduce the error in SQL*Plus by asking SQL*Plus to connect to a database that is not defined in the tnsnames.ora file on my computer:
To fix the above error message, I would need to add an entry into the tnsnames.ora file in the correct folder location in the Oracle client’s home for the undefineddb.
On 64 bit versions of the Windows operating system, the situation is potentially more complicated. If the 32 bit version of Excel is installed (the 32 bit version is common even on 64 bit operating systems), you must install the 32 bit Oracle client on the computer. If the 64 bit version of Excel is installed (rather than the 32 bit version), you must install the 64 bit Oracle client on the computer. That requirement for a matched 32 bit or matched 64 bit installation could mean that you will have more than one Oracle client installed on the computer. As such, each client home should have a copy of the tnsnames.ora file with the database connection defined. If you always specify that the Oracle base path should be C:\Oracle, the tnsnames.ora file should be found in this location for the first Oracle client that is installed:
C:\Oracle\product\11.2.0\client_1\network\admin
If you do not know where the Oracle client is installed, open a Windows command prompt and type
For example, you may see something similar to this:
Based on the above, there are four locations where my tnsnames.ora file must be present:
C:\Oracle\product\12.1.0\dbhome_1\network\admin
C:\Oracle\product\11.2.0\client_1\network\admin
C:\Oracle\product\11.2.0\dbhome_3\network\admin
C:\Oracle\product\11.2.0\dbhome_2\network\admin
i using the ora_client 11.2.0
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
Hi Hooper,
i executed the reg edit codding also which you mentioned for win 7 but still am getting the “Provider cannot be found” if i executed the same code in vbs which i use in VBA,my OS is Win 7 enterprise,but i am am getting this error “ORA-12154 could not resolve connect identifier specified” if i executed the same code in VBA(macro)
thank you (advance)
Hi Hooper,
thanks for your reply i solved that issue by using below code
TNS_INFO = “(DESCRIPTION=” & _
“(ADDRESS_LIST=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=prodtst1.sdd.hp.com)” & _
“(PORT=1521)))” & _
“(CONNECT_DATA=(SID=prod_tst)” & _
“(SERVER=DEDICATED)))”
cnxDB.ConnectionString = “Provider=OraOLEDB.Oracle;” & _
“Data Source=” & TNS_INFO & “;” & _
“user id=cressman;” & _
“password=mean2me”
Debug.Print cnxDB.ConnectionString
Hi Raja/Hooper,
My users don’t have Oracle Client Installed in their PCs, will still able to access the database using the code mentioned above.
I am using below Code:
strDatabase = “(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=” & DBHost & “)(PORT=” & DBPort & “)))(CONNECT_DATA=(SID=” & DBsid & “)(SERVER=DEDICATED)));”
dbVMFG.ConnectionString = “Provider=OraOLEDB.Oracle;Data Source=” & strDatabase & “;User ID=” & DBuid & “;Password=” & DBpwd & “;ChunkSize=1000;FetchSize=100;”
Thanks in advance.
Ashok
At a minimum, you would need to install the Oracle Instant Client with ODAC:
http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html
The download for the Oracle Instant Clients is available here:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
Without the regular (or instant) Oracle client, a Windows computer would not know how to resolve the Provider=OraOLEDB.Oracle in the connection string.
i want to connect vb6 with oracle10g in 64 bit…is dat possble ..hlp me out of ds problem, i want to do a project .its urgent
Yes, it is possible to connect VB6 to a 64 bit Oracle Database 10.x database. The key requirement is that you MUST have a 32 bit Oracle client installed on the computer that will run the VB6 generated program, because VB6 generates 32 bit programs. However, you must install and configure a 32 bit Oracle client that is compatible with the version of Windows – the 11.2.0.x Oracle client that I mentioned in this article is probably the best choice for Windows XP and newer.
Hi, I’m able to get the vbs working but in VB6 it still gives me error “Provider cannot be found. It may not be properly installed.”
Loon,
I suspect that you may have the 64 bit Oracle Client (or Database) installed on the computer, rather than the 32 bit Oracle Client which is required by VB6. If both the 32 bit Oracle Client and 64 bit Oracle Client are installed on the computer, the 32 bit Oracle Client probably needs to be listed first in the system PATH variable. Start a Windows command line and enter tnsping with no other parameters, for example:
The above output indicates that I have the 64 bit Client (or Server) installed, and it is listed first in the system PATH variable. To see the system PATH, enter path on the command line. For example:
Also, when testing the vbs script, make certain that you are testing with the 32 version of wscript, for example:
Hi there,
I am having problem in creating file dsn on windows 8 64 bit machine. As I want to run my project developed in vb6 need to be connected with oracle 12c 64 bit database using dsn, but there is no odbc driver found for oracle.
please help me.
thanks in advance.
Vani,
See the May 11, 2016 update that is included in this blog article – that update is specific to your question. The screen captures are from 64 bit Windows 7, but the concepts apply to Windows 8.0 also.
pls help me a vb6 program with oracle 10g as database, using add, edit, delete and seach button, with following fieds: title, author, publisher, year publish & category pls help me
Jeremy,
What you need to do is to think about how the add, edit, delete, and search button commands are translated into standard SQL syntax:
add: INSERT
edit: UPDATE
delete: DELETE
search: SELECT
Once you are able to connect to the Oracle database from VB 6, simply formulate SQL statements to carry out the required tasks, wrap VB 6 code around the SQL statements, and then send the SQL statements to the database (and process the resultset in the case of a SELECT). Ideally, your SQL statements would use bind variables rather than literals (constants) in the SQL statements. Take a look at the examples in the following link – the programming syntax used in an Excel macro is almost exactly the same as in VB 6. The VBS examples are quite similar, except that all variables are considered variants (no declared variable type).
https://hoopercharles.wordpress.com/category/vbs/
Good luck with the assignment.
Hi,
I am trying to run a VB6 program that uses Oracle 9.2 version & I am getting an error” activex cannot create object”.
Dependecy walker shows that dlls- ORA73.dll, KG734.dll, CORE350.dll, CORE35.dll are missing. How do I resolve this erro
Searched Google for CORE35.dll and ORA73.dll, and found a couple of discussion posts from 1998 to 2004. The name ORA73.dll suggests that this application expects to work with the very old 7.3 Oracle Client software, and a few people with Oracle 8.x clients reported that their “new” Oracle client did not include CORE35.dll. This VB 6 program may be using connection functionality that is significantly different from the focus of this blog article. If you have the source code of the VB6 program, you may be able to convert the program to use ADO, the use of which is described in this blog article.
Some (possibly many) older VB6 programs used DAO for database access, rather than ADO – those programs generally are not able to connect to the database unless the computer has the correct DAO drivers installed. From what you posted, I have no idea if this is the cause of the problem. I recall having to install DAO drivers on Windows 7 computers to support some VB 6 database programs that I wrote in 2000. I found this link that seems to provide access to DAO drivers:
https://support.microsoft.com/en-us/kb/239114
You might try using Process Monitor (available from Microsoft’s website) to determine what missing files or registry entries are causing the ActiveX error.