On Fri, Oct 30, 2020 at 9:03 AM David Gauthier <davegauthierpg@xxxxxxxxx> wrote:
I was thinking of a pre-insert and pre-update trigger which could make the translation. But I'd rather not try to do this one char at a time... translate "..." today to fix today's issue, then "--" tomorrow when that pops up, then the way MS translates double quotes the next day, etc... . Is there an elegant way to do this ?
I think the trigger solution is probably the correct one (not sure where else you could inject yourself into the data stream). It seems inefficient for the trigger to pull its knowledge base from a table - thus code the logic directly within the trigger. The other side of that equation is that now you need to update schema to change rules instead of adding a row to a table. Neither is more elegant than the other, rather there is a trade-off here that only you need to make.
Some things to consider:
Can you get a reference from MS on these conversions so you can code them in advance of seeing them appear in data?
Is there some kind of pattern for the ones you know about that you can check for more generally and warn you when the pattern appears but there is not specific rule?
What is the downside of not doing this at all? Or missing new ones?
Do you need to reverse the process so MS-Access is getting the data it expects?
David J.
