Re: Domains, casts, and MS Access

On Wed, Aug 4, 2010 at 8:49 PM, Richard Broersma
<richard(dot)broersma(at)gmail(dot)com> wrote:
> On Wed, Aug 4, 2010 at 3:41 PM, Peter Koczan <pjkoczan(at)gmail(dot)com> wrote:
>
>> Yep, that's the stumbling block we're running into. ODBC and these
>> fields' assumptions of true/false are at odds. I'm trying a few other
>> things with casts in the meantime to see if they'll work.
>
> Well there is a solution that I've been toying around with.  In
> PostgreSQL, there are many data-types that cannot be expressed
> directly in an MS-Access Linked table.  For example, composite types,
> arrays, range types, hstores, postgis types et.al.   However, most of
> these types can be decomposed in to base types that can be express in
> linked tables.
>
> The key is using update-able views to decompose the data for Access
> and re-assemble it before it transmitted back to the base table.  The
> same can be done for boolean datatype.
>
>
>> Does anyone know if another product, like OpenOffice Base with its
>> native postgres driver, does any better?
>
> From my limited experience, I believe is does do better.  The
> following blogs as a few entries about using Base:
>
> http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html

The goal of this is to be as straight a port as possible (as part of a
larger project that's a pretty straight port). The update-able views
and using Open Office are good ideas for when we finally get around to
redesigning the database, whenever that will happen.

Anyway, we got this mostly working. There were a couple other quirks
we found that we have since fixed.

- Access does not like LongVarChar types to be primary keys. If you
are keying on text types, set TextAsLongVarchar=0. It's probably not
the best idea to have text as a primary key in general, but sometimes
that's what the legacy gives you to work with.

- To fix the incompatibility in the bit/boolean type, we mapped the
drop-down menu input to have "Yes" == 1 instead of -1. We had to do
that for each input. It was tedious, but workable. Again, this was
necessary because of legacy and the other workarounds we put in to
account for it.

Thanks for all your help.
Peter