Run-time error 3146 ODBC--call failed encountered from MS Access 2K VB to Oracle(9) ODBC

I have a code generated query being executed from a MS Access 2K VB code segment, while using a SQL Pass Thorugh Query via an ORACLE ODBC connection.

I have several SQL stmts (CRU) all working from the code using the same methodology. When I take the particular insert stmt from the code and run it directly against oracle via SQL Plus, it works as anticipated.

Do you have any suggestions?

Here is the code, note the pass through query is performed in a seperate module:

   Dim Q As DAO.QueryDef
    Dim sSQL As String
    Dim NL As String
    Dim Description As String
    Dim lUserID As Long
   
    NL = Chr(13) & Chr(10)

    Select Case GetUserID()
        Case "foo"
            lUserID = 290
        Case "bar"
            lUserID = 1066
        Case "foobar"
            lUserID = 1958
        Case Else
            lUserID = -1
    End Select
   
    sSQL = ""
    sSQL = sSQL & " insert into" & NL
    sSQL = sSQL & "  inv.mtl_cross_references" & NL
    sSQL = sSQL & "  (inventory_item_id," & NL
    sSQL = sSQL & "   organization_id," & NL
    sSQL = sSQL & "   cross_reference_type," & NL
    sSQL = sSQL & "   cross_reference," & NL
    sSQL = sSQL & "   description," & NL
    sSQL = sSQL & "   last_update_date," & NL
    sSQL = sSQL & "   last_updated_by," & NL
    sSQL = sSQL & "   creation_date," & NL
    sSQL = sSQL & "   created_by," & NL
    sSQL = sSQL & "   last_update_login," & NL
    sSQL = sSQL & "   org_independent_flag)" & NL
    sSQL = sSQL & "  values (" & NL
    sSQL = sSQL & "   " & tbxInventoryItemID & "," & NL
    sSQL = sSQL & "   " & tbxOrganizationID & "," & NL
    sSQL = sSQL & "   " & "'" & cbxBarCodeType.Column(0, cbxBarCodeType.ListIndex) & "'" & "," & NL
    sSQL = sSQL & "   " & "'" & tbxBarCode & "'" & "," & NL
    Description = Nz(tbxDescription, "")
    Description = Replace(Description, "'", "")
    Description = Replace(Description, """", "")
    sSQL = sSQL & "   " & "'" & Description & "'" & "," & NL
    sSQL = sSQL & "   " & "sysdate" & "," & NL
    sSQL = sSQL & "   " & CStr(lUserID) & "," & NL  
    sSQL = sSQL & "   " & "sysdate" & "," & NL
    sSQL = sSQL & "   " & CStr(lUserID) & "," & NL  
    sSQL = sSQL & "   " & "1" & "," & NL
    sSQL = sSQL & "   " & "'N'" & ")" & NL
    Set Q = CreatePassthrough("", sSQL)
    Q.ReturnsRecords = False
    Q.Execute
    Q.Close
 
Public Function CreatePassthrough(sNameBasis As String, sSQL As String) As DAO.QueryDef
    Dim qdf As DAO.QueryDef
    If sNameBasis = "" Then
        Set qdf = CurrentDb.CreateQueryDef("", "select -1 from dual;")
    Else
        Set qdf = CurrentDb.CreateQueryDef(QueryName(sNameBasis), "select -1 from dual;")
    End If
    qdf.Connect = "ODBC;DSN=Oracle ODBC;UID=TEST;PWD=TEST;DBQ=" & PassthroughInstance & ".WORLD;ASY=OFF;"
    qdf.SQL = sSQL
    Set CreatePassthrough = qdf
End Function
ardelkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
Can you show the value of sSQL before this statment.

Set Q = CreatePassthrough("", sSQL)

Leon
ardelkAuthor Commented:

From the immediate window:
?sSQL

 insert into
  inv.mtl_cross_references
  (inventory_item_id,
   organization_id,
   cross_reference_type,
   cross_reference,
   description,
   last_update_date,
   last_updated_by,
   creation_date,
   created_by,
   last_update_login,
   org_independent_flag)
  values (
   61283,
   2,
   'UPC',
   '073950273428',
   '',
   sysdate,
   1066,
   sysdate,
   1066,
   1,
   'N')

leonstrykerCommented:
Is there a particular error message generated?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ardelkAuthor Commented:
run-time error 3146
ODBC call--failed
leonstrykerCommented:
>against oracle via SQL Plus

Try running from Access using Pass through.  Does this work?  You can also try running a hardcoded string from VB to see if it gives you the same error.

Leon
ardelkAuthor Commented:
It is currently being ran using SQLPassThrough.
When I run the query by hand in SQLPlus - it works fine.

Am I missunderstanding, your suggestion?
leonstrykerCommented:
>Am I missunderstanding, your suggestion

Yes, I want you to open Access open a query window then go to Query/SQL Specific/Pass-Through  copy it there and run it.
ardelkAuthor Commented:
it appears to have worked from there.....
why would it work from there and not the code?
ardelkAuthor Commented:
Do you have any other suggestions on how to make it work from the code?
ardelkAuthor Commented:
Here is some more information:
When I connect to the dev or test dbs this code works, but it does not work for the Prod DB.
The query itself works from the sqlPlus command
and from the access SQL specifc pass through test you described.....

I am truly stuck
leonstrykerCommented:
> dev or test dbs this code works

Check the table rights for the user id used with the application.  Do you have Insert rights on that table or is it read only?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ardelkAuthor Commented:
yes. I connected with the user using sqlplus and it succesfully inserts.....
I am going to try an experiement and will let you know the results in a few
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.