Re: ODBC, access, and joins

=-- On Fri, 11/23/07, Tom Hart <tomhart(at)coopfed(dot)org> wrote:

> The problem I'm having is that anything defined as a "text" datatype in > postgres gets converted to a "memo" datatype in access, and when we
> attempt to perform a join query on thatfield access starts complaining > about not being able to join on a field with a "memo" type.

The problem is that Access doesn't know how to deal with the postgres text type. Crystal reports has the same problem. MS-Access can only create indexs on it version of the text type which is limited to 255 characters. If you know that your columns will always be less than 255 characters, then you need to define these columns as a varchar(255) so that MS-Access knows how to deal with it. Since Postgres places no limits on the length of characters that a field of the postgres text datatype can contain, Access rightly should treat it as a memo type or else there is the potential of breakage.

> There's a couple other weird conversions, like boolean to text
> (with a number value) but I've been able to manage most of them.

I usually configure the odbc drive as [uncheck] bools as char and [check] true = -1. Ms-access seems to like this.

However, there is one piece of advice that I just discovered: when dealing with odbc linked booleans fields in access, ALWAYS define these columns as not null. This is a known MS-Acess bug in that all odbc linked tables (to any flavor of RDBMS) will incorrectly treat nulls and false. All attemps to update a record using MS-access and ODBC with a null boolean will result in failure.

Regards,
Richard Broersma Jr.