How to Avoid Errors When Importing to Access Database

Brad Arnold

I had a spreadsheet that was in .xlsx format that I needed to import into an Access Database. I made sure the columns were in the correct order, and that the column headers matched my field names exactly as in Access.

I then ran the Import Wizard (which by the way you have to run Access “As Administrator” after a recent Office update or else the Import Wizard will just close on you without any error or without actually importing) and pointed the wizard to my spreadsheet and then told it to append the data to an existing table. After going through the steps and hitting finish I got the following

Looking at the ImportErrors table that was created it was complaining about Type Conversion Failure on the BoxNum field.

Now, what’s odd about that is that field in my Access Database is Short Text (255)

After looking at my spreadsheet I noticed that rows 1 – 27 have the value of 1 (box number 1), however, row 28 (the first error row) has a value of 1A.

What’s happening is, Access is scanning the rows in the spreadsheet and trying to ‘guess’ what the data type are. The default number of rows to scan is 8, which to me seems like an odd number and way too few of rows. The value is stored in the Registry and can be easily changed.

To change the registry key:

  1. In Windows environments, select Start ► Run and type REGEDIT to display the Registry Editor.
  2. In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Microsoft ► Office ► 15.0* ► Access Connectivity Engine ► Engines.
  3. Double-click the Excel node.
  4. In the right panel, double-click the TypeGuessRows entry.
  5. Change the value data from 8 to 0.
  6. Click OK.
  7. Select File ► Exit to exit the Registry Editor window.

To change the registry key for the 32-bit version of Access that is running on a 64-bit operating system:

  1. In Windows environments, select Start ► Run and type REGEDIT to display the Registry Editor.
  2. In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 15.0* ► Access Connectivity Engine ► Engines.
  3. Double-click the Excel node.
  4. In the right panel, double-click the TypeGuessRows entry.
  5. Change the value data from 8 to 0.
  6. Click OK.
  7. Select File ► Exit to exit the Registry Editor window.

*I’m using Office 2013 which equates to 15.0 You can look here for a list of Office Version numbers.

By setting the registry key to 0 it will now scan the entire spreadsheet. This of course could take a while if you frequently import huge spreadsheets into Access Databases. Mine are typically a couple hundred rows max so I’m not worried about it. You can obviously set this value to whatever number of rows makes sense for you.

Now when you import and you run through the wizard the preview window will still only show you first 25 rows of data, however, Access has scanned the entire spreadsheet and now sees that my BoxNum column has values that are 1A as well as 1. After finishing the wizard the data was all imported successfully without errors!