Accessing PostgreSQL and Oracle with Access and SQL Developer

Microsoft Office Access is a RDBMS that uses a GUI interface and RAD tools and a Jet Database Engine backend. It can also use external data stored in SQL servers such as MSSQL or Oracle, PostgreSQL, DB2, etc. via ODBC.

PostgreSQL is "the most advanced" open source database. It is a BSD-style licensed ORDBMS that supports advanced SQL features such as referential integrity constraints (foreign keys, column checks, etc), full ACID compliance, ANSI SQL compliance, views, rules, sub-selects, transactions, triggers, sequences, inheritance and has a built in language (PL/pgSQL) similar to Oracle PL/SQL. It also exhibits almost linear scalability up to 16 cores, being much more scalable then say, MySQL. As of version 8.3 it gets a performance and scalability boost too. The pgbench benchmark here shows an almost 50% performance boost (under certain workloads) from 8.1.

Using an ODBC connection you can use Access as a Rapid Application Development interface to develop Forms, Reports and Applications using PostgreSQL as a backend database, via PSQLODBC.

Install psqlodbc on your Access machine, and all you need to get started now is a PosgreSQL server (if you don't have one on your network you can install one on your Windows machine from:
http://www.postgresql.org/download/

Fire up Access and open up a database. We are going to create the ODBC connection and save the settings. First, let's export some table to the PostgreSQL database via ODBC. We can later link or import the table (in any database).



Create a new Data Source using the "PostgreSQL Unicode" driver. The goods database was created using "CREATE DATABASE goods OWNER cmihai ENCODING 'UTF8'" and we are planning on storing Unicode data.




Once you've selected a Server hostname (or IP), username, password and database (like the newly created "goods" database in your PostgreSQL server) you need to set some advanced options. Uncheck the "Bools as Char" box.



Check "True is -1":



Now we can Import a table using an external data source (it will import the structure and data of a Table in PostgreSQL as a table in Access. The tables will not be linked).

We can also Link tables using an external data source. This means that the data and structure of the table (schema) is only modified in the PostgreSQL database. We can link multiple applications to the same database (and tables). Just use Get External Data - Link to Data Source and select the PostgreSQL ODBC connection.




As you have noticed, PostgreSQL is case sensitive.

The linked tables are shown in the Table view with a sphere icon.



While you can use the Query Builder (in Design view or SQL view) to query data in the Access database (it can use both access tables and ODBC connected tables), there is an option to pass the SQL query directly to PostgreSQL, in Pass-Through mode.

SELECT public_clienti.nume, public_clienti.prenume, public_tari.tara
FROM public_clienti INNER JOIN public_tari ON public_clienti.tara = public_tari.tara
WHERE (((public_clienti.nume)="Gigi"))
ORDER BY public_clienti.nume, public_clienti.prenume;

The PostgreSQL query wouldn't use the public_clienti prefix, we can just do a simple Pass-Through query:



You can use Pass-Through queries in your Forms and Reports too.

Of course, you can also use Access with Microsoft SQL Server 2005 Express (freely available) or even the free Oracle 10g Express, using the SQL Developer Quick Migration tool to export MDB files to Oracle.



You can use the Quick Migration Wizzard:



Or the full blown "Oracle Migration Workbench Exporter for Access" to export your .mdb file for SQL Developer and Oracle Application Express.