Data screwed up when imported or exported. Poor architecture. How to detect and fix this?
What I found with Access is its inability to properly import/export data types. Automated and even forced type casting, from a database table to text or CSV files (or the other way around) is poor, resulting in many errors. It is because this environment is too rigid, not flexible enough to do smart type detection and/or casting. Ever had to deal with a date that was recorded as an integer, while the date in the next row was recorded as mmddyy? Or or comma (inside a data field value), that screws up your CSV file?
I've received many data files that had a data corruption rate above 5%. Sometimes two different types of values (advertiser keyword vs, user query, or referral domain vs. ad network domain) are stored in the same field (solution: add an additional field that tells you the type of keyword or referrer). Sometimes the field are not properly aligned. Sometimes it looks like the people who produced the data were not experts in regular expressions, or used separators very badly. Sometimes it's the software itself that screws things up - that happened to me many time with Access.
Sometimes these screw-ups have undesirable side effects, such as one user in your database becoming a kind of "garbage collector", rendering data insights worthless. Sometimes it's not a big deal.
Criticizing Access for being too "rigid" when importing data seems odd to me. Any time one wants to load data into a relational database it always involves three basic steps. We've come to associate these steps with a certain class of tools created for that purpose: Extraction, Transform, and Load. Expecting to perform complex transformations using the facility of Access intended to merely load the data will always lead to disappointment. On the other hand, spending the "big dollars" for an ETL tool, just to load data into a relatively inexpensive desktop database, would seem excessive to most of us. Therefore, usually, when I'm preparing data to load into an Access database I first transform it using the "Practical Extraction and Reporting Language". Yes, I use "PERL". It is free, incredibly powerful, and once mastered falls into the category of a "quick and dirty" language. By this last statement I mean that the development of a transform program of even moderate complexity can be done in minutes. The "dirty" part comes from the unfortunate property of PERL that renders its source impenetrable to the author within a few hours of having written it. But, that's seldom a problem, because you can always re-write it. (I jest.)
Or, if PERL isn't available for some obscure reason, and one wants to limit the processing entirely to Access, I first bring the data into a "raw" table where every field is simply text. Then, I can bring the power of various SQL queries or Visual Basic for Applications (VBA) programs to bear on the raw table to transform and load the data into the final normalized relational tables against which the "reports" run.