{"id":666,"date":"2016-09-13T16:35:00","date_gmt":"2016-09-13T20:35:00","guid":{"rendered":"http:\/\/debradalgleish.com\/blog\/?p=666"},"modified":"2018-06-17T14:28:55","modified_gmt":"2018-06-17T18:28:55","slug":"excel-errors-access-database","status":"publish","type":"post","link":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/","title":{"rendered":"Excel Errors With Access Database"},"content":{"rendered":"

Recently, I\u2019ve 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?<\/p>\n

These Excel errors were different from the Read Only database problem<\/a> that I wrote about before.
<\/p>\n

Problem 1 – Data Type Mismatch<\/h3>\n

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.<\/p>\n

The system has been working smoothly for about 10 years, then one day, spit out a mysterious error:<\/p>\n

Run-time error \u2018-2147217913 (80040e07)\u2019;<\/p>\n

Data type mismatch in criteria expression<\/p><\/blockquote>\n

\"access<\/p>\n

What\u2019s the Problem?<\/h3>\n

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.<\/p>\n

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:<\/p>\n

Data type mismatch in criteria expression<\/p><\/blockquote>\n

\"access<\/p>\n

Follow the Query Trail<\/h3>\n

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.<\/p>\n

Finally, I found the culprit \u2013 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.<\/p>\n

Letters and Numbers<\/h3>\n

Recently, some new product codes had been entered, and one of was incorrect \u2013 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\u2019t work.<\/p>\n

Instead of showing a number in the results, the query showed #Error.<\/p>\n

Later in the sequence of queries, another query tried to sort that column, and that\u2019s when the \u201cData type mismatch in criteria expression\u201d error appeared, even though there weren\u2019t any criteria in that query.<\/p>\n

Fix the Problem<\/h3>\n

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.<\/p>\n

I could add some error handling to the width calculation formula too, to prevent future problems. But since that\u2019s only happened once in 10 years, I\u2019ll leave it as is for now.<\/p>\n

Sometimes when you \u201cfix\u201d things, it breaks something else, further downstream!<\/p>\n

Problem 2 \u2013 Data Could Not Be Retrieved<\/h3>\n

In the second case, another system had been working well for a long time, and started showing an error this month.<\/p>\n

\u201cData 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\u201d<\/p><\/blockquote>\n

Well, that message had more words, but just about as much helpful information as the first one!<\/p>\n

Check the Data<\/h3>\n

There weren\u2019t any obvious problems \u2013 the database did exist, and the person with the problem was the \u201cdatabase administrator\u201d.<\/p>\n

This problem reminded me of the \u201cData Type Mismatch\u201d mystery though, so I suggested looking at the query results in the Access database.<\/p>\n

Here are the steps to do that:<\/p>\n

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

    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\u2019s a good place to start.<\/p>\n

    Excel Errors With Access Database<\/h3>\n

    The lesson I\u2019ve learned from working with Access data in Excel is \u2013 Look for the Errors!<\/p>\n

    One tiny piece of bad data can bring an otherwise healthy process to a grinding halt!<\/p>\n

    _________________<\/p>\n

    Save<\/span><\/p>\n

    Save<\/span><\/p>\n

    Save<\/span><\/p>\n

    Save<\/span><\/p>\n

    Save<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

    Recently, I\u2019ve 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 … Continue reading “Excel Errors With Access Database”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1009,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-666","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-office-tips"],"yoast_head":"\nExcel Errors With Access Database - Debra D's Blog<\/title>\n<meta name=\"description\" content=\"If you see strange Excel errors with Access database data, try these steps to solve the problem. Data type mismatch or data could not be retrieved\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel Errors With Access Database - Debra D's Blog\" \/>\n<meta property=\"og:description\" content=\"If you see strange Excel errors with Access database data, try these steps to solve the problem. Data type mismatch or data could not be retrieved\" \/>\n<meta property=\"og:url\" content=\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Debra D's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-13T20:35:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-06-17T18:28:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png\" \/>\n\t<meta property=\"og:image:width\" content=\"356\" \/>\n\t<meta property=\"og:image:height\" content=\"212\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Debra Dalgleish\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Debra Dalgleish\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\"},\"author\":{\"name\":\"Debra Dalgleish\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/fc3f038bcaeda478f9c5c06cfe648735\"},\"headline\":\"Excel Errors With Access Database\",\"datePublished\":\"2016-09-13T20:35:00+00:00\",\"dateModified\":\"2018-06-17T18:28:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\"},\"wordCount\":719,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png\",\"articleSection\":[\"Microsoft Office Tips\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\",\"url\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\",\"name\":\"Excel Errors With Access Database - Debra D's Blog\",\"isPartOf\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png\",\"datePublished\":\"2016-09-13T20:35:00+00:00\",\"dateModified\":\"2018-06-17T18:28:55+00:00\",\"author\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/fc3f038bcaeda478f9c5c06cfe648735\"},\"description\":\"If you see strange Excel errors with Access database data, try these steps to solve the problem. Data type mismatch or data could not be retrieved\",\"breadcrumb\":{\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage\",\"url\":\"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png\",\"contentUrl\":\"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png\",\"width\":356,\"height\":212,\"caption\":\"access query errors 1\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/debradalgleish.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Errors With Access Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/#website\",\"url\":\"https:\/\/debradalgleish.com\/blog\/\",\"name\":\"Debra D's Blog\",\"description\":\"Behind the Scenes in My Online Business\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/debradalgleish.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/fc3f038bcaeda478f9c5c06cfe648735\",\"name\":\"Debra Dalgleish\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4ce35cfcf6755fd46a43ba02263b28eeee3c46c5caac91f7ff18f31c817bb943?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4ce35cfcf6755fd46a43ba02263b28eeee3c46c5caac91f7ff18f31c817bb943?s=96&d=mm&r=g\",\"caption\":\"Debra Dalgleish\"},\"url\":\"https:\/\/debradalgleish.com\/blog\/author\/debra\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel Errors With Access Database - Debra D's Blog","description":"If you see strange Excel errors with Access database data, try these steps to solve the problem. Data type mismatch or data could not be retrieved","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/","og_locale":"en_US","og_type":"article","og_title":"Excel Errors With Access Database - Debra D's Blog","og_description":"If you see strange Excel errors with Access database data, try these steps to solve the problem. Data type mismatch or data could not be retrieved","og_url":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/","og_site_name":"Debra D's Blog","article_published_time":"2016-09-13T20:35:00+00:00","article_modified_time":"2018-06-17T18:28:55+00:00","og_image":[{"width":356,"height":212,"url":"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png","type":"image\/png"}],"author":"Debra Dalgleish","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Debra Dalgleish","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#article","isPartOf":{"@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/"},"author":{"name":"Debra Dalgleish","@id":"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/fc3f038bcaeda478f9c5c06cfe648735"},"headline":"Excel Errors With Access Database","datePublished":"2016-09-13T20:35:00+00:00","dateModified":"2018-06-17T18:28:55+00:00","mainEntityOfPage":{"@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/"},"wordCount":719,"commentCount":0,"image":{"@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage"},"thumbnailUrl":"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png","articleSection":["Microsoft Office Tips"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/","url":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/","name":"Excel Errors With Access Database - Debra D's Blog","isPartOf":{"@id":"https:\/\/debradalgleish.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage"},"image":{"@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage"},"thumbnailUrl":"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png","datePublished":"2016-09-13T20:35:00+00:00","dateModified":"2018-06-17T18:28:55+00:00","author":{"@id":"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/fc3f038bcaeda478f9c5c06cfe648735"},"description":"If you see strange Excel errors with Access database data, try these steps to solve the problem. Data type mismatch or data could not be retrieved","breadcrumb":{"@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#primaryimage","url":"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png","contentUrl":"https:\/\/debradalgleish.com\/blog\/wp-content\/uploads\/2016\/09\/accessqueryerrors01.png","width":356,"height":212,"caption":"access query errors 1"},{"@type":"BreadcrumbList","@id":"https:\/\/debradalgleish.com\/blog\/2016\/09\/13\/excel-errors-access-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/debradalgleish.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Excel Errors With Access Database"}]},{"@type":"WebSite","@id":"https:\/\/debradalgleish.com\/blog\/#website","url":"https:\/\/debradalgleish.com\/blog\/","name":"Debra D's Blog","description":"Behind the Scenes in My Online Business","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/debradalgleish.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/fc3f038bcaeda478f9c5c06cfe648735","name":"Debra Dalgleish","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/debradalgleish.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4ce35cfcf6755fd46a43ba02263b28eeee3c46c5caac91f7ff18f31c817bb943?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ce35cfcf6755fd46a43ba02263b28eeee3c46c5caac91f7ff18f31c817bb943?s=96&d=mm&r=g","caption":"Debra Dalgleish"},"url":"https:\/\/debradalgleish.com\/blog\/author\/debra\/"}]}},"_links":{"self":[{"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/posts\/666","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/comments?post=666"}],"version-history":[{"count":8,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/posts\/666\/revisions"}],"predecessor-version":[{"id":1011,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/posts\/666\/revisions\/1011"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/media\/1009"}],"wp:attachment":[{"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/media?parent=666"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/categories?post=666"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/debradalgleish.com\/blog\/wp-json\/wp\/v2\/tags?post=666"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}