Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: SQL Server Error Message Fun

    I've researched and digested all the information I can find on this, and have read KB article 209855.

    Still, I have an ODBC error message I can't trap...because I don't know where to do it, or if it can be.

    Environment is an Access 2003 frontend against SQL Express 2005 backend, with linked tables.

    The cause of the error is a simple one - I leave a required field null and attempt to move to a new record.

    The exact message generated is: "ODBC--update on a linked table 'dbo_Customers' failed.

    In the FormError event I am trapping ODBC and VBA errors, and issuing a Response = acDataErrContinue.

    Still, I get the message.

    Can someone please tell me where and how to trap this?

    In addition, is it possible to programmatically determine the name of the field and/or the control in violation?

    Many thanks.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Do you have any triggers on SQL Server against the SQL Server table? These are sometimes difficult to spot when updating a new record on SQL Server.

    To find the required field(s) in the table, you can look at the design of the table and it's properties for Required = Yes in either Access or SQL Server. You can also try adding data into fields directly in the table verses a form, going to a new record until you can pinpoint one or more fields which might be giving you problems. You can try doing this in SQL Server directly to take Access and ODBC out of the picture.
    Last edited by pkstormy; 08-04-07 at 22:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    Yes, there is a trigger. The behavior I describe is fully expected - it is a required field. I just want to be able to trap the long, nasty error message and supply something more friendly, without validating the control or the form, as this is a rapid development project using linked tables. If validation on individual controls is the only thing that will work, so be it...but I have about 20 subforms with multiple required fields I'll have to code for.

    Edit:

    Also, being that this application is needed in hours for a job, I've not coded any navigation buttons, instead using the built-in variety. The 'update failed' message is shown when moving to new record with the built-in navigation buttons. I'm actively testing this, and have discovered that if I move to a new record programmatically (DoCmd.RunCommand), I get a nice, trappable 3157. However, if I coded some navigation buttons, I still wouldn't know which field is causing it. Writing frontends against ODBC tables is not something I've done much, so my inexperience in the associated error handling is slowing down this "rapid" project.

    In any case, the many forms with required fields are subforms, called when needed in a single subform control on a parent form. Navigation buttons would be on the Parent.
    Last edited by TXPatriot; 08-05-07 at 00:24.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If the error occurs due to a required field, the easiest way might be to set the default value of that field on SQL Server to something.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2007
    Posts
    3
    Ok, thanks for your reply on this. There isn't really any decent default value I might put on it. It happens to be a foreign key for a table of various types of exotic metals that a piece of equipment might be made of. Bunch of 'em in the db...guess I could default 'em all to aluminum and tick some people off heh heh.

    Seeing that there are so many required fields in our developing lives, I figured I ought to bite the bullet and start diving in. So I've been redoing all the forms with custom navigation buttons and decent validation. The crew flying to CA in the morning who need this will just have to wait 'till afternoon.

    Incidentally, I still have not been able to determine where to trap that type of error. I can't trap it in any event on the form, either in the Err object or the Errors collection.

    For what it's worth, the title bar on the msgbox says Microsoft Office Access...or something very similar. Jet error? Heck, I don't know

    Thanks again, pk.

Posting Permissions

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