MDB is the original Microsoft Access database format. It was replaced by the ACCDB format in 2007, but there still applications that use MDB files. For instance I have a handful of customers using Raiser’s Edge software for donor tracking, and this application still creates export files in MDB format.

One Raiser’s Edge customer recently contacted me about a report that kept crashing. There was no error message – Crystal would just shut down. The report had 32 tables and 31 joins. If he deleted one of the tables the report ran fine. As soon as he added the last table the report would crash.

In my testing I found that it didn’t matter which table was dropped. So it became apparent that the issue was a limit, somewhere on the number of tables or joins. But it was hard to determine where the problem lay without any error messages.

First I checked the SQL generated by Crystal Reports and that looked normal. I copied the SQL into a new SQL command but that had the same exact limit.

Next I tried the same report using different connection methods. Both OLEDB and ODBC failed in the same way as DAO.

Then I decided to see if the problem was in the MS Access engine so I copied the SQL From the report and pasted it into a new MS Access query. (I am one of those people who still use MS Access 2002.) The query would run fine in MS Access as long as I dropped one of the tables. If I added that last table the MS Access query would generate an error that said:

“Query is too complex”

I couldn’t find the official limits for MDB files but I did find a page that showed the limits for ACCDB files and it says that the number of tables in a query is limited to 32, which in some cases can be reduced even lower.  So apparently we hit the limit. And worse, when you exceed the limits for MS Access, the report can’t survive to give you an error message.  It just dies.