oracledba.help
Windows

VB6 and Oracle

<- Windows

Overview

The following are the essential steps to use Visual Basic 6 with Oracle 19c\20c on current Microsoft Windows operating systems. Run all installation programs as Administrator.

VB6 Fun Facts!

  • Microsoft announcement of support for VB6 for: Windows 7-11, Windows Server: 2008-20nn.
  • With Devart's Oracle ODBC Direct option you can connect to any database without 32bit configuraion issues on 64bit systems.
  • Newer Microsoft OS's already have MDAC 2.8 installed as per the Component Checker.
  • RAD Basic may be the future of VB6.

Install Oracle InstantClient

You dont need to do this if you are using Devart's ODBC Driver.

Prerequisite

  • Download Oracle 12c 32-bit InstantClient (click See All).

Procedure

 1. Unset your ORACLE_HOME variable.
 2. Run setup.exe for InstantClient.
 3. (x) InstantClient Software
 4. Software Location
    Path: c:\app\oracle\product\12.1.0.1\ic32
 5. Install
 6. Close
 7. Reset your ORACLE_HOME variable.
 8. For some environments you may need to set your TNS_ADMIN environment variable. 
Example: c:\app\oracle\product\12.1.0.1\dbhome_1\NETWORK\ADMIN

Set 12.1.0.1 to match your install version.

Configure\Test ODBC

 1. Create an Oracle TNS service entry for your database (ex: DB01).
 2. Run the 32bit ODBC Admin
    C:\Windows\SysWOW64\odbcad32.exe
 3. System DSN -> Add
    a. Driver: Oracle in OraClient12Home1_32bit
    b. Data Source Name: DB01
    c. TNS Service Name: DB01
    d. User ID: scott
 4. Test Connection

If using Devart's Oracle ODBC go here.

VB6 Installation

Prerequisites

 1. Create a zero byte file in the C:\windows directory called: MSJAVA.DLL
 2. Mount VB6.iso and copy to local dir (C:\sw\vb\vb6).
    You will run the setup.exe from the locally copied dir.

VB6 Process

 1. Run setup.exe:
    a. End User Lic Agreement
       ⮿ I accept...
    b. Product Number and User ID.
       CD Key : nnn-nnnnnnn
       Your name: Administrator
    c. Visual Basic 6.0 Professional Edition
       ⮿ Install VB 6 Professional Edition
    d. Install folder: C:\app\vb6
       Install to a directory path without spaces to avoid  
       numerous design time bugs on new MS OS's.
       [Continue]
    e. Choose: ⮾ Custom installation.
    f. Data Access Option changes:
       Select Data Access 🠊 Change Option then uncheck:
       ☐ ADO, RDS, and OLE DB Providers

       If displayed, uncheck:
       ☐ Visual Studio Analyzer
       ☐ Microsoft Visual InterDev 6.0
       [OK]
     g. Enable
        ⮽ Graphics
        [OK] Process runs...
 2. System prompts for reboot.
 3. After reboot you may be prompted to Install MSDN.
    If so, unselect ☐ Install MSDN then select [Exit].
    You will do this manually next.

MSDN Process

 a. Mount and\or copy MSDN files to local dir.
    Example: C:\sw\VB\utils\MSDN
 b. From extracted files run setup.exe.
 c. When displayed, select [Custom] then:
    Add: 
       ⮽ Full Test Search Index
       ⮽ VB Documentation
       ⮽ VB Product Samples
       ⮽ Platform SDK Documentation
       ⮽ MSADC Samples
    [Continue] Process runs...and will display when completed.

Install Support Files

Install VB6 support files and tools.

 1. 64bit Component Checker
    a. The 64bit component checker.
       Folder: C:\app\CompChecker\
       ⮾ Everyone
    b. Once installed run it to see MDAC version.
       Select highest matching OS type: WinXP SP2 or WinSrv 2003 SP2.
       Newer OS's will probably already have MDAC 2.8 installed.
 2. Install Devart's Oracle ODBC Driver.

Essential Environment Changes

Right-click on C:\...\MS Visual Studio\VB98\VB6.exe and set properties as so:

  • Run program in compatibility mode for Windows XP (highest SP listed).
  • Disable visual themes.
  • Disable desktop composite.
  • Disable display scaling on high DPI settings.
  • Enable: Run this program as Administrator

OR ... simply create a VB6 development environment in a Windows XP Virtual Box. ;-)

RAD Project Creation

Implementing this here ensures the following configuration steps are in place for all future projects. Also, is really useful for pumping out standardized\solid applications.

  • Create a new project with all the components and settings you prefer. Maybe as _Default
  • Each time you create a new project:
    • Copy the _Default directory to a new directory and change the new directory name to match project.
    • Change the name of the .vbp and .vbw to you new directory name.
      The .vbw file can be deleted too (it is recreated).
    • Start new project by double-clicking on the new .vbp file name.
    • Change the Project -> Project Properties as required.
    • Change .exe Name From Default

You can change the name of your .exe when you compile it the first time from the File-> MyExe.exe menu option. A Make Project window appears. Set the new name in the File Name field. From that point on all further .exe will have the new name.

Incremental Builds

This is useful for deployment and version tracking. Use Revision as the build number.

  • Enable Auto Increment.
    • Project -> Project Properties -> [Make Tab] [x] Auto Increment
    • Example code snippet:
app.Major & "." & app.minor & " (" & app.revision & ")"

Miscellaneous Changes

Options

  • Editor
    • Tab width = 3
    • ⮽ Require Variable Declaration
    • ☐ Default to Full Module View
    • ☐ Procedure Spearator
  • Advanced
    • ⮽ SDI Development Environment

Package and Deployment (PD) Wizard

This is required to ensure the Package and Deployment Wizard uses the latest MDAC.

  1. If need be, run MDAC so current OS set (MDAC_TYP.exe).
    • Use the component checker to confirm here.
  2. Replace MDAC_TYP.exe used by the PD Wizard with the latest file.
    • If need be get latest file here.
    • File location: C:\Program Files\Microsoft Visual Studio\VB98\Wizards\PDWizard\Redist

Snippets

ADO Connection Changes

For long running queries you made need to bump the connection related values to avoid timeouts and ORA-01013 errors.

conn.CommandTimeout    = 1000
conn.ConnectionTimeout = 1000

ADO Connection Snippet

The following snippet will allow you to connect to Oracle. Change the Oracle ConnectionString to match your environment.

 'Initialize Variables
 Dim oConn As New ADODB.Connection
 Dim oRS As New ADODB.Recordset
 Dim sSQL As String

 'Connect to Database
 oConn.ConnectionString = "DSN=DB01;UID=x15;PWD=x15"
 oConn.Open

 'Create RS and Get Data
 sSQL = "SELECT sysdate FROM dual;"
 Set oRS = oConn.Execute(sSQL)
 MsgBox oRS.Fields(0).Name & " = " & oRS(0)

 'HouseKeeping
 oRS.Close
 oConn.Close
 Set oRS = Nothing
 Set oConn = Nothing

OR as SysDBA oConn.ConnectionString = "DSN=DB01;UID=x15;PWD=x15 as sysdba;"

ADO DSNless Connection Snippet

   'Initialize Variables
   Dim oConn As New ADODB.Connection
   Dim oRS As New ADODB.Recordset
   Dim sSQL As String
   Dim sOracleDriver, sOraclePort, sOracleHost, sOracleService
   Dim sOracleUsername, sOraclePassword As String
   sOracleDriver = "Driver={Oracle in OraDb11g_home1};"
   sOraclePort = "1521"
   sOracleHost = "localhost"
   sOracleService = "DB42"
   sOracleUsername = "X15"
   sOraclePassword = "x15"

   'Connect to Database
   oConn.ConnectionString = sOracleDriver & "CONNECTSTRING=(DESCRIPTION=" & _
                           "(ADDRESS=(PROTOCOL=TCP)"      & _ 
                           "(HOST=" & sOracleHost & ")"   & _
                           "(PORT=" & sOraclePort & ")))" & _
                           "(CONNECT_DATA=(SERVICE_NAME=" & sOracleService & "))" & _ 
                           "UID=" & sOracleUsername & ";PWD=" & sOraclePassword   & ";"
   oConn.Open

ADO To .CSV File

 'Create a DSN for {Microsoft Text Driver} as "TEXT" to dir of file.

 'Init Vars
 Dim oConn As New ADODB.Connection
 Dim oRS As New ADODB.Recordset
 Dim sFile As String
 Dim sSQL As String

 'ADO Connection to File
 sFile = "customers.txt"
 oConn.ConnectionString = "DSN=TEXT"
 oConn.Open

 'Create RS and Get Data
 sSQL    = "SELECT * FROM " & sFile
 Set oRS = oConn.Execute(sSQL)

 'Process
 While Not oRS.EOF
    MsgBox oRS(0)
    oRS.MoveNext
 Wend

 'HouseKeeping
 On Error Resume Next
 oRS.Close
 oConn.Close
 Set oRS = Nothing
 Set oConn = Nothing

ADO Data Control

Essential Properties

Form Paint() Example

 Screen.MousePointer = vbHourglass
 Adodc1.ConnectionString = "DRIVER=Devart ODBC Driver for Oracle;Direct=True;
                            Host=lnx01;Service Name=MyDB;
                            User ID=scott;Password=tiger"
 sSQL = "SELECT * FROM emp ORDER BY LastName ASC"
 Adodc1.RecordSource = sSQL
 Adodc1.Refresh
 Screen.MousePointer = vbDefault

The following controls can be used via DataSource (Adodc1):

  • Data Grid
  • Via DataField: TextBox, DBList, DBCombo

Form Unload()

 Adodc1.Recordset.Close
 Set frmMain = Nothing

Disabling UAC Prompt for a VB6 Application

 Create Scheduler Task
 1. Click Start, right click on Computer and choose "Manage".
 2. Click "Task Scheduler" on the left panel.
 3. Click "Create Task" on the right panel.
 4. Type a name for the task.
 5. Check "Run with highest privileges".
 6. Click Actions tab.
 7. Click "New".
 8. Browse to the program in the "Program/script" box. Click OK.

 Create Shortcut to Scheduler Task
 1. On desktop, right click, choose New and click "Shortcut".
 2. In the box type: schtasks.exe /run /tn TaskName 
    Where TaskName is the name of task you put in on the basics tab and click next.
 3. Type a name for the shortcut and click Finish.

Extract SP6 Files

With this you can manually copy and extract OCX files to c:\windows\system32

 msiexec /a vb60sp6-kb2708437-x86-enu.msi /qb targetdir=C:\sw\vb6\sp6-ext

Copying the OCX file:

  • For Windows 64-bit systems, extract the OCX file to: C:\Windows\SysWOW64
  • For Windows 32-bit systems, extract the OCX file to: C:\Windows\System32

Register the OCX file:

Right-click Start, click Command Prompt (Admin)

If you're using Windows 32-bit, type the following command and press ENTER:

 regsvr32 comdlg32.ocx

If you're using Windows 64-bit, type the following command and press ENTER:

 C:\Windows\SysWOW64\regsvr32   C:\Windows\SysWOW64\comdlg32.ocx

Links

These are useful to give your VB6 app a modern look:

Obsolete Changes

Already functionally implemented on newer Windows OS's.

Add Reference for ADO

  1. From the Project menu select References.
  2. Select: Microsoft ActiveX Data Objects 2.n Library (used highest number ex: 2.8)

Components

These are common components used in most applications. Add these from the Project -> Components menu.

  • MS ADO Data Control 6.0 (OLEDB)
  • MS Common Dialog Control 6.0
  • MS Data Bound List Controls 6.0 (OLEDB)
  • MS DataGrid Control 6.0 (OLEDB)
  • MS DataList Controls 6.0 (OLEDB)
  • MS FlexGrid Control 6.0
  • MS Tabbed Dialog Control
  • MS Windows Common Control 6.0

If the install instructions are used on this page these should all already be selected.

<- Windows