You are here:

Using MS Access/Importing Spreadsheets and Checking for Import Errors


QUESTION: I want to use microsoft access to import spreadsheets from different individuals.  I want fields to be specific lengths etc, they will be given a template to work from.  However I have tried a demo and if the cell of a spreadsheet is wrong it imports the spreadsheet with errors.  I want access to tell me there are errors so i can send it back to them and tell them to fix it.

For example:  Column 1 is a company code which is 5 numbers, column 2 is a birthdate mm/dd/yyyy, and column 3 is a whole number.  

I put a 6 digit company code, a bunch of random letters for the second column, and 56.64 for the third column in a spreadsheet and it still imported it.

Any ideas how I can get access to tell me there are errors??


ANSWER: Access doesn't know these are errors. What you should do is link to the spreadsheet rather than import it. Then run an Append query that adds the data from the sheet to your table. You can do validation in the query to make sure the format is correct.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I created a table and I am importing the spreadsheets in as appends to the table.  In the table I have specified if the field is required, if it has to be unique, how long it can be, and I have used input masks for dates and social security numbers.

I have 2 fields that I would like to leave as numeric.  Any ideas how I can limit the characters to 4 as I cannot specify field size on a numeric datatype??

Again, if you link to the sheet and run an Append query rather than append directly, you can control this. For example, you could use an expression like:

Expr1: Val(Left(fieldname,4))

will import only the first 4 characters but convert it to a number to store in the table.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.