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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
