Re: Make MS Access "UPDATE" PostGre SQL Table

_______________________________________
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Pettis, Barry
Sent: Monday, March 24, 2008 6:08 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Make MS Access "UPDATE" PostGre SQL Table
>>
Note:
Generally, it is best to post in plain text, rather than html.
<<

Hi,
        I'm fairly new to DBMS's and PostGre SQL in particular.  Most of my experience is with MS Access.  I've created MS Access to retrieve data from PostGre by linking tables and have done so without error.  However, I am now in need of being able to update a PostGre table.  The source will be a .csv file.

        I've seen a "COPY" command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data.
>>
Copy is a fast way to insert, that is a bit less flexible than ordinary insert statements:
http://www.postgresql.org/docs/8.3/static/sql-copy.html
<<

I need to be able to Update or Append data from a file.  Maybe going the route of a temp file then update from that.
>>
The COPY FROM command performs an APPEND operation.
Do you really want a MERGE statement or something else? Microsoft Access also lacks MERGE.
If you want to append, then just perform an insert/select.
If you want to merge, then you will need several steps to do it:
A. Perform an equal join of the spreadsheet against the PostgreSQL table, updating the PostgreSQL rows
B. Perform an equal join of the spreadsheet against the PostgreSQL table, deleting the spreadsheet rows
C. Perform an insert into the PostgreSQL table of the remaining spreadsheet rows.

Or something along those lines.
<<
        I'm using Access as that is a point of comfort for me.  I can write a .net procedure though I'm not exactly firm on the procedure.

Thoughts on how to do the entire front end in Access.
>>
1. Link the PostgreSQL table using an ODBC or OLEDB provider
2. Link the CSV spreadsheet using an ODBC or OLEDB provider or as a Microsoft Excel spreadsheet
3. Query these tables exactly as you would query any other tables.
<<
Or

Thoughts on whether to do it from a .net perspective.

MSAccess ( 2003 )
PostGreSQL ( 8.1.8 )

Regards,
Barry Pettis
CSO Atmel Corp