Thread: ODBC, access, and joins

Hi again everybody. I've got kind of a weird one today (the more
complicated my db gets, the stranger the questions). I've built a data
mine backend to replace our access data mine (eww, I know), but we'd
like to still be able to use access to get to the tables, and write
queries/reports/etc. against them. I've set up the ODBC driver (the
"official" driver, from http://pgfoundry.org/projects/psqlodbc/) and it
connects and pulls the data out just fine, considerably quicker than
access natively. 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 that field access
starts complaining about not being able to join on a field with a "memo"
type. I think this might be a bug in the odbc driver, but I want to
check my bases. Has anybody else run into this issue, or heard anything
about the odbc driver having problems with datatypes in access? 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.

Also, I did try ODBCng after I read a couple reports on the performance
numbers, but I found it didn't have the same ldap support the official
one did, and that's a pretty big necessity for us. Any help would be
much appreciated. TIA

Thomas R. Hart II
tomhart@coopfed.org

On Fri, Nov 23, 2007 at 03:48:01PM -0500, Tom Hart 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 that field access
> starts complaining about not being able to join on a field with a "memo"
> type. I think this might be a bug in the odbc driver, but I want to
> check my bases. Has anybody else run into this issue, or heard anything
> about the odbc driver having problems with datatypes in access?

For the text-memo thing there are options in the configuration (a
tickbox xsomewhere). I think it's called text-as-varchar switch or
something. There were some other config options that I also changed,
but it's been too long ago to give anything specific.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

=-- 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.