don't find my message : connect to oracle db (4.2. 19:39)

[b geber]

can anyone tell me, where my message is??



hello,


i want to connect to an oracle database (excel vba)
i always find examples with mysql, can anyone
give me an example for ORACLE db access?
thanks a lot

 

[Tim Williams]

See below for some sample code

Tim.

'####################################
Const USER_ID As String = "xxxxxx"
Const USER_PW As String = "rrrrrrr"
Const DB_ALIAS As String = "ggggggg"

Dim sConnections As String
Dim sSql As String

On Error GoTo haveError

' open connections

sConnections = "User Id =" & USER_ID & _
";Password=" & USER_PW & _
";Data Source=" & DB_ALIAS

Dim cnConnection As New ADODB.Connection
Dim rsData As New ADODB.Recordset

With cnConnection
.Provider = "MSDAORA"
.Connectionstring = sConnections
.Open
End With

sSql = "select myCol from myTable where colId=1"

' Execute query
rsData.Open sSql, cnConnection, adOpenStatic

'get data
If rsData.BOF Or rsData.EOF Then
msgbox "Not found"
Else
msgbox rsData(0).Value
End If

'close connection
rsData.Close
Set rsProjects = Nothing
cnConnection.Close
Set cnConnection = Nothing
'#####################################

Tim.

 

[Jamie Collins]

With cnConnection
.Provider = "MSDAORA"

That is the Microsoft version of the OLE DB provider. When I was using
Oracle a couple of years ago, Oracle's own version was perferred. For
this, among many other connestion string samples, see:

http://www.able-consulting.com/MDAC...roviders.htm#OLEDBProviderForOracleFromOracle

Jamie.

--