vb6 and oracle-quick help plzzzzzz

[pangolin_10]

hi everybody,
i have ended up in problem when i tried to shift from Vb and Access to VB and oracle. I don't really know how to connect VB6 to an Oracle database but i need to know. can anyone help me.

specify plzzz-
1-where is the oracle database stored when i use sql to create tables and views?
2-how do i access them using VB6

plzzzzzzzzzzzz reply fast...

[powerfull_king]

Hey,

I was using System DSN before then for some reasons i decided to start using File DSN, i still don't know if this is the best approach.

You create your System DSN or File DSN.

Then you can place the code below in a module if you want:

Dim dbConnect As Connection
Dim USERNAME, PASSWORD As String

'This Function starts connection to the database
Public Function StartConnection()
    Set dbConnect = New ADODB.Connection
    With dbConnect
        .ConnectionString = "FileDSN=" & App.Path & "\resources\YourDsnFile.dsn;UID=" & USERNAME & ";PWD=" & PASSWORD & " "
        .CursorLocation = adUseClient
        .Open , , , adAsyncConnect
    End With
End Function

'This one ends the connection
Public Function EndConnection()
    Set dbConnect = Nothing
End Function

'This one gets the connection
Public Function GetConnection() As String
    GetConnection = dbConnect
End Function

'Setting the username
Public Function SetUsername(aString As String)
    USERNAME = aString
End Function

'Setting the password
Public Function SetPassword(aString As String)
    PASSWORD = aString
End Function

In case you want the database connection to start when the form loads and end when the form unloads, then, put the code below in your form load:

Private Sub Form_Load()
    'First set the username and password then call the StartConnection function.
    SetUsername ("ANORACLEUSERNAME")
    SetPassword ("ANORACLEPASSWORD")
    StartConnection
End Sub

Private Sub Form_Unload(Cancel As Integer)
    'First End the connection then end the process
    EndConnection
    End
End Sub

I consider the form above as a MDI Form or Main form that is loaded from the beginning to the end.
If you want a user to start searching when he/she clicks on the search button of any of your forms in the application, then:

Private Sub cmdSearch_Click()
    Dim rsSearch As ADODB.Recordset

    Screen.MousePointer = vbHourglass
    
    Set rsSearch = New ADODB.Recordset
    rsSearch.Open"SELECT * FROM ATABLE", GetConnection
    
    While Not rsSearch.EOF
        'Do anything you want
        rsSearch.MoveNext
    Wend
    
    rsSearch.Close
    Set rsSearch = Nothing
    
    Screen.MousePointer = vbDefault
End Sub

As you can see in the code above, you can call the GetConnection as you've already set it.

This might not be the best approach but you've got something to start with.
Hope This help.

Best of luck.
Powerfull King.

[pangolin_10]

i got the logical part,but about the connection establishment, i hav a few questions--

1->i used to use 'Microsoft ActiveX Data Objects 2.0 Library' from under the references tab when i used Access as the back-end database. But now do i have to use any reference when i am using an oracle database?
2->i could connect to the oracle database using ADODC and its built in connections ,But as i tried to use an ADODB connection, i got the recordcount of a particular table as -1.What is the reason??

anyways, many thnx for your help but plzzz help me again...

[HairyMonkeyMan]

1> You need to use the ADO to connect to any database (you can also use DAO or RAO). You could remove the reference, and instead of :

Set dbConnect = New ADODB.Connection

you would use

Set dbConnect = CreateObject("ADODB.Connection")

You would lose those object browser menus, but the code should execute slightly faster.

2> The recordcount property is not always reliable. Try doing the following before looking at the recordcount :

RecordSet.MoveLast
RecordSet.MoveFirst
MsgBox RecordSet.RecordCount

hth