Connection to Oracle DB

[dgrimm]

I am looking for a generic vba code to connect to an oracle db. I do not know any details at this time. Any help would be greatly appreciated.

Thanks in advance.

 

[sous2817]

Have a look here and see if you can get what you're looking for:

http://www.connectionstrings.com/

 

[ogo]

Hi you can use Oracle inProcess server , see the code below,

Public objSession As Object
Public objDataBase As Object
Sub ConnectToOracle()
'Create a reference to the OO4O dll
Set objSession = CreateObject("OracleInProcServer.XOraSession")

'Create a reference to database
Set objDataBase = objSession.OpenDatabase("DATABASENAME", "USERNAME/PASSWORD", 0&)
'Note: to get DATABASENAME execute the query select * from global_name
Dim OraDynaSet As Object

'Write the query. Here i am selecting only 2 columns
strSQL = "SELECT NAME,SALARY FROM EMPLOYEE"

Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)

'To know the number of rows returned by the query
RowCount = OraDynaSet.RecordCount
        
        'If there are records retrieved
        OraDynaSet1.MoveFirst
        
        'Loop the recordset for returned rows
        For i = 1 To OraDynaSet1.RecordCount
            'Put the results in different columns in Sheet1
            'Since i am selecting only two columns two field value will be returned
            Sheet1.Cells(i, 1) = OraDynaSet.Fields(0).Value
            Sheet1.Cells(i, 2) = OraDynaSet.Fields(1).Value
        OraDynaSet1.MoveNext
        Next i
End Sub

Thanks,
Ogo

 

[dgrimm]

Thanks sous and ogo....

I really appreciate the help.

Dave

 

[ogo]

Sorry, I have made a mistake in code, i wrote OraDynaSet1 instead of
OraDynaSet in two places

Thanks,

Ogo

 

[ogo]

More details are here http://download.oracle.com/docs/html/B10952_01/o4o00005.htm

Thanks,
Ogo