oracle link

i need to make an application to access my oracle database
i am new to vb4 and need help with ODBC maybe someone could help me to simply run any sql query in a simple form so i can get the idea ??? or maybe that`s too much to ask :) ... thanks
mattwarrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

twardCommented:
One of the easiest  ways to connect to Oracle is through the Oracle Objects for Ole, here is some code that I use in order to connect to a database.  If you need any clarification just let me know.

  Option Explicit

  ' Oracle setup '
  Public OraSession As Object
  Public oraDatabase As Object

  ' Oracle Connect String (Used in DatabaseOpen) '
  Public CONNECT$

  ' Oracle Database String (Used in DatabaseOpen) '
  Public DATABASE$

  ' Oracle SQL String (Used in OpenDynaset) '
  Public SQL$

  Public DynaSetOpenError As Boolean

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseOpen(CONNECT$,DATABASE$)  '
'                                                 '
' Description:  Opens the Database Passed to it   '
'               in DATABASE using the             '
'               NAME/PASSWORD in CONNECT.         '
'                                                 '
'       Input:   CONNECT$ - "NAME/PASSWORD"       '
'               DATABASE$ - "X:ORA_DB_SRV:ORCL"   '
'                           "PROD"                '
'                                                 '
'      Output:  Message Box on Error, otherwise   '
'               an Open Database Object.          '
'                                                 '
'     Returns:   True = Database was Opened.      '
'               False = Database Open Failed.     '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DatabaseOpen(ByVal CONNECT$, ByVal DATABASE$) As Boolean
 
  DatabaseOpen = True
 
  ' Setup The Oracle Session '
  On Error Resume Next
  Set OraSession = CreateObject("oracleinprocserver.xorasession")
  If Err.Number <> 0 Then
   
    MsgBox "Oracle Error:  " & Err.Description, vbOKOnly + vbCritical, "Database Session Error..."
    DatabaseOpen = False
    On Error GoTo 0
    Exit Function
   
  End If
 
  ' Setup The Oracle Connection '
  On Error Resume Next
  Set oraDatabase = OraSession.dbopendatabase(DATABASE$, CONNECT$, 0&)
 
  If Err.Number <> 0 Then
   
    MsgBox "Oracle Error:  " & Err.Description, vbOKOnly + vbCritical, "Database Connection Error..."
    DatabaseOpen = False
   
  End If
 
  On Error GoTo 0
 
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseClose()                   '
'                                                 '
' Description:  Closes an Open Oracle Database.   '
'               Database should have been opened  '
'               using OpenDatabase.               '
'                                                 '
'       Input:  NONE.                             '
'                                                 '
'      Output:  Clears OraSession and oraDatabase.'
'                                                 '
'     Returns:  NONE.                             '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub DatabaseClose()
 
  Set OraSession = Nothing
  Set oraDatabase = Nothing

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  OpenDynaset(SQL$)                 '
'                                                 '
' Description:  Opens a Dynaset using the SQL$    '
'               SQL Statement passed to it.       '
'                                                 '
'       Input:  SQL$ - Any valid SQL Statement.   '
'                                                 '
'      Output:  An Open Dynaset, Use CloseDynaset '
'               to Close Dynasets.                '
'                                                 '
'     Returns:  The OpenDynaset Object.           '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function OpenDynaSet(ByVal SQL$) As Object
 
  On Error Resume Next
 
  DynaSetOpenError = False
 
  Set OpenDynaSet = oraDatabase.DBCREATEDYNASET(SQL$, 0&)
 
  If Err.Number <> 0 Then
 
    Call MsgBox("Error #" & Err.Number & ":  " & Err.Description, vbCritical + vbOKOnly, "SQL Query Error.....")
    DynaSetOpenError = True
   
  End If
  On Error GoTo 0
 
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  CloseDynaSet()                    '
'                                                 '
' Description:  Closes the Open Dynaset.          '
'                                                 '
'       Input:  None.                             '
'                                                 '
'      Output:  None.                             '
'                                                 '
'     Returns:  Nothing into the DynaSet Object.  '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function CloseDynaSet() As Object

  Set CloseDynaSet = Nothing

End Function

Public Sub btnStart_Click()

  Dim DocumentDynaset As Object

  If DatabaseOpen("NAME/PASS", "DATABASE") = False Then
   
    End
 
  End If
 
       Set DocumentDynaset = OpenDynaSet("SELECT * FROM DOCUMENT WHERE CONCAT(PREFIX,DOCNO)='" & DocId & "'")
       
       If DocumentDynaset.Fields("NRPAGES").Value <> 0 Then
       End If
       
      Set DocumentDynaset =  CloseDynaset
      DatabaseClose

End Sub

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mattwarrAuthor Commented:
thanks very much for your excellent answer ... now i just need to know how to run the thing ... and see what it does .. i`m gonna start a new project and paste the code in general ... see what happens .. thanks again :)
0
mattwarrAuthor Commented:
sorry tward i think you went to a lot of trouble with this answer but i am unable to inplement the code you gave ... i pasted it into general desc in a new form .. but cant make it do anything ...sorry for being so lame .. please explain further if you can
0
twardCommented:
You need to paste it into a Module, not a form.  Also you need to install Oracles Objects for OLE.

I can send you the module via Email if that would be easier then all you have to do is add it to your project.

Send me email requesting the ORACLE.BAS file and I'll send it to you.

tward@msii.com

After you receive the module include it in your project and then use the stuff in the btnStart_Click event to actually connect to the database and open a Dynaset.

If you still have problems as always just post some of the code that you are using here and I'll try to help you out the best I can.  

I have about 30 different programs that use the same ORACLE.BAS to connect to an Oracle 7 database via Objects for OLE v2.0.


0
mohd130Commented:
Dear tward

Please if you can help me by sending the oracle.bas to e-mail : mohd@aus.ac.ae

also if you can aswer my question:

http://www.experts-exchange.com/jsp/qShow.jsp?ta=vbdatabases&qid=10230212 
Thanks alot
Moh
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.