re: [INTERFACES] Postgresql vs. Access97, TransferDatabase - Mailing list pgsql-interfaces

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.