none
ON UPDATE CASCADE is not working RRS feed

  • Question

  • Hello,

    I am using ALTER TABLE Query to add foreign Key constraint and it is successfully created..but In that I am adding ON UPDATE CASCADE and after that I see in relationship table but checkbox for that is not checked..

    What to add in ALTER query to check that checkbox ?

    Thursday, December 31, 2015 12:27 PM

Answers

  • >>>What to add in ALTER query to check that checkbox ?

    According to your description, when your ALTER statement is valid Access DDL, must be executed with ADO. You could refer to below code:

    Sub OnAlterUpdateCascade()
    
      strSql = "ALTER TABLE TB_Employee ADD CONSTRAINT FKDepartmentId FOREIGN KEY (DepartmentID) REFERENCES TB_Department(ID) ON UPDATE CASCADE"
    
    ' executing that statement with DAO triggers error 3289,
    ' "Syntax error in CONSTRAINT clause."
    ' (CurrentDb.Execute is a DAO Method)
    'CurrentDb.Execute strSql ' DAO is fail
    
    ' CurrentProject.Connection.Execute is an ADO method,
    ' so this attempt executes without error
    CurrentProject.Connection.Execute strSql ' ADO is OK
    
    End Sub
    


    Note: Execute the statement from the Access query designer, that also uses DAO trigger error 3289.

    Friday, January 1, 2016 6:33 AM

All replies

  • >>>What to add in ALTER query to check that checkbox ?

    According to your description, when your ALTER statement is valid Access DDL, must be executed with ADO. You could refer to below code:

    Sub OnAlterUpdateCascade()
    
      strSql = "ALTER TABLE TB_Employee ADD CONSTRAINT FKDepartmentId FOREIGN KEY (DepartmentID) REFERENCES TB_Department(ID) ON UPDATE CASCADE"
    
    ' executing that statement with DAO triggers error 3289,
    ' "Syntax error in CONSTRAINT clause."
    ' (CurrentDb.Execute is a DAO Method)
    'CurrentDb.Execute strSql ' DAO is fail
    
    ' CurrentProject.Connection.Execute is an ADO method,
    ' so this attempt executes without error
    CurrentProject.Connection.Execute strSql ' ADO is OK
    
    End Sub
    


    Note: Execute the statement from the Access query designer, that also uses DAO trigger error 3289.

    Friday, January 1, 2016 6:33 AM
  • Thank you..!!! It works for me...
    Friday, January 1, 2016 7:00 AM