Using MS Access with PostgreSQL - Postgres OnLine Journal

Many in the PostgreSQL community use Microsoft Access as a front-end to their PostgreSQL databases. Although MS Access is strictly a windows application and PostgreSQL has its roots in Unix, the two go well together. A large part of that reason is because the PostgreSQL ODBC driver is well maintained and has frequent updates. You can expect one new ODBC driver release every 4-6 months. There exist only 32-bit production quality drivers. The 64-bit driver is of alpha quality. In addition to other front-ends to PostgreSQL that utilize the ODBC driver used by Windows developers, there is VB 6 (VB.NET/C# use the ADO.NET driver also very well maintained), Visual FoxPro, Delphi, to name a few).

People who have never used Microsoft Access or anything like it and consider themselves hard-core programmers or database purists, dismiss Microsoft Access as a dangerous child's toy, causing nothing but grief when real programmers and database administrators have to debug the disorganized mess of amateurs. They dream of the day when this nuisance is rid of and their company can be finally under the strict bureaucratic control of well-designed apps that no one cares to use.

Beneath the croft of this dinkiness/dangerous toy is a RAD and Reporting tool that can connect to any database with an ODBC or ADO driver. It serves the unique niche of

Empowering a knowledge worker/beginner programmer/DB user who is slowly discovering the wonders of relational databases and what time savings such a tool can provide.
On the other side - it is inviting to the pragmatic (lazy) database programmer who has spent precious time to investigate its gems. The pragmatist sees it as a tool which provides a speedy development environment and intuitive reporting environment. It allows one to give more freedom to less experienced users, thus relieving one of tedious requests for information. By using it as a front-end to a strong server-side database such as PostgreSQL, it allows one to enforce a sufficient level of data integrity and control. The pragmatist realizes that often the best way to maintain order is to not fight disorder because the more you try to restrict people's freedoms, the craftier they get in devising ways of circumventing your traps. The pragmatic programmer also takes the view of Give a man a fish and he will pester you for more fish. Teach a man to fish and he will help you catch bigger fish.

In this article - we'll walk thru:

How to install the PostgreSQL ODBC driver and gotchas to watch out for
How to link to PostgreSQL tables and views via Linked tables
Pass-thru queries - what they are and how to create them?
How to export access tables and even other linked datasources to PostgreSQL - e.g. using MS Access as a simple exporting/importing tool
Quick setup of a form that uses the new TSearch functionality in PostgreSQL 8.3

For this example we will be using Microsoft Access 2003, PostgreSQL 8.3 RC2. For the database, we will be using the pagila 0.10 database (8.3 version).

Installing PostgreSQL ODBC Driver

The latest PostgreSQL 32-bit ODBC Driver can be download by choosing a mirror from http://wwwmaster.postgresql.org/download/mirrors-ftp and then navigating to the pub/postgresql/odbc/versions/msi/ folder. The current version is psqlodbc_08_03_0100.zip which was released Jan-22-2008. For those who desperately need 64-bit ODBC, you can compile your own or try the AMD 64-bit test version.

Unzip psqlodbc_08_03_0100.zip
Run the psqlodbc.msi file (If you have an older version of the PostgreSQL driver, uninstall it first before installing the new one)

How to link to PostgreSQL tables and views via Linked tables

Create a blank Access Database
Go to Files->Get External Data->Linked Tables As shown below
Click on drop down and switch to ODBC Datasources as shown here -
Switch to File Datasource. Note we are using File DSN instead of Machine Datasource because File DSN string gets embedded in the MS Access Database, therefore you do not have to setup the DSN on each computer that will use the MS Access Database. Machine DSNs have to be setup on each individual pc. File DSNS are also normally kept in files that sit in C:\Program Files\Common Files\ODBC\Data Sources and this default path can be changed from ODBC manager to a Network location if you want users to be able to share File DSNs.
Click New Button
Select driver as shown here. Note: in this picture we have selected the ANSI driver since our database is WIN-1252 encoded. Choose Unicode if your database encoding is UTF-8 or a non Latin Encoding, choose ANSI if your database encoding is SQL_ASCII, EUC_JP, BIG5,Shift-JIS, or a LATIN character set
Click Next and type in a name
Click Next and fill in relevant server, db.
Click the Connection button and uncheck Bools as char as shown
Click the Page 2 button and check True is -1, and uncheck updateable cursors as shown and then click OK
Now select the tables you want and click Save Password.
If you are missing primary keys on tables, Access will prompt you for what fields or set of fields you would like to use as the primary key. This doesn't make any structural changes to the actual table, but in the linked structure, Access will pretend this is the primary key and use that accordingly for table updates and such. This is particularly useful for views where the concept of primary keys does not exist and you want your updateable views to be updateable from Access. If you click OK or Cancel to the question without picking a set of fields, that table will be marked as readonly, which is the desired behavior for a lot of reporting views.

Pass-thru queries - what they are and how to create them

Access has a query feature called Pass-thru Queries available in the Query Designer. What this lets you do is pass a native PostgreSQL query directly to PostgreSQL so that it is not translated by the JET driver. Note pass-thru queries have visibility into the PostgreSQL db, and not your access database so don't expect to be using Access tables in them.

Pros

You can use native PostgreSQL functions and every other sweet function in PostgreSQL that Access has no clue what to do with such as full text search queries and Postgis spatial queries.
Skips the JET translation layer so is faster especially if you are joining with other tables in PostgreSQL
You can reference PostgreSQL tables and views you don't have linked in.

Cons

Unlike using linked tables in queries, you can't access any tables, jet functions, or custom access functions you have in your access database
Pass-thru queries are never updateable.

One example use is to for example use the sophisticated full text functionality in of PostgreSQL directly in MS Access. Below is an example using the Pagila database.

Create a new MS Access Query and select Design View and don't bother picking any tables
Under the Query menu choose -> SQL Specific -> Pass Through as shown
Type SELECT * FROM film WHERE fulltext @@ to_tsquery('fate&india'); in the query window
Click the Properties icon
In the Properties window - click the ... next to and pick the DSN you had created earlier and choose to save password as show
Close the window save the query, call it qryFilmSearch and run

Using Microsoft Access as an Exporting/Importing tool

In addition to linking tables, Microsoft Access can be used as a simple conduit for importing and exporting data in and out of PostgreSQL.

To export data to PostgreSQL from any linked table or physical table in Microsoft Access - do the following:

Rename the table to the name you would like it named to.
Make sure the default schema of the user you are using in Postgres, is the schema you want to export the data to.
Go to File->Export-> Select ODBC Datasources which is way at the bottom and select the DSN you had created. One gotcha here is that PostgreSQL will maintain the casing of the fields in the table and the table name, so its best to rename all your fields to lowercase first so you don't have to be quoting them everytime you use them.

To import data from PostgreSQL into a Microsoft Access database for distribution etc. Do the following

Choose File->Get External Data->Import
Again select ODBC Datasource and use the DSN we created
Select the set of views, tables etc you want to import and then click OK.

Building a form with a Pass-thru Query that uses TSearch

In this little example, we'll demonstrate how to create simple form bound to a pass-thru query and programmatically change the pass-thru query via user input.

First bind the form to the passthru query you created above and just build the form with a wizard
Next place a text box on form and name it txtSearch
Next add a button on the form and name it cmdFindFilm and label it Find Film

Now put in code for the onclick event of the button that looks like this

To programmatically change a pass thru query in response to a user's input so you can use it as a record source of a form, you can write something like this:


Private Sub cmdFindFilm_Click()
    Dim qdf As Object
    Dim tSearchText As String
    If Me.txtSearch.Value > "" Then
        tSearchText = Replace(Replace(Me.txtSearch.Value, " ", "|"), "'", "''")
        Set qdf = CurrentDb.QueryDefs("qryFilmSearch")
        qdf.SQL = "SELECT * FROM film WHERE fulltext @@ to_tsquery('" & tSearchText & "') ORDER BY ts_rank(fulltext, to_tsquery('" & tSearchText & "')) DESC, title"
        qdf.Close
        Me.Requery
    Else
        MsgBox "Please type in a search criteria"
    End If
End Sub

Below is a snapshot of our finished form with a sample query we ran. Aint it cute. TSearch form in MS Access

Gotchas

File DSN does not let you change the port number

I suspect this is a bug. When setting up file dsns via ODBC manager, for some reason the port is greyed out so if you are not running on the standard 5432 port, you have to edit the generated .dsn file manually. On top of that the file doesn't get generated with all the necessary info if a successful connection is not made. To get around this annoyance, you can go into .dsn file (in this case C:\Program Files\Common Files\ODBC\Data Sources\pagila.dsn) and change the port number before linking. Remember, once a table is linked with a file DSN, the actual DSN config gets encoded directly in the linked table meta data so you do not need to make the File DSN file accessible to users who use the access database. This is not true for Machine DSNs, only File DSNs.

Below is something like what the DSN file should look like.


[ODBC]
DRIVER=PostgreSQL ANSI
UID=pagila
XaOpt=1
LowerCaseIdentifier=0
UseServerSidePrepare=0
ByteaAsLongVarBinary=0
BI=0
TrueIsMinus1=1
DisallowPremature=0
UpdatableCursors=0
LFConversion=1
ExtraSysTablePrefixes=dd_
CancelAsFreeStmt=0
Parse=0
BoolsAsChar=0
UnknownsAsLongVarchar=0
TextAsLongVarchar=1
UseDeclareFetch=0
Ksqo=1
Optimizer=1
CommLog=0
Debug=0
MaxLongVarcharSize=8190
MaxVarcharSize=255
UnknownSizes=0
Socket=4096
Fetch=100
ConnSettings=
ShowSystemTables=0
RowVersioning=0
ShowOidColumn=0
FakeOidIndex=0
Protocol=7.4-1
ReadOnly=0
SSLmode=disable
PORT=5432
SERVER=localhost
DATABASE=pagila

Tables Pre-fixed with schemas

One of our pet peeves is that when you link all the tables you want it prefixes the tables with the schema and its not schema.tablename its schema_tablename e.g. public_actors.

This is especially annoying if you use MS Access as a quick sql generator that you then use to paste back into your postgresql database as a view. This is an issue when you try to link any schema supporting database in MS Access. E.g. public_actors just is no good. Just actors works fine if you have default schemas in place or do not have a schema segmented database (e.g. everything is in public). Below is a VB subroutine we use to strip off the schema prefix.


Sub StripSchemaName(schemaname As String)
    'schemaname that prefixes the table e.g. public
    '--EXAMPLE use from immediate window -
    '--  StripSchemaName "public"
    Dim tdf As Object
    Dim i As Integer
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, Len(schemaname)) = schemaname Then
             'plus 2 to strip the _ as well
            tdf.Name = Mid(tdf.Name, Len(schemaname) + 2)
        End If
    Next
    MsgBox "Done"
End Sub

Dealing with Booleans

One of the problems with using PostgreSQL as a back-end to MS Access is that Postgres has a true boolean data type where as MS Access has a Yes/No field which internally maps to -1 and 0. In earlier versions of PostgreSQL, there was an auto-cast in place to cast boolean to integer and vice-versa. This was later taken out. So now you get errors like operator does not exist boolean = integer when trying to do queries against these fields.

Note the below example is useful for transparently casting Access's (True/False (-1/0) to PostgreSQL True/False)

The below was adapted from Bahut ODBC PostgreSQL boolean mess. In Bahut's rendition he uses plpgsql functions. We revised to just use plain sql functions. The reason being is that in general when a function can be written in SQL, it performs much better than a plpgsql or other PL language written function, because the sql functions are more transparent to the Postgres query planner for appying indexes and so forth. In this case, the SQL variants are more succinct as well.


CREATE OR REPLACE FUNCTION inttobool(integer, boolean) RETURNS boolean
AS $$
   SELECT CASE WHEN $1=0 and NOT $2 OR ($1<>0 and $2) THEN true ELSE false END  
$$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION inttobool(boolean, integer) RETURNS boolean
AS $$
   SELECT inttobool($2, $1);
$$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION notinttobool(boolean, integer) RETURNS boolean
AS 
$$
  SELECT NOT inttobool($2,$1);
$$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION notinttobool(integer, boolean) RETURNS boolean
AS $$
	SELECT NOT inttobool($1,$2);
$$
LANGUAGE sql;

CREATE OPERATOR = (
PROCEDURE = inttobool,
LEFTARG = boolean,
RIGHTARG = integer,
COMMUTATOR = =,
NEGATOR = <>
);

CREATE OPERATOR <> (
PROCEDURE = notinttobool,
LEFTARG = integer,
RIGHTARG = boolean,
COMMUTATOR = <>,
NEGATOR = =
);

CREATE OPERATOR = (
PROCEDURE = inttobool,
LEFTARG = integer,
RIGHTARG = boolean,
COMMUTATOR = =,
NEGATOR = <>
);

CREATE OPERATOR <> (
PROCEDURE = notinttobool,
LEFTARG = boolean,
RIGHTARG = integer,
COMMUTATOR = <>,
NEGATOR = =
);

PostgreSQL is case-sensitive

One of the most annoying things for people coming from a Windows environment is that PostgreSQL is case-sensitive whereas MS Access in-general is not (except when querying case sensitive databases). Explaining this to users and training them on case sensitivity is just a lot of hassle, not to mention the time-loss of having to upper case things. Hopefully this will change in the future so that PostgreSQL supports different collation depending field by field similar to the way SQL Server 2005 does. Needless to say, when running a query in MS Access, one has three options:

Write your query along the lines of upper(somefield) LIKE UCase('abc%') and make sure you have a functional index on upper(somefield)
Use the custom data type such as citext which you need to compile yourself.
or Put functional upper(somefield) indexes on your common fields and use the freedom that PostgreSQL gives you to redefine varchar operators in your database by doing the below. NOTE that this gives you the benefit of not having to redefine varchar fields as citext or anything like that thus making it more portable to transfer back and forth between non-case sensitive dbs or use the same schema as non-case sensitive dbs. Note we couldn't do the below with text because that is defined high up and can not be overwritten. We can overwrite the behavior of varchars however because varchars get implicitly cast to text and use the text operators. By using PostgreSQL's operator overload feature, we can define special behavior for varchar when used in comparators. When Postgres sees there is such an operator, it will use that instead of cast varchar to text and using the default text operators. The downside is that this will not work with PostgreSQL text (NOTE: varchar in PostgreSQL/ANSI SQL maps to text in MS Access and text in PostgreSQL/ANSI maps to memo in MS Access - all very confusing) . In most cases this is a non-issue since most searches are done on short Access text fields rather than memo fields. NOTE: Use with caution. We haven't thoroughly tested this technique to catch all the possible situations where it can go wrong. It seems to behave correctly from our naive tests.
```
		
CREATE OR REPLACE FUNCTION ci_caseinsmatch(varchar, varchar) RETURNS boolean
AS $$
   SELECT UPPER($1)::text = UPPER($2)::text;
$$
LANGUAGE sql
IMMUTABLE STRICT;

CREATE OPERATOR = (
PROCEDURE = ci_caseinsmatch,
LEFTARG = varchar,
RIGHTARG = varchar,
COMMUTATOR = =,
NEGATOR = <>
);


CREATE FUNCTION ci_like(varchar, varchar) RETURNS boolean
AS $$
   SELECT UPPER($1)::text LIKE UPPER($2)::text;
$$
LANGUAGE sql;

CREATE OPERATOR ~~(
  PROCEDURE = ci_like,
  LEFTARG = varchar,
  RIGHTARG = varchar,
  RESTRICT = likesel,
  JOIN = likejoinsel);

		
	
```
Doing the above allows us to define a query like this in MS Access
Which yields:

And can now be written in SQL even in PgAdmin without all that messy upper lower stuff and still uses indexes if you have them defined on say upper(first_name) or doing range case-insensitive searches e.g. (customer.last_name between 'f' and 'h') :
```
		
			SELECT customer.*
			FROM customer
			WHERE customer.last_name 
			Like 'Farns%' OR customer.first_name = 'Jim';
		
	
```
Which will give you all customers with first name Jim or last name like Farns. Best of all, if you put in a functional index on last name and first name like below, it will use those indexes when doing equality or between ranges etc..