Results 1 to 6 of 6
  1. #1
    kingscrest is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4

    Type Mismatch Error when Importing from Excel into Access 2013 Table

    I have several Access 2013 databases into which I import Excel data to process (and subsequently export) - all manually (no macros).


    One day (yesterday) - this process stopped working.


    I select the Excel icon from the "Import and Link" ribbon. The "Get External Data - Excel Spreadsheet" window opens. I choose the file I want by selecting "Browse". I can select either import, append or link - regardless I get the same error when I click "OK".


    The error consists of a "Type Mismatch" Error pop-up window. When I click OK in that, I get an incompletely formed window with "mx_FrmMain" in the title bar, a white field across the top, a blank field in the middle with vertical and horizontal slide bars, a gray field at the bottom and 4 buttons "Cancel" "<Back" (which is greyed out), "Next>" and "Finish".


    Clicking "Next" results in "The expression you entered refers to an object that is closed or doesn't exist" (which makes sense since no data was imported, no table was created).


    It doesn't matter whether I attempt to import files that used to work in the past or a simple and small data set (3 rows, "a", "b", "c" as the column headers and 1, 2, 3, 4, 5, 6 spread across 3 columns and 2 rows). I get the same error. The error doesn't happen if I import into a blank database. But, once my database has content in it (whether just a table or tables and queries), the error occurs all the time.


    I have uninstalled and reinstalled all of Office 2013. Immediately upon re-install - it seemed to work (that is, the "type mismatch" error message didn't occur, but something wasn't right with the data. (It appeared that "0" (zero) values were being imported incorrectly - I ran out of time to debug. When I came in this morning, when I started Office, I got a "configuration in process" message (that I didn't get yesterday). After that, I was back to where I started yesterday - no import - Type Mismatch Error.


    As this used to work "last week", I know that all the data types and formats are consistent between what is in Excel and what is in Access. This used to work - until this week. I've changed nothing in either the formatting or content of the source Excel file or the Access database.


    I don't think it is a data type/format problem. Something about how Access is processing incoming data when there is already content in the database is new.




    I am using Windows 7 Enterprise - Version 6.1 (build 7601: Service Pack 1)
    Microsoft Office Professional Plus 2013 - Version 15.0.4859.1000 32-bit
    Attached Thumbnails Attached Thumbnails MxFrm.jpg  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Sometimes, Access has to guess what is in an excel field. Due to the data in it.
    sometimes it guesses wrong and you get data type errors.
    to fix, I run a macro in excel to 'fix' the data by converting everything in a string column to a string. The column may have values that look like numbers ,but aren't. I run a macro to put a single quote in front of all values, to force it to string.
    thEn the import KNOWS its a string.

    another macro validates number only columns.

  3. #3
    kingscrest is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4
    It used to work. One day it stopped, even when using the old data that used to work. I don't think it has anything to do with content. Why would "old content" work last week but not this week with no changes in the content?

  4. #4
    kingscrest is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4

    Not the desired solution

    I tried the same scenarios and data sets on a different computer. It worked perfectly.

    Unfortunately, my choices are re-image my laptop or move everything over to a new laptop. I'm in the process of doing that.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access 'guesses' datatype by inspecting the first few rows (16 I think). If it finds nulls in a date or number column for example it will assume text. Similarly if the first few rows of a currency column are round numbers it will assume integer or long and not double or currency. Note that formatting is ignored by access so formatting 20 as 20.00 will not make access think it is a double. Generally better in excel to format all columns as General to see exactly what you have.

  6. #6
    kingscrest is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    4
    Access may guess the datatype - but that isn't my problem. My problem is Access is giving a different response to the exact same data, separated by a week in time. Last week, the data set imported perfectly. This week it won't. It is the exact same data set, exact same data, exact same database. The only difference is it is now a week later with whatever fixes, hotfixes, patches, security updates that have been foisted upon me by corporate IT. I tried this same exercise on a different computer this week - and it worked perfectly. Went back to my computer - still doesn't work. Time for a new image / new PC.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-18-2014, 12:26 PM
  2. Replies: 9
    Last Post: 11-20-2013, 03:16 PM
  3. Replies: 1
    Last Post: 05-02-2013, 01:29 AM
  4. Type mismatch Error after upgrading to Access 2010
    By twm07073 in forum Programming
    Replies: 7
    Last Post: 06-13-2012, 10:07 AM
  5. Type error when importing Excel spreadsheet
    By jlmnjem in forum Database Design
    Replies: 2
    Last Post: 08-27-2006, 07:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums