Microsoft Access to PostgreSQL

You 've written an application utilizing Microsoft Access.
Your database is growing larger and is becoming slower.
You don't like Access's behavior on a multi-user environment.
You want to keep your existing Access application as a front-end (all your business rules are there after all).
You want a solution that is scalable, reliable, Open Source.

Fear not!
Here is the solution.

• Read the instructions in exportSQL3.2-dev (VB code) to export your schema and data into SQL files.
• Run the generated SQL files against your PostgreSQL db.
• Download the latest PostgreSQL ODBC driver and create a DSN for your PostgreSQL db.
• Use Access' Link Table funtionality to link to the tables of your PostgreSQL db.
• Your Access UI (forms, reposts, queries, macros, modules) should work seamlessly (with 1 caveat... So read below)

exportSQL3 already exists out there. I just made some enhancements to better support PostgreSQL, especially when its tables will be used by Access.

What exportSQL3.2-dev will do for you:
- DataType Conversion (Including OLE objects)
- Primary Keys, Unique Keys and Indexes.
- Default Values and Null constraints
- AutoIncrement fields
- Something I might be forgetting (so read the comments in exportSQL3.2)

What exportSQL3.2-dev does not (yet?) support.
-Foreign Keys (aka Relationships)

Use the latest PostgreSQL db server and ODBC driver and you should not face any problems with non-English characters, as long as your PostgreSQL database encoding is in UTF-8. (The ODBC driver will handle the conversions properly).
Also, when running the INSERT SQL queries (the ones in the "add" generated file), you need to make sure that your client where you run the queries from can support the encoding of the generated file. Free PgAdminIII will work, but it will choke on large files (>30 MB). Instead You can use Navicat PostgreSQL and execute the files as batches to your db server.

When creating the DSN make sure you choose the following options (careful, different from defaults):


You say: Cool, I'm sold, what's the 1 Caveat????

Here it is: Microsoft Access does case-insensitive comparisons in queries. PostgreSQL does case-sensitive comparisons.
When working with a Microsoft Access table, SELECT * FROM SomeTable WHERE Name = "SmItH"
can return records with corresponding name 'Smith'.
When working with a PostgreSQL linked table, it can't.
So your Access GUI might require some modifications after all.

Enjoy...