Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Unanswered: ODBC Update Fails

    Hi: I am hoping someone can help me with this. I've had multiple iterations and I have a function that sometimes works and sometimes does not.

    First a little history: I used to use an update query to do this, but sometimes we would get ODBC failures - I could never figure it out, so I thought I would write something like the following function. It worked, and then it didn't, it works with some equipment UID's and not others, or is intermittent with others at the least. Most recently, I added the rs.movefirst, and had some debug statements, it worked, so I made the mde file, tested again, and it failed. Test again from the unchanged access mdb file, and get ODBC -- Update on a linked table 'tblEquipmentMaster' failed.

    As you can see in an earlier revision I inserted adLockPessimistic. There is a form open that does a query on 'tblEquipmentMaster', and for the record

    Public Function UpdateEquipPM(EquipID As Integer, PMDate As Date) As Boolean
    Dim rs As New ADODB.Recordset
    Dim query As String
    Dim retvalue As Boolean

    query = "select * from tblEquipmentMaster where [equipuid]=" & EquipID

    rs.Open query, CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    rs.Find ("[equipuid]=" & EquipID)
    If rs.EOF Then
    retvalue = False
    rs!LastPMDate = PMDate
    retvalue = True
    End If
    UpdateEquipPM = retvalue
    End Function

    Any ideas or help would be greatly appreciated. Back end is a SQLServer Express Database.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    There can be a problem with the date format. SQL Server does not use the same rules as Access. Try to explicitly cast the date.
    Have a nice day!

  3. #3
    Join Date
    Oct 2003

    Additional Information

    I did a bit more testing . . . I use a series of forms, but in essence, I have a form: "frmCustomerEquipmentLookupforDispatch" that has a source "select * from tblEquipmentMaster" from it, a button "btnPostPM" opens an unbound form from which users enter PM information (FSR Number, Date, TechnicianID). I run two functions when the submit button is depressed, one to insert into a history table - this works fine, and one to update the equipment table.

    When I have "frmCustomerEquipmentLookupforDispatch" using a snapshot for data . . . BUT, if I set it to dynaset, this function works "BUT" then I get messages about can't save a record from frmCustomerEquipmentLookupforDispatch. seems like a catch 22.

  4. #4
    Join Date
    Oct 2003

    OK, so I was being obtuse.

    The solution, as you might have guessed was a simple one. I had tried to create functions that would work whether or not the form was open . . . However when the form was open, it created the problem - so, simple.

    if isopen("frmCustomerEquipmentLookupforDispatch") then
    forms!frmCustomerEquipmentLookupforDispatch.LastPM Date=me.pmdate
    . . .
    end if

    I should have seen this earlier, but I keep trying to move things away from one form modifying another . . .

Posting Permissions

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