Results 1 to 9 of 9
  1. #1
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102

    odbc -- insert on a linked table "tablename" failed.


    Hello, I have an access front end and SQL server backend, I have two groups, standard users that can edit database info and read only users that cannot. When a read only user tries to edit or write new info I get this error 'insert on a linked table "tablename" failed. How can I use this to my advantage to inform the user that they are not allowed to edit or insert and close the form? Or is there another way of handling this scenario?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not a sql server user, but do you get an error number along with the message?
    Can you trap the error number and provide your own message.

  3. #3
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    I think the error number is 3155 but it doesn't come up with a number, and not sure how to do that even if I knew the number lol

  4. #4
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Looked on the net to see if anyone had written some code to inform the user that they are not allowed to edit and add

  5. #5
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Does anyone have an code to throw up a message to notify the user that they are not allowed to enter info, or is there another method that I have missed.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you are getting an error 3155, you could check for it in your error handling code.
    However, this would be an after the fact confirmation that someone was attempting to modify/insert/delete a record in the linked table.

    In your code to limit user rights, if the user is a ReadOnly user, you could display a msgbox or a modal form that they had to see and react to in order to continue. And you could have an error handling routine to tell them again.
    Below is untested code ---air code to show the general logic involved ----

    Code:
    If userType is ReadOnly Then
     MsgBox "You have READ ONLY access to tables. Any attempt to edit, insert or delete a record will fail",vbInfoOnly
    end if
    IN the error handler

    Code:
    If err.number = 3155 then
     MsgBox "You do NOT have authority to modify, insert or delete records", vbOKOnly
    else
     msgbox err.number & " " Err.description, vbOKCancel
    end if

  7. #7
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Thank you for that response, would I place these on the form load or open event or completely separate to that? sorry for being a pain.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The general message could be on the form open.

    The error handler would be part of the form where user is reviewing data.

  9. #9
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi, and thanks for the input, I have tried previous suggestions and not quite got my head round how to resolve this. We are on a network that uses Windows login to that network, that login is passed to two groups in SQL, a full user group and a read only group.
    This is for obvious reasons, the full user can read/write, delete etc and the ready only can only do that, but if the read only user attempts to alter a record on one of the entry forms I get the ODBC error that writing to the table has failed. Can I simply put a message box in there to tell the user that they can't do that and suppress warnings and simply close the form? or is it more complex than that. i.e set warnings false then back to true to not show the ODBC warning, then close the form without saving the attempted changes?
    Attached Thumbnails Attached Thumbnails ODBC error.jpg  

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "Run Time Error 3146, ODBC call failed" :(
    By selvakumar.arc in forum Access
    Replies: 0
    Last Post: 12-04-2014, 01:57 PM
  2. ODBC : "the call has failed" in report
    By petertje in forum Access
    Replies: 3
    Last Post: 01-02-2014, 07:42 PM
  3. Replies: 1
    Last Post: 10-11-2011, 11:16 AM
  4. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  5. Replies: 0
    Last Post: 11-30-2010, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums