Don't use DAO/ODBC, use ADO and the Oracle OLEDB provider which you
download from Oracle. If you *must* use ODBC, make sure you're
configuring the Microsoft ODBC for Oracle version 2.573.xxx something.
Here is a sample ADO module for a dsnless connection (from a VB
project):
~~~~~~~~~~~~~ BEGIN CODE
Option Explicit
'
' public connection objects
Public conn As ADODB.Connection ' connection object
Public rsA As ADODB.Recordset ' static recordset object (no
updates)
Public rsB As ADODB.Recordset ' dynamic recordset object
' both recordset objects use optimistic locking
' and client side cursors
'
Public Function OpenConn(strUser As String, strPwd As String, strSvc
As String)
' open db connection
Set conn = New ADODB.Connection
'
conn.Provider = ADODB_PROVIDER ' = OraOLEDB.Oracle
conn.Open strSvc, strUser, strPwd
'
End Function
'
Public Function OpenRsA(connLive As ADODB.Connection, strSQL As
String)
' open recordset A, static
Set rsA = New ADODB.Recordset
'
rsA.CursorLocation = adUseClient
rsA.Open strSQL, connLive, adOpenStatic, adLockOptimistic
'
End Function
'
Public Function OpenRsB(connLive As ADODB.Connection, strSQL As
String)
' open recordset B, dynamic
Set rsB = New ADODB.Recordset
'
rsB.CursorLocation = adUseClient
rsB.Open strSQL, connLive, adOpenDynamic, adLockOptimistic
'
End Function
'
Public Function ShutConnections()
' close the connection and
' disinstantiate the connection objects
'
Set rsA = Nothing
Set rsB = Nothing
'
If Not conn Is Nothing Then
conn.Close
End If
'
Set conn = Nothing
'
End Function
'
~~~~~~~~~~~~~ END CODE
On 24 Mar 2005 03:22:24 -0800,
mc******@yahoo.com wrote:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.
I am trying to connect to an Oracle 9 database on a VMS server. Here's
the info:
VMS machine:
-IP Address 192.168.0.250
-Database global name: db1.mcbill
PC:
-IP Address: 192.168.0.202
-Win XP Pro
-Office XP
When I run the linked table manager and then type username, password
and server I get an Odbc call failed error. Here is what I tried:
username: scott
password: tiger
For the server, I've tried the server's IP address, db1, db1.mcbill,
db********@192.168.0.250, etc.
Can anyone help?
BTW, I know this is not a connectivity issue as I can connect from this
PC using Oracle forms, SQLPlus and Toad.
Here is my tnsnames.ora file:
# TNSNAMES.ORA Network Configuration File:
c:\oracle\ora10g\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.mcbill)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)
Thanks.
Bill
