On Thu, Feb 9, 2023 at 5:10 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 2/9/23 14:43, Brad White wrote: > > On Tue, Feb 7, 2023 at 10:20 PM Brad White <b55wh...@gmail.com > > <mailto:b55wh...@gmail.com>> wrote: > > > > 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. > > > > Back in the office today and I note that all of the fields that are > > getting the issue are the target field in an UPDATE statement. > > All the other tables and field names are quoted correctly. > > > > I suspect an ODBC driver bug. Is there a better place to report those? > > > > Driver: PostgreSQL Unicode > > Filename: PSQLODBC35W.DLL > > Version: 13.02.00 > > ReleaseDate: 9/22/2021 > > https://www.postgresql.org/list/pgsql-odbc/ > > > > > On the other hand, the app updates things all the time. Only about 12 of > > the update statements are ending up in the log. Still looking for the > > common denominator in how those statements are called. > > > So how the successful UPDATE's called? > I'm still trying to track down all the statements. Because of the translation between the two database systems, I can't just search on a simple string. > > Are the successful UPDATES's on the same tables and columns? > This is the only routine that updates the InsertFlag column. All the order tables have that flag. > > Are these UPDATE's actually necessary? > This system is critical to the company, but has a reputation of being unreliable. I suspect this may be one cause. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
