3

When importing data into an MS Access Database, sometimes there are errors in the formatting of the data source.

As I recall, when this happens, MS Access will put the offending row into a separate table called <something>.

What is this table's proper name?

BIBD
  • 15,107
  • 25
  • 85
  • 137

1 Answers1

4

Import Errors is the name of the table, followed by an integer if you have had previous failed imports. (Access 2003: http://office.microsoft.com/en-us/access-help/import-or-link-data-and-objects-HP005187610.aspx?CTT=1)

Access will create a table called Paste Errors for pasted data.

I notice that in Access 2010, the file is called <name of import file>_ImportErrors, for example, importing try.txt would give try_ImportErrors:

Error                               Field   Row
Null value in an auto-number field     id     2
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • You're obviously testing these. Are these tables, by chance, given their own type number in MSysObjects? Or are they indistinguishable from user-created tables? – David-W-Fenton Nov 30 '10 at 00:46
  • 1
    Indistinguishable, type=1. However : SELECT m.Type, m.Name, m.DateCreate FROM msysobjects m WHERE m.Name ALike "%Import%Errors%" – Fionnuala Nov 30 '10 at 00:54
  • Er, you're running that SQL in SQL 92 mode? I'd recommend against it, as it causes a host of problems in an Access app (not least of which being that the list of reserved worlds is different than for traditional SQL 89 mode). – David-W-Fenton Dec 01 '10 at 03:01
  • 1
    Guess what? I have a test database. Sometimes it is ANSI 92, sometimes it isn't. – Fionnuala Dec 01 '10 at 11:38
  • I would recommend against posting an Access answer with SQL 92 wildcards only, as most Access users are not going to be able to get it to work. Indeed, I don't think SQL 92 mode is worth using at all, in fact, because of the many, many problems it causes (different reserved words, Autocomplete stops working in combo boxes with hidden bound column, etc.). – David-W-Fenton Dec 02 '10 at 04:04
  • 1
    It is not an answer, it is a comment in reply to your request for further information; I do not think you are an average user. Furthermore, it references an unsupported system table. – Fionnuala Dec 02 '10 at 11:31
  • Actually, it *is* a supported system table -- MS promised along time ago that anything that is used in any past version of Jet will be supported going forward, and those are known "used" parts of MSysObjects. I still don't get why you think it useful to post a SQL 92 query, whether in a comment or in an answer. If comments are supposed to be ignored, or held to different standards of usability than question, it's news to me. And I wouldn't be tripped up by it, because as you say, I'm not an average user, but I assume you're not just commenting for my personal benefit alone. – David-W-Fenton Dec 03 '10 at 21:32
  • 1
    It is not an SQL 92 query, it uses the keyword ALIKE (http://stackoverflow.com/questions/1108171/how-to-use-like-condition-in-sql-with-numeric-field) – Fionnuala Dec 03 '10 at 21:46
  • This import error table name depends on the current language settings. For example, in Italian it is called *_ErroriImportazione. So how can a developer check if this table exists indepentently from the current language settings? – Simon Dec 11 '20 at 20:01