re: [INTERFACES] Postgresql vs. Access97, TransferDatabase

Okay, to provide some helpful feedback to others.... I readily admit that
I am still a novice at this, so feel free to chime in and correct any
misunderstandings that I may have.

First, I've been trying to transfer a large dataset from access to postgres
using a variety of methods:

0) copy command - never could get this to work. The copy command requires
postgres superuser access for backend processing, which I did not have.
There is an alleged \copy command that I could not work either - it kept
processing it as a connect request.

1) Cut and paste from access table to linked table:

This is very slow and tedious, as one writer indicates. Further, it is
quite sensitive
to data type and field name issues. Just because Access allows you to
do something doesn't mean postgres or odbc will.

2) Macro to TransferDatabase - if your table will map PERFECTLY 1:1 to the
postgres table, this may work.

3) Export table - I believe this is the same as cutting/pasting except that
it
attempts to create the table on the backend as well. I did not have
much
success with this either, due to the hidden fields associated with
replication.

4) SQL - this is what I finally used to update the postgres database. Once
I had
the tables defined in an acceptable manner, I created an update query:

INSERT INTO tblproducts1
SELECT productid AS productid, ... BarCode10 AS BarCode10
FROM tblproducts

The simplest form of this insert is:

INSERT INTO tblproducts1 SELECT * FROM tblproducts;

but this only works if your tables map PERFECTLY. using the
field AS field format keeps Access and the ODBC from thinking too
much. I also gave up trying to use the drag/drop gui in the query
builder.

5) See item 0. Because I could not get the privs or otherwise of the copy
command
to operate properly, I considered creating a utility to generate a
series of insert into
commands, parsing a comma delimited file as a data source. I believe I
will still
craft this (since I have more data to load). This would allow me to zip
a text file
up and ftp it to my server location. If I get this working, I'll be
happy to provide it
to anyone who wants it (maybe post the source on a postgres site?).

Beware: comma delimited files produced by Access simply will not be
imported
by postgres. For example, a null text field is '' (two single quotes).
Access does not
generate this.

Suggestions:

+ Read the postodbc faq. one of my problems was a missing type - I just
needed
to create it.

+ Watch out for databases that have been replicated. I have yet to locate
the
extra fields and remove them. These will give you a large amount of
grief.

+ Watch out for 'bad' characters in field names.

+ Use the psqlodbc.log file judiciously.