Re: Make MS Access "UPDATE" PostGre SQL Table

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