How to connect Visual Basic 6 to Oracle 11g

5 pts.
Tags:
Database programming
Oracle 11g
Oracle connectivity
Visual Basic
Visual Basic 6
What are the steps to connect VB 6.0 to Oracle11g? Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could do it this way:

First, you need to install Oracle Client software and make sure you are able to connect to the database (test the connection from SQL*Plus)

Then the VB code is pretty simple. This is an example using ADO:

Dim C As New ADODB.Connection
Dim Rec As New ADODB.Recordset
Dim SQL As String

'Open the connection
C.Open "Driver={Microsoft ODBC for Oracle};Server=your_database_alias; Uid=your_username;Pwd=your_password;"

'Sql command
SQL = "SELECT * FROM your_table where "

'Open the recordset
Rec.Open SQL, C, adOpenForwardOnly, adLockReadOnly

'Process the records
Do Until Rec.EOF
'do something
Loop

Rec.Close
Set Rec = Nothing
C.Close

(your_database_alias is an entry in your tnsnames.ora file and its some times the same as the service name and some times not so be careful to choose the alias not service name)

OR TRY THESE

CONNECTING TO A DATABASE IN VB USING A FUNCTION

Should be written in the module

Public Function Cons() As Connection
Set con = New Connection
With con
.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Users\gadget\Documents\Sql.mdb;Persist Security Info=False”
.Open
Set Cons = con
End With

Connection to a computer on the network
‘Conns.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\192.168.168.3\Users\Public\Documents\Database1.mdb;Persist Security
Info=False”

Conns.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”
& App.Path & “\” & “KisumuPolyTTable.mdb;Persist Security Info=True;
jet OLEDB:Database Password=COMPUTER” ‘jet OLEDB:Database
Password=computer

End Function
Should be written in the module

Public Function OpenRecs(Sql As String) As Recordset
Set rst = New Recordset
With rst
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.ActiveConnection = Cons
.Source = Sql
.Open
End With
Set OpenRecs = rst
Exit Function

End Function

Centring a Form
Should be written in the module
Public Sub center(frm As Form)
With frm
.Top = ((Screen.Height / 2) – .Height / 2) – 1000
.Left = ((Screen.Width / 2) – .Width / 2) – 10
End With
End Sub

Sample Form
Private Sub Form_Load()
Center Me
With OpenRecs(“Select * from trial2″)
Text1.Text = !Name
Text2.Text = !Course
End With
End Sub

Mysql server connection module
Option Explicit
Public con As ADODB.Connection
Public rstStudent As ADODB.Recordset
Public username As String
Public pass As String
Public serverIp As String
Public db As String
Public sql As String

Public Function connectMysql(username As String, pass As String, serverIp As
String, db As String, conn As ADODB.Connection, rs As ADODB.Recordset)

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = “DRIVER={MYSQL ODBC 5.1 driver}; SERVER=” &
serverIp & “; UID=” & username & “; PWD=” & pass & “; DATABASE=” & db & “;
OPTION=” & 1 + 2 + 8 + 32 + 2048 + 163841 & “”
conn.Open

End Function

************IGNORE THESE CODES   ***********
Calling the connection Function from the FORM

serverIp = “127.0.0.1″ ‘LocalHost IP Address
Call connectMysql(“root”, “Sylvia”, serverIp, “Admission”, con, rstStudent)
sql = “select * from PersonalDetail”
rstStudent.Open sql, con

Call JazaForm
***********************************8

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rupeshit
    [...] How to connect Visual Basic 6 to Oracle 11g [...]
    0 pointsBadges:
    report
  • Rupeshit
    [...] How to connect Visual Basic 6 to Oracle 11g [...]
    0 pointsBadges:
    report
  • Yeahyow
    I already tried your code that you posted but there's an error. Can you please help me?
    40 pointsBadges:
    report
  • carlosdl
    Yeahyow, if you are getting errors, please post the error messages.
    69,835 pointsBadges:
    report
  • Yeahyow
    The error message is this.. Compile error: User-defined type not defined Can you help me please to do my work? Thanks.
    40 pointsBadges:
    report
  • Yeahyow
    Are you a Filipino Sir? I'm using Oracle 11g and Benthic Golden32 and I need to connect my database to VB 6. In my VB 6 FORM, I named it INSTITUTION FORM. There's 3 text boxes and also 3 labels. In the 1st Label, I named it INSTITUTION NAME, 2nd INSTITUTION NUMBER, and the 3rd is INSTITUTION LICENSE and there's 2 command buttons for PREVIOUS and NEXT.
    40 pointsBadges:
    report
  • carlosdl
    Hi Yeahyow. No, I'm not Filipino. That's not an error related to the database connection. I would recommend to create a new question explaining your problem with enough details. You should post the complete error messages you get, and your code. Best,
    69,835 pointsBadges:
    report
  • Yeahyow
    C.Open "Driver={Microsoft ODBC for Oracle};Server=omniqa_loc;Uid=bw3;Pwd=bw3data;" In this part (when I run it), the message error is this: Compile error: Invalid outside procedure Also, this part of: "Driver={Microsoft ODBC for Oracle};Server=omniqa_loc;Uid=bw3;Pwd=bw3data;" was highlighted in the color blue.
    40 pointsBadges:
    report
  • JohnKennedy
    Hi, u can try the sample codes i av posted above but during coding u will meet alot of errors so u need to specify what kind of error. And while coding do not embed errorhandler statement.
    415 pointsBadges:
    report
  • JohnKennedy
    I av not formatted the sample codes. I prefer that style of vb coonection bs it's very simple and learning it is also easy. Try the one u feel is easy to adapt and do post the errors u encountered. Errors like user -defined could mean that u have dim all the variables. So try placing "Option Explicit" in the first line before u start coding any form to identify for u any variable u have used but haven't define yet................
    415 pointsBadges:
    report
  • JohnKennedy
    Always avoid using the password for the Dbase. You can have tblEmployees for the user with password who have the privileges to login
    415 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following