Modify a Data Macro?

[Uncle Gizmo]

I want to modify a data macro attached to a table in MS Access. However I am finding the interface archaic in the extreme! It's like using early dos-based programming! Surely there's an easy way of modifying a data macro! Any ideas?

 

[theDBguy]

Not sure if it's easier, but I think you can also create/modify a data macro through its XML code.

 

[Uncle Gizmo]

Not sure if it's easier, but I think you can also create/modify a data macro through its XML code.

Thank you DBGuy. I've tried everything I can think of to edit the data macros. I wanted to test referential integrity with some VBA code and needed to introduce a referential integrity error.

I attempted to do this in the Northwind 2 Developer Edition. I set up a separate database with code designed to externally modify a table within the Northwind database, thinking this might bypass the referential integrity constraints. Unfortunately, I ran into data macros that prevented any edits to the tables.

I was considering pursuing this further, but then it struck me—why complicate things with the Northwind database? I decided to upload a simpler database, where I know the tables are constructed simply and straightforwardly.

I've run out of time for now, so I'll have to revisit this another day.

Cheers Tony...

 

[ebs17]

thinking this might bypass the referential integrity constraints

In principle, this shouldn't work.

 

[Uncle Gizmo]

In principle, this shouldn't work.

That's exactly what I'm trying to find out. My attempt was defeated by the data macros in the Northwind 2 Developer Edition database. I'm going to attempt it now with a simpler database setup. I'll let you know how I get on!

 

[Uncle Gizmo]

I'll let you know how I get on!

Thanks for all the feedback and suggestions. After extensive testing and numerous attempts, I've come to accept that it’s not possible to change a record in a way that violates referential integrity while keeping the constraints in place. It’s clear that Access enforces these rules very strictly, even when trying to make changes externally or with the database closed.

Given this, I’ve decided to move on from this experiment. It’s been an enlightening process, and I’ve learned a lot about how robustly Access handles referential integrity. I appreciate all the input and support from this community – it’s been invaluable.

Thanks again, and happy developing!

 

[GPGeorge]

Thanks for all the feedback and suggestions. After extensive testing and numerous attempts, I've come to accept that it’s not possible to change a record in a way that violates referential integrity while keeping the constraints in place. It’s clear that Access enforces these rules very strictly, even when trying to make changes externally or with the database closed.

Given this, I’ve decided to move on from this experiment. It’s been an enlightening process, and I’ve learned a lot about how robustly Access handles referential integrity. I appreciate all the input and support from this community – it’s been invaluable.

Thanks again, and happy developing!

Two things come to mind.

I'm curious about the context in which it would be a good idea to bypass referential integrity? What is the long-term goal? What would be the positive outcome that could result from doing so?

Data Macros fill the same role as Triggers on SQL Server tables. They, are therefore, quite useful in scenarios where you want a particular process to fire regardless of how data is changed in a table. I agree that the interface for editing Data Macros is obscure and cumbersome, though.

 

[Uncle Gizmo]

I'm curious about the context in which it would be a good idea to bypass referential integrity?

I wrote some code with the aid of chatty to detect referral integrity violations. I needed a sample database containing a referral integrity violation to test it.

Why? Curiosity about a past experience!

I believe I had a referential integrity violation on a database I was developing about 20 years ago, and I manually fixed the problem in the table. However, I can't be sure exactly what the problem was. It had been caused by someone switching off a network computer. There were four computers with front-end databases accessing the same back-end database. A recent post reminded me of this incident, and I thought I would have a little play around and see what I could do!

I also know that one of my hard drives has got a copy of this corrupt database on it somewhere but I can't find it!

 

[Uncle Gizmo]

This summary was prepared by Chatty, my AI assistant, who has been invaluable throughout this process.​

Process and Learnings​

• Exploration of Referential Integrity:
  
  • Attempted to introduce referential integrity violations in the Northwind 2 Developer Edition database.
  • Set up a separate database with VBA code to externally modify a table within the Northwind database, hoping to bypass referential integrity constraints.
• Encountering Data Macros:
  
  • Discovered that data macros in Northwind 2 prevented any edits to the tables that would violate referential integrity.
  • Realized that Access enforces referential integrity strictly, making it impossible to introduce violations without disabling the constraints.
• Learning About Data Macros:
  
  • Found that data macros are very awkward to modify.
  • Discovered some excellent YouTube videos by Sean MacKenzie on data macros, which provide an introductory look at their functionality:
    • Data Macros in Microsoft Access: An introductory look at Data Macros, similar to "Triggers" in other database management systems.

Key Takeaways​

• Understanding Referential Integrity:
  
  • It’s not possible to change a record in a way that violates referential integrity while the constraints are in place.
  • Access enforces these rules even when trying to make changes externally or with the database closed.
• Data Macros in Access:
  
  • Data macros are a powerful feature in Access, similar to triggers in other DBMS.
  • Modifying data macros can be challenging, and it’s an area that requires more exploration and learning.

Conclusion​

This journey has been enlightening, and I’ve learned a lot about how robustly Access handles referential integrity and the complexities of data macros.

 

[GPGeorge]

I wrote some code with the aid of chatty to detect referral integrity violations. I needed a sample database containing a referral integrity violation to test it.

Why? Curiosity about a past experience!

I believe I had a referential integrity violation on a database I was developing about 20 years ago, and I manually fixed the problem in the table. However, I can't be sure exactly what the problem was. It had been caused by someone switching off a network computer. There were four computers with front-end databases accessing the same back-end database. A recent post reminded me of this incident, and I thought I would have a little play around and see what I could do!

I also know that one of my hard drives has got a copy of this corrupt database on it somewhere but I can't find it!

Thanks. To me it seems like a circular problem. RI would refuse to let a violation occur, but you need a violation of RI to test, but you can't create it because it won't let you....

Sort of like a Prime Directive.

The only sure way I ever saw to create RI problems was to drape a network cable over the top of a fluorescent light with a ballast that caused a power surge when the light was turned on. Worked every month or so.

 

[Pat Hartman]

I set up a separate database with code designed to externally modify a table within the Northwind database, thinking this might bypass the referential integrity constraints.

That isn't the way RI works. RI is defined by the database where the tables exist and respected by all applications that attempt to access the data. So, it doesn't matter if a web app written in Java and an Access application written in VBA try to update a table in a different Access database. The same update rules will be applied. This is why many applications use Triggers to enforce many simple business rules. It is a single location solution. That means that my Access app can share tables with a web app and neither application needs to attempt to enforce the business rules the Trigger or Data Macro is enforcing.

Data Macros are still sort of new to Access and so are not widely used at this point. Also, if the BE is not ACE, then Data Macros could not be used anyway. RI is always enforced by the database that hosts the data. So you can't add a Data Macro to a linked table. - That's an assumption. I never tried to do it but based on their raison d'etat I think the assumption is valid

I don't know why the Data Macros got in the way but you must have violated one of its rules. The Data Dacro should not care what application attempted to modify a table. Its job is the same as that of a Trigger in SQL Server. WHATEVER, WHOEVER makes a request to update tables guarded by Data Macros, Triggers, simple RI, all attempts are rejected if they violate the rules. That is why RI, Data Macros, and Triggers are always applied in the database where the table resides.

 

[Isaac]

This is why many applications use Triggers to enforce many simple business rules. It is a single location solution. That means that my Access app can share tables with a web app and neither application needs to attempt to enforce the business rules the Trigger or Data Macro is enforcing

Hmm. I tend to avoid triggers unless absolutely necessary at least on SQL Server. They can be useful, but there are many downsides.
It would be best to have that "app" that your db is interacting with utilize an API that can return a lot of useful information to the caller and enforce protocols. Triggers can create a LOT of confusion when trying to trace what the heck happened, when, and why.

 

[Pat Hartman]

The point of triggers is that they enforce rules uniformly. It makes no difference what application is updating a table, the same validation always applies. If only one single Access application is ever going to update a database, then you can control the updating very well by using forms in your Access app. No need to worry about Data Macros for ACE BE's or Triggers for other RDBMS BE's.

 

[Isaac]

The point of triggers is that they enforce rules uniformly

I'd put that more on server-side Constraints including referential ones
but i agree, it's nice that they're agnostic as to who is doing the updating