Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    13

    Unanswered: Problem with Update/Delete Cascade

    I've been trying to add an update cascade and delete cascade to the following code which works fine without it...


    Alter table [AgentRecords]
    add constraint [FK_AGENTID]
    foreign key ([Agent ID])
    References [IEX Hierarchy] (AGENT_ID)


    but when I put this in my query in access 2007 in sql view:

    Alter table [AgentRecords]
    add constraint [FK_AGENTID]
    foreign key ([Agent ID])
    References [IEX Hierarchy] (AGENT_ID)
    ON UPDATE CASCADE ON DELETE CASCADE


    I get a syntax error in constraint clause and it highlights "UPDATE"

    I'll be happy to provide any additional details if need be and thanks
    Last edited by jmantn; 03-27-09 at 22:28.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Why do you need to put in a query a select type statement which alters the table design?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Posts
    13
    I've got a backend db where I get most of my info from an odbc that is updated every day (technically all day long) and I have a query that takes select info from the odbc and makes a table with all the necessary info into a front end database for my users.

    The data from odbc can range from newly hired agents to status changes (which rules out an append I believe) and employees that are termed to name a few.

    I haven't updated the table in a while (Still making the database) and have set some relationships with the data from this table and other tables and didn't realize I couldn't update until now because of the relationships I've implemented. This is my first database and I completely forgot.

    The relationship I have set up is needed as I have three main tables in my front end database one is my hierarchy which is made from the make table query, a training db, and an agentrecord table which consists of data from the two previously stated tables and some other info. So if an agent quits I can delete them from the hierarchy table and thier records are automatically deleted from the agentrecords table.

    So to solve this I'm exploring other ways of doing this and my current thoughts are to have a query that makes the primary key, a query that sets up the foreign key and set the relationship and then I'll have one set up to disable (if possible) the relationship so I can do the make table update and not inadvertently delete anything.

    I had posted a similar thread but with this one I'm going in a different direction. Previous thread (http://www.dbforums.com/microsoft-ac...ionships.html).

    Hope this helps explain things a bit.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Wouldn't a simple "Make-Table" or "Append" query in MSAccess be so much easier? You use the ODBC Linked table as the source to create your MSAccess table or append to an existing one.

    If relationships are involved, set them up on pre-determined tables and then run append queries to append the data.

    I still don't understand why you need to establish relationships (on the fly) when you can easily set them up in table design (beforehand) and then just append data to the tables.
    Last edited by pkstormy; 03-29-09 at 03:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2009
    Posts
    13
    Thanks for bearing with me on this as I have a lot to learn.

    Due to my relationships and having a pk on the main table that's getting appended to I can't run an append query. It tells me there's a lock violation so when I remove the pk and relationships it will then run the make table or append query.

    I'll try to explain this another way:

    Data from ODBC makes up my Hierarchy table which has employee info in it.
    On this table I have Agent ID as a PK as it is unique to each employee

    I have a second table that holds all training info for each class we've done
    On this table I have Course Number as a PK

    My third table AgentTraining holds data from the first two tables plus info such as missed or completed and etc.
    The pk from both tables are associated to this table.

    The database will be used by people who have no understanding of how access works so I'm trying to make this simple and clean so maybe I'm over-complicating things.

    The reason for the relationship with Hierarchy and AgentTraining is because if an agent is termed it will automatically remove them from both tables. I can do an unmatched query to weed the orphaned records if I forego the relationship between just those two tables and the pk and fk that are associated however I wanted to avoid.

    So what I was going to do which does seem like it's a tad more work but have the queries I mentioned previously where I have one establish the pk and relationship and one to disable it when I update but I can't get the cascade update and delete to work.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This doesn't sound clean and simple. It seriously sounds like you need to revisit the structure if you need to "disable" a relationship in order to update records (although you can sometimes change a query type to "Dynaset (Inconsinstent updates)" to bypass certain relationship issues.

    MSAccess can automatically cascade delete (or not) and you can also define it to automatically cascade update (or not) in the relationships screen.

    Instead of having an actual relationship of 2 fields in your AgentTraining to 2 separate tables, you may want to revisit the table design/structure so there's not the need to do this kind of join and cause you to disable a relationship or look at your update/delete queries and design a better update query method. It is not good practice to disable/enable a join in order to update or delete records.

    You may want to upload a sample mdb of your structure to get some feedback (convert the structure to MSAccess tables if it's SQL Server or upload as is as we can still see the table design.)
    Last edited by pkstormy; 03-29-09 at 23:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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