Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    27

    Unanswered: Error of delete command

    I have a database which connects using ODBC to a datasource which has the details I need for individuals in a sub form. The subform creates a table with only a few details in it including the keys from both the external table as well as the key from a table called session. I have a button that is built to delete a record from the training session but it seems to be trying to also then delete the parent record in the joined ODBC table.

    The error message is:

    ODBC--delete on a linked table 'dbo_PRESENT' failed

    [Microsoft][ODBC SQL Server Driver][SQL Server]DELETE permission denied on object 'PRESENT', database 'NAMEOFSQLDATABASE', owner 'ABC'. (#229)


    Any ideas??

  2. #2
    Join Date
    Jul 2002
    Posts
    67
    Just a thought...

    Do you have DELETE permissions on this other datasource?

    Another possibility...

    You can't delete a parent record in a relational database if there are other child records dependent on that parent record...data integrity violation.
    Last edited by SLACKER; 08-20-02 at 09:46.
    Don't do today, what can be done tomorrow.

  3. #3
    Join Date
    Jul 2002
    Posts
    27
    I do not have delete rights set for the ODBC connection and I don't want them anyway. I am just using the data from the ODBC as a set of information to show on the subform.

    It seems that deleting a record off the subform will not just delete records from the joining table. The table itself is not the parent table anyway. I guess I need code to delete a row of data from a particular table instead of using the wizard to create a delete button.

  4. #4
    Join Date
    Jul 2002
    Posts
    67
    I never use the wizard to create buttons. By doing what you just said in the previous post, might just work for you.

    Just wondering...where is your delete button located? On the main form or on the subform?
    Don't do today, what can be done tomorrow.

  5. #5
    Join Date
    Jul 2002
    Posts
    27
    The button is on the subform and used to work before I started using multiple tables from the ODBC connection. Could you give me a hand with the code to delete a record from a table based on which record is selected on a subform? If you can it would be much appreciated.

    Cheers

  6. #6
    Join Date
    Jul 2002
    Posts
    67

    Re: Error of delete command

    Originally posted by bentley

    The error message is:

    ODBC--delete on a linked table 'dbo_PRESENT' failed

    [Microsoft][ODBC SQL Server Driver][SQL Server]DELETE permission denied on object 'PRESENT', database 'NAMEOFSQLDATABASE', owner 'ABC'. (#229)

    bentley...

    I strongly feel that the reason you can't delete is because you don't have DELETE permissions for the data stored in the SQL Server database that you are connected to (via ODBC)...hence, the error message. You need to contact the database admin for this SQL Server database and discuss the situation further with them.

    Side Note: Somewhere in your delete process, it is wanting to delete data from the SQL Server Database, that is why you are getting the error message.

    If you can send a code sample of your delete process, than we all can take a look at it and see what we can come up with as a solution. Just remember, for every problem there is a solution.
    Last edited by SLACKER; 08-22-02 at 14:55.
    Don't do today, what can be done tomorrow.

  7. #7
    Join Date
    Jul 2002
    Posts
    27
    I know that the error is due to it trying to delete from the SQL server, I don't want it to do that so the code is wrong by trying to delete it. I am only using the SQL server to provide me with details about cilents, I have specifically requested ReadOnly access as that is all I need.


    Basic table setup is as follows:

    account, tbv_title, present, site are all SQL tables.
    clientsession, session are both local tables

    account is related to clientsession by actid
    session related to clientsession by sessionid
    tbv_title holds full titles from title in account
    present related to account holding multiple locations per client
    site related to present with more details of the physical site

    My subform holds details of name, title, and primary street address therefore using all the tables, as well as session number relating to the primary form.



    Autocreated Delete Button has following code????

    Private Sub Command57_Click()
    On Error GoTo Err_Command57_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Exit_Command57_Click:
    Exit Sub


    So it is just using the delete command from the edit menu which is why it is trying to delete from the SQL server also (i think)

    Could you let me know what code I should use just to delete a row from the subform only removing data from the clientsession table.

  8. #8
    Join Date
    Jul 2002
    Posts
    67
    Bentley,

    Because your Account table(SQL Server) is related to your ClientSession table(local) you won't be able to delete any records. I don't think Access will let you do what you want to do unless you have delete permissions for the SQL Server database.

    Some of my work involves creating Access frontends to SQL Server backends and you have to have the necessary permissions for everything to work as planned. I know this is not the answer you want to hear but unless someone can prove me wrong, which for your sake I hope they do, I really think you are at an impass with this.
    Don't do today, what can be done tomorrow.

  9. #9
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Is your data source joining one of the sql-server tables? If so you should not delete directly from it (it's not allowed in SQL 92/99 btw). Access will try to delete the records from every table (I think). Replace your prebuilt code with something like this:

    CurrentDb.Execute "DELETE FROM MyTable WHERE MyUniqueID = " & Me.MyUniqueIDField

Posting Permissions

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