Access 2003 using ORACLE 8 client ODBC error 3146

[self2006]

Hi,

having a problem getting some data from on oracle 9i database using the Oracle 8 ODBC driver. I can create a mdb and link the tables no problem using the DSN I have set up. When I try and access it through access VB I get run-time error 3146 - ODBC Call failed. Any ideas??

Code:
Private Sub CmdTest1_Click()

Dim strsql As String
Dim w_workspace As Workspace
Dim w_database As Database
Dim w_records As Recordset

Const cur_sql = "SELECT CODE, DATE, RATE, ORD FROM table_name WHERE CMPCODE = ""COMPANY"""

Set w_workspace = DBEngine.CreateWorkspace("", "LIVE", "", dbUseODBC)
Set w_database = w_workspace.OpenDatabase("", , dbReadOnly, "ODBC;DSN=LiveBox;UID=LIVE;PWD=TEST")

strsql = cur_sql

'MsgBox strsql
Set w_records = w_database.OpenRecordsed(strsql,dbOpenSnapshot)

While Not w_records.EOF

'process recordset
Wend

w_workspace.Close
w_database.Close
End Sub

 

[SkipVought]

Hi,

Some of my code...

    Set cnn = New ADODB.Connection
    
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT Qty, Current_Op, Location, sf_cc, sf_oper, mach_grp "
    sSQL = sSQL & "FROM FPRPTSAR.MFG_ORDER_INFO "
    sSQL = sSQL & "WHERE PART_ID like '" & Trim(sPN) & "%' "
    sSQL = sSQL & "  And mfg_ord Not like 'M-%' "
    sSQL = sSQL & "  And mfg_ord Not like '%CMO%' "
    sSQL = sSQL & "  And mfg_ord Not like '%MEGA%' "
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

Skip,

_[red][/red]