Get External Data in Access 2003

[Guest]

Firstly, sorry if this has been covered a hundred times before.

I'm creating a table in Access, defined fields and validation rules. I
import a text file into the table. One record is rejected due to validation
checking as intended (this is an educational exercise). BUT, Access does
not identify it. The only way I can find it is by doing a visual check,
which kind of defeats the wonder of computers.

Does anybody know how I can get Accessm2003 to record what data it rejects?

Thanks,

pg

 

[Ken Snell [MVP]]

ACCESS creates a table containing the ImportErrors that you are finding.
When you get the error message, it'll tell you there (or in a subsequent
window) the name of the table. It should be visible in the database window.

 

[Guest]

Sounds great, but that's not happening.

What I do get is:

"Microsoft Office Acces was unable to append all the data to the table.

The contents of fields in 0 record(s) were deleted, and 0 record(s) were
lost due to key viiolations.
*If data was deleted, the data you pasted or imported doesn't match the
field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key
values that already exist in the destination table, or they violate
referential integrity rules for a relationship defined between tables.
Do you want to proceed anyway?"

At this point I click yes and get the table created minus one record.

 

[Ken Snell [MVP]]

I've not seen or experienced your result; I would not expect what you're
seeing, especially being told that zero records are being lost, and then
losing one or more records.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Mmm. So - if I'm reading the message from Access correctly, it is saying
that not all data has been passed to the table (my one missing record), but
it is not offering to tell me what is being excluded. Any thoughts as to
where I can take it from here? Or have I remarkably found a new bug that
no-one knows about (which I think is unlikely)?

pg

 

[Ken Snell [MVP]]

How are you importing the data? The error message suggests that a copy/paste
operation is going on?

 

[John Nurick]

PMFJI, Ken.

In my experience Access often does not create an _ImportErrors table;
I've never bothered to work out the circumstances in which it does and
doesn't.

But AFAIK it's always possible to write queries to identify the
problematic records: in the present case, a query to identify records in
the source data that don't pass the validation rules and/or relational
constraints in the destination table.

 

[Ken Snell [MVP]]

Thanks, John. I must have been "lucky" so far, as I've never not seen an
errors table in this situation.