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
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).
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.
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.
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.
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.
SELECT * FROM film WHERE fulltext @@ to_tsquery('fate&india');in the query window
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:
To import data from PostgreSQL into a Microsoft Access database for distribution etc. Do the following
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.
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.
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
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
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 = = );
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:
upper(somefield) LIKE UCase('abc%')and make sure you have a functional index on upper(somefield)
Doing the above allows us to define a query like this in MS Access
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);
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..