How to work on PostgreSQL or MySQL tables from a desktop database

Why keep the records in a server

A database file on one computer serves the person at that computer. Once several people need the same records at once, the records belong in a database server such as PostgreSQL or MySQL (or MariaDB, which grew out of MySQL and speaks to the same programs). The server settles who saved what when two people change the same record, checks every account, and is backed up in one place. A desktop program then gives people the forms and reports they are used to.

What you need from the server

• The server’s address and port: 5432 is PostgreSQL’s usual port, 3306 MySQL’s and MariaDB’s.
• The database’s name.
• An account and its password, made for this use.
• Whether the server requires an encrypted (TLS) connection, which it should whenever the connection leaves your own network.

In Microsoft Access: linked tables

Install the ODBC driver for the server: psqlODBC for PostgreSQL, or MySQL Connector/ODBC (MariaDB has its own). Pick the 64-bit or 32-bit driver to match your Office, not your Windows. Then, in Access, External Data, New Data Source, From Other Sources, ODBC Database, choose to link rather than import, and pick the tables.

A linked table looks like any other, and every change goes straight to the server. Access needs a primary key or unique index on each table to change its records; without one the table opens read only. Queries on linked tables run best when the filtering can be passed to the server, so keep their criteria simple.

Other ways in

• LibreOffice Base connects to an existing MySQL, MariaDB or PostgreSQL database from its Database Wizard, with its own connectors or through ODBC and JDBC, and builds forms and reports on the tables.
• DBeaver, free and open source, opens tables from almost any server as a grid you edit like a spreadsheet, alongside an SQL editor.
• pgAdmin for PostgreSQL and MySQL Workbench or HeidiSQL for MySQL and MariaDB are made for running and looking after the server, and edit tables too.

Doing it safely

• Do not open the server’s port to the internet. Reach it over your own network, a VPN or an SSH tunnel.
• Give each person, or each program, an account of its own with only the rights it needs. In PostgreSQL, for example: GRANT SELECT, INSERT, UPDATE ON orders TO shop_staff; leaves out DELETE and every other table.
• Require encryption on any connection that leaves the building.
• Keep the password out of shared files. A linked database saved with its password inside hands it to anybody who copies the file.

When the connection drops

A linked table needs the server. For work away from it, a program has to keep a copy of the table and send the changes back later, deciding what happens when a record was changed at both ends. Access has no built-in answer for this; it is something to ask of any tool you choose if you work on the move.