Connecting to Oracle DB

[bjso]

I am trying to connect to an oracle database from Excel VBA. I have an ODBC connection to the oracle database: ABC. There seem to be soething wrong with that, cause i dont think the code below is wrong(or is it?). Is it possible to do an ODBC connection directly in the code? Help would be really appriciated.

Dim oCn1 As ADODB.Connection
Dim oRs1 As ADODB.Recordset
Dim connection1 As String
Dim szSQL As String

connection1 = "Provider=MSDAORA.1;" & _
"Password=PW;" & _
"User ID=PW;" & _
"Data Source=ABC"

Set oCn1 = New ADODB.Connection
oCn1.Open connection1

szSQL = "SELECT * FROM Table; "

Set oRs1 = New ADODB.Recordset
oRs1.Open szSQL, connection1, adOpenForwardOnly, adLockReadOnly, adCmdText



/Bjso

[brucevde]

Your connection string is using the OLEDB provider and is looking for an Oracle instance named ABC.

The DSN itself indicates which ODBC driver to use and the location of the server. Providing your DSN is setup properly this is all you should require.

connection1 = "DSN=ABC;Password=PW;User ID=PW"

However, unless you have a compelling reason to use ODBC, its recommended to use OLEDB instead.

Just change the Data Source of your original connection string to be the Oracle Instance name and you should be set.

Here is some more information

[bjso]

The Oracle Instance name is A, the alias name is ABC.

If I use the OLEDB provider for oracle:

connection1 = "Provider=msdaora;" & _
"Data Source=ABC;" & _
"User Id=PW;" & _
"Password=PW"

...And the set up in System DSN:

Driver=Microsoft ODBC for Oracle

Data sourcename: ABC
Desc:
Username:PW
Server:ABC

I get the message: Table or view does not exist
If I use A instead of ABC in the string I get the message:TNS could not resolve service name.

Im not sure if Im doing right when I set up the dsn.

/Bjso

[Robbo]

for oracle 8i i use this:-

Public Sub Oracle_Conn()
Dim adoConnect As ADODB.Connection
Dim adoRecordSet As ADODB.Recordset
Dim m_Server as String
Dim m_UID as String
Dim m_UPW as String
Dim m_strSQL as String

' in code, initialize the connection
Set adoConnect = New ADODB.Connection

m_Server = frmConn.txtServer.Text 'textbox with server name"
m_UID = frmConn.txtUID.Text 'textbox with user
m_UPW = frmConn.txtUPW.Text 'textbox with password

Set m_ORAcmd = New ADODB.Command

m_strSQL = _
"data source=" & m_Server & ";" & _
"user id=" & m_UID & ";" & _
"password=" & m_UPW

With adoConnect
.Provider = "MSDAORA"
.CursorLocation = adUseClient
.ConnectionTimeout = 3
.ConnectionString = m_strSQL
.Open m_strSQL, , , -1
End With

Set m_ORAcmd.ActiveConnection = adoConnect


' in code, using recordset
Set adoRecordSet = New ADODB.Recordset

' Prepare the RecordSet
adoRecordSet.CursorType = adOpenStatic
adoRecordSet.LockType = adLockOptimistic

SQL = "SELECT * FROM Table"

adoRecordSet.Open SQL, adoConnect, , adCmdTable

'If No Records Start
If adoRecordSet.EOF = False Then
ReportRecordFound = True
adoRecordSet.MoveFirst
txtCurrent.Text = "1"
End If

dont need the DSN setting up then

[Robbo]

this issue resolved now then, plase change header, cheers