[Fwd: Re: Access 97 DB to Postgres Migration Questions]

Raymond wrote:
> Have an Access 97 database being migrated to Postgres 7.3.3. Access 97 clients
> will query and write to the Postgres database across a WAN via ODBC.

When you say "Access 97 clients" - are you meaning the clients accessing
the database are using Access 97 as the front-end? That is what I am
taking as your meaning.

One thing I ran into, and this concerns Memo fields (and issues
regarding updates and speed when accessing them) - I thought I would
throw it out, to help others.

Basically, my front-end application, which used an Access 97 back-end
database, was written in VB6, and used DAO. When I converted the
application over to use PostgreSQL via ODBC, using the latest ODBC
driver, I was having issues with updating TEXT-type fields (which I had
converted the memo fields to) in a certain manner.

In order to get around this issue, I changed the TEXT fields to large
VARCHAR fields (on the order of 80,000 - 128,000 bytes wide) - but then
any SQL selects involving those fields ran *very* slowly. I hacked my
way around this problem, restructuring the SQL and how I used the
statements, coding my way around issues...

I ended up running into some problem I couldn't get past - I don't
remember what it was, probably speed related or something.

In the end, what I found was that if I switched to using ADO instead of
DAO, the problems went away. I converted those fields back to TEXT
fields, the speed issue didn't seem a problem anymore, and my update
issue on those fields was solved as well (however, I have recently run
into other problems stemming from things that DAO allowed that ADO
doesn't allow - but that is another story).

I think if I was developing *any* application in the future that used
any database backend via ODBC (or maybe otherwise) - I would stick to
straight SQL for all interaction with the database - it just seems like
the best way to go all around. If you can do this with your Access
system, that would probably be best.

> 2) Is an ODBC type conversion table available for Access to Postgres?

As noted, most types should convert OK. As I noted above, memo fields
map to TEXT fields. Autonumber fields will likely become SERIAL sequence
fields. Also, yes - via the ODBC driver, booleans act "funky" - there is
a flag that you can pass (easiest if you set up a DSN-less connection,
if possible - otherwise configure the driver) to switch the operation to
the way Access and VB expect (IIRC, PG has it as 1=true, 0=false, while
Access likes -1=true, 0=false) - in my application, I just left the
driver as default, and had code that during the read, would take the
value and CBOOL it (to convert the 1/0 to true/false), and on a write,
would take my variable and ABS() it (to convert true=-1, to +1 - and
false=0 stays 0) - this worked OK for me. Not pretty, but it works.

> Lastly any caveats or recommendations from those with previous Access 97 /
> Postgres experience would be greatly appreciated.

Your greatest hurdle may be the actual conversion of the data from
Access to PostgreSQL. There are Access templates and such to do it.
Myself, because of the control I wanted/needed - I ended up writing a VB
program to convert the data, which would squirt the data over the ODBC
connection to the new tables. My program can convert several of the
types properly. It can also convert just the table defs, or the data as
well (so you can convert the tables, then later the data "over-and-over"
if you need to, without recreating the tables). It also has a bunch of
other functions. It isn't perfect, but it worked OK for me.

Good luck on your conversion, and I hope my comments help you or someone
else out there in the future...

Andrew L. Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.