MS Access - Highlight Validation Rules Misfits

[r0bb]

Hi Guys,

For a datamigration, I have an Excel sheet containing 30.000 rows of data, each row contains 20 values. This data needs to be migrated to MS Access, using Validation Rules for each cel. I have created an MS Access Db, in which I have created a Table similar to the format of the MS Excel sheet. Next to this I have created a Form which is linked to the MS Access Table. In this Form, I have assigned Validation Rules to each column.

Now here is the problem: Because of the Validation Rules, I am only able to enter data row-by-row by hand. As you can imagine this will be a **** of a job as I have 30.000 rows. I know I can bypass the form and simply dump all the data in the MS Access Table. This data will then also appear in the form, without any Validation Rules applying. I am looking for a setting which can now highlight all the cells which DO NOT FIT my Validation Rules. In this way correcting my data will be completed MUCH sooner compared to enter row-by-row data through the form.

I hope someone knows this trick, and can help me with it.

Thanks in advance, Robbin.

 

[Toasty]

Hi Robbin,

I can only think of a bypass option to load all the data in your d-base, then building a query upon your validation rules and (maybe) putting your output back in a table which is conected to your form.

Good Luck,

 

[OctaFish]

I know I can bypass the form and simply dump all the data in the MS Access Table. This data will then also appear in the form, without any Validation Rules applying.

I have never heard of a table with the necessity of validation rules for every field. So that would worry me (a lot). You have based your form on this table, which means that all your data in the table will show up in your form. And the validation (which you applied on the form, not on the table which is also a bit odd) will apply on the data in your form. Because all data in your form has the validation applied to it.

So if your table doesn't have the validation rules, you can simply create an Insert query to import the Excel data into the table. This should always work, since there is no (or little) validation in your table. But you should seriously consider to put the validation rules in your table, and not in your form. After all: the table is the most important part of your database, not your form. If the data in your table is invalid, then your database is invalid. You might not see the corrupted data in your form (because of the validation rules) but it's still there....

 

[r0bb]

I agree to the fact that validation rules in the table instead of the form would be logical. Will build this in tomorrow. As Toasty mentioned, I tried to build a query in which I only want the values outside my validation rules. Still working on it. Seems to be the closest trick so will try to work with it. Thanks for the help. KR, Robbin.