PostgreSQL: alternative ways to connect to database

connectivityexcelpostgresqlvba

THIS IS A THEORETICAL QUESTION. I am posting it on this site because of this and because I think that database administrators could provide more accurate insight. The line that separates the two sites is still not crystal clear to me so I may have got it wrong.

Long story short: there is a certain Postgres database, and there is a need of creating a form like access to it, and I have run out of ideas and/or lack some knowledge as to how to do some things (in case they can be done at all)

There are some fields of a single table that need to be accessed to to read and edit them on demand. The rest of the data is imported into the database following a traditional ETL process, but these pieces of information need to be inserted manually and on demand, and the users will be business people who are incredibly fond of Excel.

Below are some of the options I have considered and the reasons as to why they have been rejected:

  1. Web form: the time needed to develop it is not affordable.
  2. Distribute an Access Application based form with an ODBC connection to DB: every single user would have to install both MS-Access and the psqlODBC driver and they are strongly against this *sigh*
  3. Having a similar form to that based on Access, but developed in Excel: from what I have seen and tried, pulling data from PostgreSQL to Excel is viable although not so trivial (I have had some issues with the casing of most names, as Windows is case insensitive while Postgres is not), but the reverse is not. Maybe there is a VBA solution to do the push? I know some VBA and could manage to get a solution if this is viable. I know how to do this through VBA in Access but the VBA used in Excel has a different set of resources and I don't know them that much.
  4. Having this single table managed by a FDW (Foreign Data Wrapper) and distribute an Excel file linked to it (with a form-like "interface"): from my research I have found that importing/pulling data from a csv file is a common task but I have not found anything on reversing the data flow: editing the "remote" csv file from Excel. If this can be done, truly dynamically linking a csv file to excel so that it can be updated, it could pose as a viable solution

Any suggestion or insight as to how can this be done will be greatly appreciated.

Edit:
The options number 3 and 4 described above are oficially rejected. My brain must have gone numb or something to even consider Excel pushing data a possibility. It has never been able to do that, it just pulls, visualizes and analyzes data. That's it. That's all there is to Excel.

Edit2:
We have successfully made it clear to the client that what he wants from this project under these conditions is plainly impossible. So they have reconsidered it and the constraints set have changed. I will be doing a web app form, which is the best solution in terms of software needed to run it.
I hope this can be of help to anyone who ever runs into a similar situation. Thanks for the attention to those who cared to read (and write) 🙂

Best Answer

A poor man's solution might be simply installing pgAdminIII, setting up the connection to the database and using it's built in filtering and data view/modification functionality.

I admit this does not give you a very good user experience (about 8 clicks to get to the table you want to view/edit), but it is very easy to set up.

One important thing: you may want to restrict access to the tables that are really editable by your users. A good approach might be:

CREATE ROLE limited_editor;

GRANT USAGE ON SCHEMA your_schema TO limited_editor;
GRANT SELECT, INSERT, UPDATE ON your_table1, your_table2, ... TO limited_editor;
REVOKE ALL ON ALL TABLES IN SCHEMA your_schema FROM public;

GRANT limited_editor TO alice, bob, ...;

When doing this, revoking access from public might have some unwanted effects on other users' privileges. Fix them accordingly (for example, by setting up a more powerful editor role, that can be granted to other users).

Related Question