oracledba.help
Windows

VB6 and Oracle

<- Windows

Overview

The following are the essential steps to use Visual Basic 6 with Oracle 11g\12c on current Microsoft operating systems (Windows 7\2008 etc.). Run all installation programs as Administrator. Interestingly, there are still a huge number of professional developers that have used both VB6 and Visual Studio and feel VB6 is far easier to develop and support. One advantage to using VB6 is that your code will work in Windows 95 to Windows 7\8\10! The VB6 Fact vs. Fiction page is here.

Microsoft have announced that the VB6 run-time is supported in Windows 10. Even the most modest estimates say VB6 will be supported until 2025.

Microsoft announcement of support for VB6 for: Windows 7-10, Windows Server: 2008-2019.

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

Install Oracle InstantClient

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\11.2.0.3\dbhome_1\NETWORK\ADMIN

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
    • Driver: Oracle in OraClient12Home1_32bit
    • Data Source Name: DB01
    • TNS Service Name: DB01
    • User ID: scott
  4. Test Connection

VB6 Installation

  1. Create a zero byte file in the C:\windows directory called: MSJAVA.DLL
  2. Run setup.exe:
    • (x) Install VB 6 Professional Edition
    • Install folder: C:\app\microsoft\vb6
      You must install to a directory path without spaces to avoid numerous design time bugs on new MS OS's.
    • Installation Type: (x) Custom
      • [x] Graphics
  3. System prompts for reboot.
  4. After reboot you are prompted to Install MSDN
    • [x] Install MSDN
    • Provide path to MSDN files.
    • Folder: C:\app\microsoft\msdn
    • Add\Remove:
      • [x] VB Documentation
      • [x] VB Product Samples
      • [x] Platform SDK
      • [x] MSADC Samples

Install Support Files

  • Download and install VB6 SP6
    • Run setupsp6.exe:
  • Use the component checker to confirm you have at least MDAC 2.8 here.

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.

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

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

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
    • [x] Require Variable Declaration
  • Advanced
    • [x] SDI Development Environment

ADO Connection Changes

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

conn.CommandTimeout    = 1000
conn.ConnectionTimeout = 1000

Snippets

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

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.

<- Windows