Re: What's the best way to translate MS generated translations of user input to what the user actually typed prior to insert or update into PG backend table ?

On Fri, Oct 30, 2020 at 9:03 AM David Gauthier <davegauthierpg(at)gmail(dot)com>
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.