Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC call failed error (AXP)

    Hello all,
    I am trying to run an append query within a form. When I enter a static value for the lookup it works just fine. However, when I enter a field name in the criteria box in the query and try to run it, I get an ODBC call error. What can I do to solve this apparent timing issue? Or is this a limitation of the software?

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed error (AXP)

    Are you appending to a table that is linked by ODBC, or is ODBC a red herring?
    By "enter a field name", do you mean a field in the query, or a reference to a control on the form (for example [Forms]![frmMyForm]![txtMyControl])?

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC call failed error (AXP)

    Thanks Hans,
    The ODBC is SQL Server and I am using that as a look up only, I am not writing data to that DB. I am using the field in a query example listed below. If I put a static value like 221290 in that criteria box, it works flawlessly. However, if I put "[forms]![wo_update]![text504]" (which has the value 221290 in it) it gives the ODBC call failed message. ANy ideas?

    Thanks,
    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed error (AXP)

    Hello Mark,

    This is outside my experience, but I suspect that the problem is that the ODBC driver doesn't know about Access forms. I would try to construct the SQL string for the query in code, and put the value from the form in as a literal value:

    Dim strSQL As String
    strSQL = "INSERT INTO tblMytable (Field1, Field2) SELECT tblSomething.Field1, tblElse.Field2 " & _
    "FROM tblSomething INNER JOIN tblElse ON tblSomething.ID = tblElse.ID " & _
    "WHERE Field3 = " & Forms!wo_update!text504
    CurrentDb.Execute strSQL

    The example is nonsense, it is just meant to demonstrate how the expression from the form is kept outside the quotes, so that its value is used.

    Others may have different explanations and suggestions.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC call failed error (AXP)

    Thanks again,
    Will that currentdb.execute statement run the query just like using the docmd.openquery command?

    Thanks,
    Mark

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed error (AXP)

    CurrentDb.Execute uses the Jet engine directly instead of going through the Access interface, so you won't get prompted whether you want to append 37 records. If you would like the user to be prompted, use DoCmd.RunSQL strSQL. See also the thread starting at <post#=304701>post 304701</post#>.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC call failed error (AXP)

    OK, when I run this it gives me a RTE 3061, Too few parameters. Expected 1. Any ideas why it may be happening? I pasted the SQL string right from the query design and fixed it up accordingly.

    Dim strSql As String
    strSql = "INSERT INTO Parts_WO ( part_wo_id, Part_Line_Item, Part_Description, " _
    & "Part_so_number, Part_Number, Part_Qty )SELECT workorders.wo_id, " _
    & "[SO Detail].DELNUM_28, [Part Master].PMDES1_01, [SO Detail].ORDNUM_28, " _
    & "[Part Master].PRTNUM_01, [SO Detail].SHPQTY_28 FROM " _
    & "(workorders INNER JOIN [SO Detail] ON workorders.wo_So_number = " _
    & "[SO Detail].ORDNUM_28) INNER JOIN [Part Master] ON [SO Detail].PRTNUM_28 = " _
    & "[Part Master].PRTNUM_01 WHERE ([SO Detail].ORDNUM_28= " _
    & "[forms]![wo_update]![text504]);"
    CurrentDb.Execute strSql

    Thanks,
    Mark

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC call failed error (AXP)

    As I explained, I deliberately put the reference to the text box outside the quotes, so that it will be resolved to its value before it is being used. You have put the reference to the form inside the quotes again, that causes the error. Replace

    & "[forms]![wo_update]![text504]);"

    by

    & [forms]![wo_update]![text504] & ");"

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC call failed error (AXP)

    Makes perfect sense now!

    Thanks again,
    Mark

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC call failed error (AXP)

    OK Hans,
    I have it working for the most part now. I will refer to the other post you mentioned about the error suppression.

    Thanks,
    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •