Hi

I am trying to connect MS access to remote oracle 10g using connection string
__________________________________________________ _____
Problem and Error
__________________________________________________ _____

When the cursor comes to "Set objWkspace" as given below in Code it goes to
"Err_Handler:" Para
And the excat error is
Error : 3001
Invalid Argument

Note - there are no other error - the application stops at first error and I have no problem in connecting using ODBC ( ODBC is working fine)
__________________________________________________ _____
__________________________________________________ _____
TNSNAMES.ORA File has Entry as
__________________________________________________ _____
ORCL10G =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.14.176)(Port = 1546))
    (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl10g)
    )
)
__________________________________________________ _____

__________________________________________________ _____
Code Written as
__________________________________________________ _____

Private Sub Change_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrORCL10G As String


On Error GoTo Err_Handler

'Build up the connection string
                                            
ODBCConnect = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOC OL=TCP)" & _
             "(HOST=192.168.14.176)(PORT=1546)))(CONNECT_ DATA=(SERVER=DEDICATED)" & _
             "(SERVICE_NAME=ORCL10G)));User Id=myUsername;Password=myPassword;"


'Use the DAO Workspace object to make the connection
        Set objWkspace = DBEngine.CreateWorkspace(ODBCConnect, bull, bull, dbUseODBC)

'Need to append it to make it active
        DBEngine.Workspaces.Append objWkspace

'Logon through a connection
     Set objOracleConnect = objWkspace.OpenConnection("objWkspace", dbDriverNoPrompt, False, _
     ODBCConnect)

     Set objRS = objOracleConnect.OpenRecordset("SELECT * FROM EMPLOYEE", _
             dbOpenDynamic, dbExecDirect, dbOptimisticValue)

Err_Handler:
Dim errLoop As Error

For Each errLoop In DBEngine.Errors
MsgBox "Error: " & errLoop.Number & vbCr & errLoop.Description, vbCritical, "Error Message"
Next errLoop

End Sub
__________________________________________________ __________________________________




Pramod Chandra

I don't think you can use DAO to connect to anything other than a Jet datasource. You need to use ADO to connect to Oracle and SQL Server. I use this code to establish a connection to Oracle:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

Set cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
End With
cn.Open "Provider=msdaora;Data Source=OracleServerName.DomainName;User ID=UserID;Password=password;"

sSQL = "SELECT * FROM OracleDatabaseName.TableName"

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

etc.

Did that help any?


mmcdonal

Look it up at: http://wrox.books24x7.com