In order to do more in-depth analysis of our student data, we have recently setup ODBC access to our online SIS, which is running on PostgreSQL. My goal was to have Access connect to the PostgreSQL data source, so I could create queries (views) in access, which I could then import into Excel. While Excel should easily connect to Access, as they are both Microsoft products, this has NOT been the case. But now that I have been able to figure out how to get it to work, I’m posting the solution for anyone else who might run there head into the same brick wall.
Before I go too much in depth, I should share the specific software versions I’m using. As there might not be the same problem with other versions. I’m using Access and Excel 2013 64-bit, and all of the files I’m using are being saved as the latest versions (.accdb & .xlsx). Our SIS provider is now using PostgreSQL 9.4, but this was a recent upgrade, and some of the problems didn’t occur until after the upgrade. We are using the PostgreSQL 9.03.0300 64-bit ODBC drivers.
Issue #1: Do Not Use Microsoft Access Dates
Our SIS provider stores dates in a numeric/text format of YYYYMMDD. So when I first started to create some queries/views in Access to be able to send later to Excel, I thought I would be clever, and convert these to date fields in Access, as I had thought that this would be easier for Excel. But Microsoft has a major bug in their software that whenever I would try to connect to my Access views, they would immediately give me an error message; and it didn’t matter how I connected, whether it was directly connecting to Access, using ODBC, or using Microsoft Query. But as long as I didn’t try converting the dates, then it wasn’t a problem… Until our SIS provider upgraded to PostreSQL 9.4, when new problems arose…
Issue #2: Use Microsoft Query to Connect to Access via ODBC
While it seems ludicrous to me that connecting straight to the Access file from Excel would not work, at least with PostgreSQL 9.4, it doesn’t. But I had seen in some forum that someone having similar problems to me said they had solved it by using Microsoft Query instead… And Voilà, it worked for me too.
Overarching Issue: Microsoft Software Has Issues
I recently read an excellent blog article about PostgreSQL vs. MS SQL Server, and I agree with nearly everything that is said, but it doesn’t only apply to MS SQL Server. In my many years of dealing with Microsoft Software, it is so common for them to make it difficult to use their software together, which is their whole sales pitch. Sure, they make some connecting pieces, like Linked Servers in MS SQL Server, but then they don’t document it well, and have obscure error messages with poor log files, and little good support, that allows you to figure out why things aren’t working. This same problem happened here. I got ODBC Error 3151, which apparently is a very generic error message, and I didn’t have any way of diving in deeper to see what was the problem. Instead I had to guess and check, and eliminate possibilities along the way. In some ways I think it is amazing that I even found a solution, and I had just about given up.
In the future I want to stop using Access as my middleware, and instead use my own PostgreSQL server, where I have the permissions to create my own Views, and import these into Excel. I will still likely use Excel, because honestly, I have not found a better spreadsheet program, as both OpenOffice and LibreOffice cannot do as much as Excel can, and R does not have as good of a user interface. Although, I plan to continue to learn Python and R more, and try to do more things in them, when appropriate, including pulling myself away from VBA in Excel, and instead using Python in Excel.