=-- On Fri, 11/23/07, Tom Hart <tomhart@coopfed.org> wrote:
> The problem I'm having is that anything defined as a "text" datatype in > postgres gets converted to a "memo"
datatypein access, and when we
> attempt to perform a join query on thatfield access starts complaining > about not being able to join on a field
witha "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-Accesscan only create indexs on it version of the text type which is limited to 255 characters. If you know that
yourcolumns will always be less than 255 characters, then you need to define these columns as a varchar(255) so that
MS-Accessknows how to deal with it. Since Postgres places no limits on the length of characters that a field of the
postgrestext 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,
ALWAYSdefine 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
booleanwill result in failure.
Regards,
Richard Broersma Jr.