Thread: Make MS Access "UPDATE" PostGre SQL Table

Barry,

Per the COPY documentation:

COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already).

...So it's not destructive. If you simply need to append the data in the CSV to a table, then COPY might be all you need:

http://www.postgresql.org/docs/8.3/static/sql-copy.html

For updates, one strategy might be to use a "staging" table which you truncate, COPY FROM the file to the staging table, and then use a stored procedure to update your main table from there. I haven't benchmarked this, but I would be shocked if this didn't yield superior performance to a solution which implements  the update logic in the front-end.

Good luck,
Dave

_______________________________________
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Pettis, Barry
Sent: Monday, March 24, 2008 6:08 AM
To: pgsql-general@postgresql.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
MSAccess to retrieve data from PostGre by linking tables and have done so without error.  However, I am now in need of
beingable 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
atit 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
firmon 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

On Mon, Mar 24, 2008 at 07:08:13AM -0600, Pettis, Barry wrote:
>         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.

If you've linked the tables (i.e. you've got an ODBC data source set up,
and not just imported the table definitions and data) then you should
be able to write DML queries as normal and MS Access will take care of
routing everything correctly (if very sub-optimally at times).  I.e. if
you've got a table linked as "pgtbl", then you can do:

  UPDATE pgtbl SET value = value + 1 WHERE id < 100;

in a normal query in Access and it will automatically get turned into an
update query hitting whatever table it is in PG.

> However, I am now in need of being able to update a PostGre table.  The
> source will be a .csv file.

That sounds a little confused, you need to get the data into the
database before you can do anything like UPDATE it.

>         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.  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.

A COPY in PG will just append the data onto the table you've asked it
to.  It's exactly the same as running several INSERTs, just a bit faster
if you've got thousands of rows.

Be aware that Access only knows how to pass through a limited subset
of SQL.  So if you want to use COPY you're going to need to bypass the
linked tables somehow.  I tend to use the psql program here, but I
suppose you could drive it from DAO or ADO as well.  Infact I tend to do
most of my development using psql and then move any needed queries into
my code in Access after getting them working.

> Thoughts on how to do the entire front end in Access.

I've written GUI code in Access using PG on the backend.  It's *much*
more robust (and faster) than using Access as the database.  I am moving
away from using the linked tables feature and tend to use DAO/ADO
directly.  If you want to allow the users to use the fancy in place
table editing then you need to use linked tables though.  ADO gives
much better error messages, but DAO is needed when you want to set the
recordset of anything to be the result of a query.

Access is good when you've got lots of other people who already use it
and just want to write simple queries and the query builder that Access
provides is enough.  If neither you or your users are accustomed to
Access then I'd probably look elsewhere.


  Sam

Pettis, Barry wrote:

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.  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.

 

        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.

 

Or

 

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

 

 

Another option is use SQL pass through to append records from ACCESS table into Postgres table that is open in Access.
http://support.microsoft.com/kb/303968
http://msdn2.microsoft.com/en-us/library/bb208861.aspx
the command would look something like this

Insert into PosgresTable (f1, f2, f3,) Select f1, f2, f3 from  AccessTable

Once you get a handle on SQL pass through in Access that knowledge will make using ADO.NET allot easier.

a starter look at ADO.NET 
http://msdn2.microsoft.com/en-us/library/ms978510.aspx?s=6

Postgres has .net provider http://pgfoundry.org/projects/npgsql

Pettis, Barry wrote:

> 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

>         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.
>

 From Access I would think you can use the standard Access import
commands to import the file just as you would into an Access/SQLServer
based table. (I haven't used Access much so don't quote me there) But
then if that worked you probably wouldn't be asking here.

The other option I would think of is using a script that would copy the
file to the server and then run the COPY FROM... command (the file will
need to be accessible to the server if your are sending it the command
as SQL to execute)


The way I would go, is having psql on the client side run it's version
of the copy command. (though you may need to install psql on client
machines along with the Access runtime)

On *nix you would do something like -
echo "\copy mytable from mylocaldata.csv" | psql

I'm sure you could do a vb script to do that for you if you wanted to
stay in Access.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

    Justin wrote:

> Another option is use SQL pass through to append records from ACCESS
> table into Postgres table that is open in Access.
> http://support.microsoft.com/kb/303968
> http://msdn2.microsoft.com/en-us/library/bb208861.aspx
> the command would look something like this
>
> Insert into PosgresTable (f1, f2, f3,) Select f1, f2, f3 from
AccessTable

It seems to me that SQL pass-through is kind of contrary to what the OP
needs.

A query in SQL pass-through mode will be executed as if it was typed in
psql, that's the point of this mode. So refering to an Access table
like your example query does is not going to work.

What the OP can do is attach the CSV file in Access so that it will
appear as a table and should be usable as such in an update or insert
query within Access query builder. It's quite straightforward, there's
no need for temp files or COPY.

 Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Daniel Verite wrote:

    Justin wrote:

Another option is use SQL pass through to append records from ACCESS table into Postgres table that is open in Access.
http://support.microsoft.com/kb/303968
http://msdn2.microsoft.com/en-us/library/bb208861.aspx
the command would look something like this

Insert into PosgresTable (f1, f2, f3,) Select f1, f2, f3 from 

AccessTable

It seems to me that SQL pass-through is kind of contrary to what the OP needs.

A query in SQL pass-through mode will be executed as if it was typed in psql, that's the point of this mode. So refering to an Access table like your example query does is not going to work.

What the OP can do is attach the CSV file in Access so that it will appear as a table and should be usable as such in an update or insert query within Access query builder. It's quite straightforward, there's no need for temp files or COPY.

Best regards,

TO quote the OP "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."

So i thought it would be nice to point  the OP in the right direction in getting that to work. learning  how to setup connection strings and write SQL queries in access will carry over to pretty much everything else.