Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2007
    Posts
    7

    Question Unanswered: trapping SQL Server errors?

    hello everybody

    I have an Access project linked to SQL Server 2000.

    In Access I have a form linked directly to a table in the server.
    When I'm trying to update a record that breaks a validation rule (duplicated key), no error is displayed in Access, and there is no way for me to know if the record was updated correctly.

    I want to trap the errors and display a descriptive message.

    The following does not work for me:

    Private Sub btnSave_Click()
    On Error GoTo trapError
    RunCommand acCmdSaveRecord
    MsgBox "Record saved!" '<<<<<< THIS IS SHOWN ALLWAYS
    DoCmd.Close
    Exit Sub
    trapError:
    Call MsgBox(Err.Number & " - " & Err.Description, vbCritical)
    End Sub

    Also I have tried with
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    MsgBox DataErr
    End Sub

    but doesn't work too

    ¿any ideas?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is this a linked table via ODBC? What does the record itself look like? Does it come from a single table? I usually use adp's or ado to hit sql server so I'm not sure how error handling works in this context, but I can find out if you can give me a more detailed description of your scenario.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2007
    Posts
    7
    Im using an Access Project (myFile.adp), not an Access database (.mdb)

    The connection to slq server is direct (actually all the tables and views shown in Access are kept in the sql server)

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's odd... the unique index on sql server wasn't defined with ingore_dups was it?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In mdb's, I'm not always trusting acCmdSaveRecord, and the DoCmd.Close, is known to close without saving.

    I don't know, but you might try setting

    Me.Dirty = False

    in stead. Perhaps this (http://allenbrowne.com/bug-01.html) mdb information applies to ADP?
    Roy-Vidar

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    As noted in the http example above (I downloaded the AccessFlaws.mdb), if the LosingData table were designed PROPERLY (i.e. with a Primary Key - autonumber) and this field were put on the LosingData form, you would NOT have the problem of losing any data.

    (Why do programmers develop tables without a primary key/autonumber? Are these old Progress or Unix based programmers? In EVERY KEY DATA table I've designed, I ALWAYS use a primary key/autonumber field, put this field on the form, and NEVER have the problem this Access mdb INCORRECTLY displays as a flaw in MSAccess. It's NOT a problem with MSAccess but a problem with an incorrectly designed table and form!! - It really baffles me why you wouldn't design a key data table WITHOUT a primary key/autonumber field.)

    I can make any development tool/language lose data by incorrectly designing the table and forms!

    Use the primary key and autonumber designator - that's what it's there for - I've used them for 25 years and have never had a problem.
    Last edited by pkstormy; 04-27-07 at 17:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    1 - if you want to criticize Allen Browne, please do so in one of the NGs he visits, or perhaps send him an e-mail
    2 - there's no requirement for a presence of an autonumber in a table for it to be designed properly
    3 - this error may occur regardless of whether there is a primary key or not defined, but creating a table without, guarantees it will occur, which is probably the point of Allens demonstration db
    4 - if you've used autonumbers for 25 year, hooray - that's just about 10 years more than Access has existed

    If you want do the natural vs surrogate, this is neither the time nor place.
    Roy-Vidar

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    1. I WILL criticize Allen Browne because it's easy to criticize MSAccess and fault it but it's difficult to do things correctly! (and I already sent him an email)

    2. You are correct - there is no requirement but there are RIGHT ways of doing things and WRONG ways.

    3. Uh...Yeah! Why not also demonstrate these kind of faults with other programming languages or is MSAccess good for kicking around? If so, I can relate a whole lot more issues than simply incorrectly designing something and saying so to speak "HERE...HERE...SEE THIS PROBLEM!" Which is MY POINT. Instead of labeling the table in his demonstration: AccessFlaws, it should be labeled: IncorrectTableDesign.

    4. I never said I programmed in JUST MSAccess (why did you assume so??)

    There were OTHER programming tools/languages besides MSAccess that existed for those of us who have programmed for more than 10 years.

    Let's get things right there Roy-Vidar. I don't like it when assumptions are made - as a developer, you should know this better than anyone else (or am I making assumptions on you being a developer?)
    Last edited by pkstormy; 04-27-07 at 18:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Take it easy kids.

    robeito has specified he's using an adp against sql server. Let's find out how his unique index is defined before we go jumping to conclusions. What he's describing is default behavior if ignore_dups is defined.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I just don't like people kicking around MSAccess Teddy (and also making assumptions about me). I'm not really in the mood today. Although I apologize about anything I might have said which upsets anyone.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Apr 2007
    Posts
    7
    More information:

    The error trapping doesn't work for violation of unique index restrictions, in any column.

    Also, trying to save a record that have a null field, and if the null is not allowed, also does not trigger an error.

    In both cases, the record in cuestion is not saved, but, I don't receive an error message, the onError in my code does not trap anythig.

    I have found that the problem arises whith sql server with service pack 4 installed, and doesn't happen with a clean install of sql server

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What happens if you try to insert the your sample data directly using query analyzer?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Apr 2007
    Posts
    7
    Teddy:

    Using the query analyzer raises correctly the error and the record is not inserted

  14. #14
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    robeito,
    sorry to cloud this, there where no luck with Me.Dirty = False either?

    pkstormy,
    this is a known bug, which has existed at least since the mid 90's. It might occur with or without primary key. For a demonstration with primary key, try the following:

    In a table with primary key
    - set one fields required property to yes (and ZLS no)
    - create a form based on the table
    - on the form, create a close button, only containing the code

    DoCmd.Close

    - open the form, create a new record, enter information in all fields except the one you've set required = true
    - hit your close button
    - reopen the form, and find your record

    As an information, in the last version (2007), Access inserts the following (well, there's some error handling too) when using the wizard:

    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close

    in stead of only the

    DoCmd.Close

    in previous versions. My guess, is that they've listened to Allen Browne
    Roy-Vidar

  15. #15
    Join Date
    Apr 2007
    Posts
    7
    RoyVidar:
    me.dirty=false didn't work

    Pinpointing my problem: the vb code that saves the current record does not raise any error flag in situations that it should, so OnError doesn't branch the goto

    Now I'm trying with:

    Me.Recordset.Save

    and it seems to work fine

Posting Permissions

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