Can I link an MS Access Application to Postgre SQL on a Linux Server? Can / Should I use ODBC?

Question

I write MS Access / VB / SQL Applications, customized. One of my clients has a truly massive Access application I wrote them, and their number of active users have just expanded to over 15. Running into more data corruption issues, I am recommending they port to an SQL backend for stability. (Of course).

They would prefer to invest in a less expensive option and go with a Linux Server. I have used ODBC to hook an Access front to SQL backend plenty of times. But I have never touched Postgre.

I am looking for comprehensive detailed data on if this is a good strategic solution for a design plan, or if it is even a possibility. Perhaps there is an easier solution and I am barking up the wrong tree...

Answer 1

Yes, this is absolutely possible. I've not run into any major issues - the biggest I had was getting the Postgres boolean's to match up correctly - but it all depends on how you setup the ODBC connection and the actual application. I've found the ODBC connection stable and usable. If you really anticipate a larger number of users, you really ought to consider rewriting the whole app into something other than Access.

Overall, I'd definitely recommend Postgres as a backend via ODBC. Once you get used to a few of the differences, a lot of people really end up enjoying Postgres as a DB.