Re: Quoting issue from ODBC

On 2/7/2023 6:19 PM, Adrian Klaver wrote:
> On 2/7/23 16:10, Brad White wrote:
>> Front end: Access 365
>> Back end: Postgres 9.4
>> (I know, we are in the process of upgrading)
>>
>> I'm getting some cases where the SQL sent from MS-Access is failing.
>> Looking at the postgres log shows that the field names and table
>> names are not being quoted properly.
>> It has been my experience that Access usually does a better job at
>> converting the queries than I would have expected, but not in this
>> instance.
>>
>> For example
>>
>> Access: connection.Execute "UPDATE [" & strTable & "] SET [" &
>> strTable & "].[InsertFlag] = Null" _
>>      & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID &
>> "));", , adCmdText Or adExecuteNoRecords
>> Note that InsertFlag is bracketed the same way in both instances.
>>
>> PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE
>> ("InsertFlag" = 166 )
>> Note that InsertFlag is quoted once but not the other time.
>> Of course this gives the error: column "insertflag" of relation
>> "Orders" does not exist at character 35.
>>
>> Looks like I have about 16 unique instances of statements not being
>> quoted correctly resulting in over 500 errors in the log for today.
>
> Where these preexisting queries or where they created today?

These queries are decades old but I don't view this log file very often,
so I don't know how long.

I'll review when I get back on site Thursday and see if I can find any
users that are not getting the error or when it started.

>
>>
>> Any suggestions on where to look?
>>
>> Thanks,
>> Brad.
>
--
Quote Signature I talk with clients, find out where their pain points
are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwhite(at)inebraska(dot)com
402-601-7990

Quote of the Day
   There is a huge difference between fathering a child and being a
father.
   One produces a child. The other produces an adult.
    -- John Eldredge