Excel Errors With Access Database

Recently, I’ve heard from a couple of people who were getting strange errors in Excel, when they tried to get data from an Access database. In both cases, things had been going smoothly for a long time, then suddenly stopped working. What was going wrong?

These Excel errors were different from the Read Only database problem that I wrote about before.

Problem 1 – Data Type Mismatch

In the first case, a product pricing workbook has macros that pull the latest prices from the Access database. Then, other workbooks link to that one, to create annual price lists.

The system has been working smoothly for about 10 years, then one day, spit out a mysterious error:

Run-time error ‘-2147217913 (80040e07)’;

Data type mismatch in criteria expression

What’s the Problem?

This error message appeared in the middle of a macro that imports data from about 20 queries in Access. So, the first step was to figure out which query was causing the problem.

The Debug button took us to the highlighted line in the code where the guilty query was named. We ran that query in the database, and got a similar error message:

Data type mismatch in criteria expression

Follow the Query Trail

That was a good start, but unfortunately this was a Totals query, based on a series of other queries. So, I followed the trail of queries, back further and further, looking for the root of the problem.

Finally, I found the culprit – the problem query has a formula to calculate the width of a part, based on its product code. The formula removes the first one or two characters, then uses the numeric part of the product code.

Letters and Numbers

Recently, some new product codes had been entered, and one of was incorrect – it started with 3 letters, instead of one or two. The query tried to convert the letter/number string into a number, and of course that didn’t work.

Instead of showing a number in the results, the query showed #Error.

Later in the sequence of queries, another query tried to sort that column, and that’s when the “Data type mismatch in criteria expression” error appeared, even though there weren’t any criteria in that query.

Fix the Problem

Once we found the problem, it was easy to fix. We just had to go to the product codes table, and correct the bad entry.

I could add some error handling to the width calculation formula too, to prevent future problems. But since that’s only happened once in 10 years, I’ll leave it as is for now.

Sometimes when you “fix” things, it breaks something else, further downstream!

Problem 2 – Data Could Not Be Retrieved

In the second case, another system had been working well for a long time, and started showing an error this month.

“Data could not be retrieved from the database. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again”

Well, that message had more words, but just about as much helpful information as the first one!

Check the Data

There weren’t any obvious problems – the database did exist, and the person with the problem was the “database administrator”.

This problem reminded me of the “Data Type Mismatch” mystery though, so I suggested looking at the query results in the Access database.

Here are the steps to do that:

Open the query in Access, and see if you spot any errors in the newer records.
Or, try sorting each column — one column might show an error message when you try.
Try to filter that column, to locate the record(s) with the errors.
Fix the errors, and run the query again, to ensure that fixed the problem

Fortunately, the problem record was easy to find and fix, and things are running smoothly again. Bad data might not be the cause of the problem if you encounter the same error message, but it’s a good place to start.