Modify a Data Macro?

[Deleted]

I want to tweak a data macro attached to a table in MS Access, but the editor is driving me mad. The interface feels incredibly archaic—like going back to early DOS-era programming.

Surely there’s a simpler, more modern way to edit data macros than clicking through that clunky UI?

Has anyone found an easier way to work with or modify data macros in Access? Any tips or workarounds?

 

[theDBguy]

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

 

[ebs17]

thinking this might bypass the referential integrity constraints

In principle, this shouldn't work.

 

[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.

 

[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.

 

[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.

 

[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