Results 1 to 9 of 9
  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/Win2K)

    Long story short....
    Converted BE data file to SQL. It is now running on a separate server. Linked all tables in the FE to the new ODBC connection on the SQL server. I can open the tables in the FE file so I know that the ODBC connection is working. Now the problem: When I try to create a new record with a form I get a "ODBC call failed" error. All of this code has worked fine for years using Access. Here is the code:

    Dim intIncID As String
    Dim db1 As Database, db2 As Database
    Dim rstNewInc As Recordset, strCust As String, rstEquipStatus As Recordset
    Dim strNewIncQry As String, strEquipment As String
    Dim strNotes As String, strNewNotes As String, strTimeLog As String

    Set db1 = CurrentDb
    Set rstNewInc = db1.OpenRecordset("incidents")


    rstNewInc.AddNew
    rstNewInc!Incident_Customer_ID = Me.cboCustName.Value
    rstNewInc!Incident_Originator = intLoginID
    rstNewInc!Incident_Contact = Me.cboContact
    rstNewInc!Incident_Contact_Phone = Me.Incident_phone
    rstNewInc!Incident_Contact_FAX = Me.txtIncCustFAX
    rstNewInc!Incident_Contact_Email = Me.txtEmail
    rstNewInc!Incident_Contact_Pager = Me.txtPager
    rstNewInc!Incident_Call_Type = intCallNum
    rstNewInc!Incident_DateIn = Now()
    rstNewInc!Incident_Equipment_ID = Me.Text140
    rstNewInc!Incident_Equipment_SN = Me.txtIncEquipSN
    rstNewInc!Incident_Situation = Me.Incident_Situation
    rstNewInc!Incident_fault_code = Me.Combo36
    rstNewInc!Incident_fault_description = Me.Combo36 + "-" + Me.Text38
    rstNewInc!Incident_CustomerPO = Me.txtIncPO
    rstNewInc!Incident_CustomerPOLimit = Me.txtIncPOLim
    rstNewInc!Incident_PartsPO = Me.txtIncPartPO
    rstNewInc!Incident_status = "New"
    rstNewInc!record_base = strRegion

    If Me.Text111.Value > "" Then
    rstNewInc!Incident_TS_Assignment = Me.Text111.Value
    ElseIf Me.Text111.Value = "" Then
    rstNewInc!Incident_TS_Assignment = strLoginId
    ElseIf IsNull(Me.Text111) Then
    rstNewInc!Incident_TS_Assignment = strLoginId
    End If

    Update:
    'Me.Refresh
    rstNewInc.Update
    rstNewInc.MoveLast
    intIncID = Nz(rstNewInc!Incident_ID, 0)
    Me.Text114 = intIncID
    Me.Repaint

    It dies on the rstNewInc.update line. Does anybody have any ideas here?

    Thanks,
    Mark Santos

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Call Failed Error (AXP/Win2K)

    Try :
    Set rstNewInc = db1.OpenRecordset("incidents",dbOpenDynamic)

    In ODBC the default is dbOpenForwardOnly, wich youcan't update
    Francois

  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/Win2K)

    Thanks!

    I entered that one and now it gives "Invalid Argument" on that line. Any other ideas?

    Thanks,
    Mark

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Call Failed Error (AXP/Win2K)

    I've just done some testing.
    First I have add a References to Microsoft DAO 3.6 Object library.

    Dim db1 as DAO.Database
    Dim rstNewInc as DAO.Recordset
    Set db1 = CurrentDb
    Set rstNewInc = db.Openrecordset ("incidents", dbOpenDynaset, dbSeeChanges)

    That seems to work.
    Francois

  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/Win2K)

    Hi Francois,
    Thanks. With those changes Iam back to the ODBC call error. If I change the opendynaset to opendynamic I get the invalid argument error instead. I am at a loss.

    Thanks,
    Mark

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Call Failed Error (AXP/Win2K)

    Did you add the DAO in the references and in the dim statements?
    Francois

  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/Win2K)

    Hi Francois,
    Yes I checked to make sure that the DAO 3.6 was in the references as well as your example exactly (except db had to be db1 in one place). Do you have any other ideas?

    Thanks,
    Mark Santos

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC Call Failed Error (AXP/Win2K)

    Can you run an append query in Access to add a record like that to the SQL Server table? If not, then you're going to have to resort to stored procedures.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ODBC Call Failed Error (AXP/Win2K)

    We haven't had any experience using AXP with ODBC, but there are a couple of possibilities based on our experience with 97/2000 working with SQL Server. One is that you don't have permissions to append records in SQL Server. To determine if that is the case, check to see if your ODBC connection uses 'sa' as the userid in SQL. If you are using Integrated security it's a little more complicated - your network login needs to have Admin priviledges on the network. If neither of those is true, then you will need to specifically set the ability to add records using SQL Server security. I presume you are running either 7 or 2000 of SQL Server. The other possibility is the version of the MDAC that you are running. We've seen some really weird behavior with a couple of versions of that. In any event, we use code very similar to what you posted with A2K and SQL Server 7/2000 with no problems.
    Wendell

Posting Permissions

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