Re: Problem in using PostgreSQL ODBC driver with VBA - Mailing list pgsql-odbc

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

Fred Parkinson

Application Programmer

Association of Bay Area Governments

415-820-7931

>>> Adrian Klaver <adrian.klaver@aklaver.com> 11/3/2016 8:16 AM >>>

On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:

> Good morning

>

> I write in order to obtain clarification (and possibly a solution !)

> about a problem I can't solve.

>

> First of all I will briefly describe the scenario.

>

> I have several applications written in VBA (Visual basic for

> Application) using Microsoft Access both as backend and frontend (data

> are stored in mdb files on a server and applications are stored in mde

> files on the pcs).

>

> I'm committed in reingeneering these applications in order to migrate

> data files to a PostgreSQL database, without changing the frontends

> except that for the connection to the data they manage.

>

> The idea is to connect each frontend to Postgres DB (version 9.5.4)

> using ODBC (version 9.5.4).

>

> In order to run some tests, I migrated data from an mdb file to PG

> tables, defined an ODBC connection and linked the Access frontend to the

> PG database using it.

>

> The problem raises when the application needs to deal with data stored

> in boolean columns.

>

> If SW_UE is the name of a boolean column in a table, when I use a

> statements like this

>

> If SW_UE = -1 Then

>

> Do something

>

> End If

>

> the following error raises:

>

> ERROR: Operator does not exist boolean = integer

>

> I already searched the Internet for a solution and I found some, but

> none solved the problem.

>

> For this reason I decided to write here, hoping in your help and/or some

> hint.

https://odbc.postgresql.org/docs/config.html

Advanced Options 2/2 Dialog Box

True is -1: Represent TRUE as -1 for compatibility with some applications.

While we are on bools:

Advanced Options 1/2 Dialog Box

Data Type Options: affects how some data types are mapped:

Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.

>

> I thank you in advance for any answer.

>

> Best regards

>

> ------------------------------------------------------------

> Vincenzo Turturro

>

> Regione Toscana

> Direzione generale Governo del territorio

> Sistema Informativo Territoriale ed Ambientale

>

> Via di Novoli 26

> 50127 Firenze

>

> Tel 055 43 83 855

> ------------------------------------------------------------

>

--

Adrian Klaver

adrian.klaver@aklaver.com

--

Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-odbc

-- 
------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------