Monday, February 04, 2008

Using Base to access PostgreSQL databases

Just like Access can connect to a PostgreSQL database via ODBC or JDBC, so can Base. But the good news is we don't need to use ODBC or JDBC, as there is a native PostgreSQL SDBC driver for OpenOffice.

Download the file, start OpenOffice (if you start writer type Ctrl-W to close it) so you can access Tools - Extension Manager. Add the downloaded driver, then restart OpenOffice (you also need to exit the QuickStarter. Just right click the SystemTray icon - Exit).

Now you can start Base and connect to a PostgreSQL database:

In the Connections Setting type: "dbname=YourDBName host=YourDBServerIP"

Setup a username and password:

That's pretty much it for the database setup, now you can start creating your database. You can use data already available in the PostgreSQL server, or create new tables. You then build Queries, Forms and Reports based on those tables.

You can use the Table Design or Table Wizard to create tables:

You can use the Relationship Editor to create Foreign Key constraints:

You can use the Form Design and Form Wizard to create Forms and the Report Design / Wizard for Reports:

It's usually a good idea to take a look with psql every now and then. Base isn't exactly perfect when it comes to creating the database schema, and especially when it comes to constraints. Of course, neither is the SDBG drivers, so it's best to consider this "just for fun" for the moment.