no validation on importing (1 Viewer)

[matt2006]

hi all
i have an excel sheet in comma delimited form. the fields in access have input masks set, but when imported into access the masks are ignored - does anyone know a way around this. Also is there anyway to get access to tell me what records its rejected when it comes accross one that breaks a validation rule

Thank in advance
Matt

 

[boblarson]

Masks don't do anything with an import. If you have a CSV file, you can set up an import specification to import. If your import has failures it will be reported in an Import Errors table that gets created on import. You can then check to see which row and item it is that failed. It doesn't give you much information about why, but you can at least see what failed and try to figure it out from there.

To create an import specification follow these steps:

1. File / Get External Data / Import
2. Select Text (*.txt,*.csv,*.tab,*.asv) from the Files of type drop down
3. Click the ADVANCED button on the bottom left of the import dialog
4. Make your selections as to each data element, dates, delimiters, etc.
5. Click the SAVE AS button and name the specification. Click OK
6. Now you can just click CANCEL

To use the specification in code to automate the import use:

DoCmd.TransferText acImportDelim,"YourImportSpecificationNameHere","YourTableNameToImportToHere","YourFilePathToTheTextFileHere",True (if has header)

If you have a Fixed Delimited file change the acImportDelim to acImportFixed

 

[matt2006]

How do i view the error table? i cant find anyway of doing it

Thanks, ive sorted the validation now
Matt

 

[boblarson]

The error table should show up in your list of tables in the database window if there was an error table generated.

 

[gemma-the-husky]

matt, see your other thread re error tables - it depends what sort of errors, I think, as to whether you get an error table