Re: Problem in using PostgreSQL ODBC driver with VBA

One thing I now remember from when this issue first appeared is that there was another possible solution.

When trying to use FALSE or TRUE in an Access query that uses a postgres boolean, I would get the error:

ODBC call failed: ERROR: operator does not exist: boolean = integer;...

Apparently you can write an operator and add it to the postgres server, an operator that will perform the comparison between a boolean and an integer.

The intern I was working with at the time tried once to write that operator, but when his attempt failed at the first try, we went with the kludge because we were in a hurry and didn't want to spend the time debugging his code.

I am not sure but I think the operator was written in PHP. In any case, that might be a fruitful line of inquery given that you can't rewrite a major application to use the kludge I describe below.

Hope this helps!

Fred Parkinson

Application Programmer

Association of Bay Area Governments

415-820-7931

>>> Vincenzo Turturro <vincenzo.turturro@regione.toscana.it> 11/4/2016 4:52 AM >>>

>

Il 03/11/2016 16:53, Fred Parkinson ha scritto:

Been there done that.

I have been using Access with PostgreSQL through ODBC for about 20 years, and boolean is essentially the biggest issue.

I create 2 odbc connections to a postgres database that has tables with boolean fields
connection 1 uses an ODBC file DSN with: PAGE 1: bools as char is checked; page 2: true is -1 is unchecked
connection 2 uses an ODBC file DSN with: PAGE 1: bools as char is UNchecked; page 2: true is -1 is checked

My access database then has 2 connections to the table in question. I sufix the connection 2 table name (in access) as: public_my_table_bolasneg1

The odbc connection using the connection 1 file DSN to this table can be used in queries with a WHERE condition of "0" or "1" to limit records based on the boolean field.
The odbc connection using the connection 2 file DSN to this table can be used to bind the MS Access boolean check box to this field and the user can use the check box natively to update this field.

Yes this is a kludge but it has worked for 20 years.

in your example below I would use the connection 1 table and the statement would be:

If SW_UE = "1" Then

do something

end if

Thank you for your answer, but your suggestion is not affordable in my situation.

I'm not writing a new application: I'm reingeneering an existing application (eventually the first of a set of ten) and I merely can't browse the thousands of lines of vba code in order to change all the "If" statements.

Nor I can use the automatic "search and replace" function because the if statements can be of the type

If BoolField = BoolVar then

do something

end if

Nor I can change the type of all the boolean variables to Char.

And I simply don't want even to think about the nightmare to dig all the SQL statements, which often are built in VBA "on the fly" according to user's choices ...

Do you think I should evaluate another database, more "Access friendly" ?

Best regards