Insert into validation rule violation

[Bob Alston]

I have a routine to copy data to new versions of my app via insert into
sql statements. Unfortunately, due to evolution of my app, sometimes
the new version has more restrictive editing than an older version that
I am updating. Thus I get this message. It tells me only how many
records have errors, not which errors or which records.

Anyone have a nice solution to identifying the specific records involved?

Maybe even the specific field?

One way I have come up with so far is to do a duplicate insert into a
version of the table with no validation rules. Then do a query joining
the two, UNRESTRICTED ----> RESTRICTED and see what records are
missing.

Another would be to write a query with the equivalent selection as the
reverse of the validation criteria, such that any record that did not
meet the validation is selected. The down side here is the query would
have to be maintained.

I am hopeful there is a more elegant way?

Bob

[Larry Linson]

Re: Insert into validation rule violation

I don't quite understand about updating multiple versions... I'd say the
solution may be to organize and clean up so that you don't have quite so
complex an environment. Other than that, if the same updates are applied to
the data for all the versions, apply them to the most restrictive first.

What is it that you do if it fails the validation rules? Do you change the
data, change the validation rules, or ???

Larry Linson
Microsoft Access MVP


"Bob Alston" <tulsaalstonsNO SPAM@cox.net> wrote in message
news:C0uCf.1405 1$JT.12037@fed1 read06...[color=blue]
>I have a routine to copy data to new versions of my app via insert into sql
>statements. Unfortunately, due to evolution of my app, sometimes the new
>version has more restrictive editing than an older version that I am
>updating. Thus I get this message. It tells me only how many records have
>errors, not which errors or which records.
>
> Anyone have a nice solution to identifying the specific records involved?
>
> Maybe even the specific field?
>
> One way I have come up with so far is to do a duplicate insert into a
> version of the table with no validation rules. Then do a query joining
> the two, UNRESTRICTED ----> RESTRICTED and see what records are
> missing.
>
> Another would be to write a query with the equivalent selection as the
> reverse of the validation criteria, such that any record that did not meet
> the validation is selected. The down side here is the query would have to
> be maintained.
>
> I am hopeful there is a more elegant way?
>
> Bob[/color]

[Bob Alston]

Re: Insert into validation rule violation

Larry Linson wrote:[color=blue]
> I don't quite understand about updating multiple versions... I'd say the
> solution may be to organize and clean up so that you don't have quite so
> complex an environment. Other than that, if the same updates are applied to
> the data for all the versions, apply them to the most restrictive first.
>
> What is it that you do if it fails the validation rules? Do you change the
> data, change the validation rules, or ???
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Bob Alston" <tulsaalstonsNO SPAM@cox.net> wrote in message
> news:C0uCf.1405 1$JT.12037@fed1 read06...
>[color=green]
>>I have a routine to copy data to new versions of my app via insert into sql
>>statements. Unfortunately, due to evolution of my app, sometimes the new
>>version has more restrictive editing than an older version that I am
>>updating. Thus I get this message. It tells me only how many records have
>>errors, not which errors or which records.
>>
>>Anyone have a nice solution to identifying the specific records involved?
>>
>>Maybe even the specific field?
>>
>>One way I have come up with so far is to do a duplicate insert into a
>>version of the table with no validation rules. Then do a query joining
>>the two, UNRESTRICTED ----> RESTRICTED and see what records are
>>missing.
>>
>>Another would be to write a query with the equivalent selection as the
>>reverse of the validation criteria, such that any record that did not meet
>>the validation is selected. The down side here is the query would have to
>>be maintained.
>>
>>I am hopeful there is a more elegant way?
>>
>>Bob[/color]
>
>
>[/color]
The issue is that I have software at nonprofit clients. It has been
installed for just over a year now. Some of the earliest versions were
missing some appropriate validation rules since added.

I had previously built a process of comparing the table structures and
issuing VBA table edit commands. However, when addressing new tables,
new fields, deleted fields, etc. that got complex. Once I thought I had
it all working, tried it at a client and had problems. so before
returning to what I found to be complex logic I took a new approach.

Yes, I saw the response of COMPARE'EM, a free utility to do this, but
this was after I was well along on my new process.

http://home.gci.net/~mike-noel/CompareEM.htm

I am using a new process to upgrade individual client versions. I take
the complete new version, delete all records from the demo data and then
INSERT INTO the new version tables from the clients existing database.
MY logic includes deleting table content in the correct sequence and
loading table content in the correct sequence.

A nice advantage of this is that I KNOW that each client now has exactly
the same software except for data. Relations, indices, etc are all
exactly the way I want it.

The issue as I see it is I get a generic error message that does not
even tell me which table is the problem. I currently can determine
which table by responding to the generic error message's option NO and
selecting cancel and debug. I can then print the table name out in the
immediate window. Then I resume the VBA and this time select YES and it
continues on - minus the problem records. That way in one pass I can
tell which tables have issues and how many issues per table. But it
does nothing to tell me which rows/records or which fields.

After I built this (generating SQL INSERT INTO statements) I realized I
should have handled the read/write via vba code; much slower but at
least I could trap the error and tell which record was causing the error.

So I am looking for a better way to resolve these issues.

Bob

[Larry Linson]

Re: Insert into validation rule violation

Once you have appended the data to the more restrictive table, create a
query joining the unrestricted table with the restrictive one, bringing down
all the fields from the unrestricted and only the unique id from the
restricted, using an outer join on the unique ids and a criteria of Null on
the id from the restricted table.

You should then be able to convert that to a Make Table query, make a table
just of the rejected records, edit them, and then append those values.
Repeat until the comparison query does not return values.

That's about as simple as I see.

Larry Linson
Microsoft Access MVP

"Bob Alston" <tulsaalstonsNO SPAM@cox.net> wrote in message
news:wnwCf.1406 5$JT.2978@fed1r ead06...[color=blue]
> Larry Linson wrote:[color=green]
>> I don't quite understand about updating multiple versions... I'd say the
>> solution may be to organize and clean up so that you don't have quite so
>> complex an environment. Other than that, if the same updates are applied
>> to the data for all the versions, apply them to the most restrictive
>> first.
>>
>> What is it that you do if it fails the validation rules? Do you change
>> the data, change the validation rules, or ???
>>
>> Larry Linson
>> Microsoft Access MVP
>>
>>
>> "Bob Alston" <tulsaalstonsNO SPAM@cox.net> wrote in message
>> news:C0uCf.1405 1$JT.12037@fed1 read06...
>>[color=darkred]
>>>I have a routine to copy data to new versions of my app via insert into
>>>sql statements. Unfortunately, due to evolution of my app, sometimes the
>>>new version has more restrictive editing than an older version that I am
>>>updating. Thus I get this message. It tells me only how many records
>>>have errors, not which errors or which records.
>>>
>>>Anyone have a nice solution to identifying the specific records involved?
>>>
>>>Maybe even the specific field?
>>>
>>>One way I have come up with so far is to do a duplicate insert into a
>>>version of the table with no validation rules. Then do a query joining
>>>the two, UNRESTRICTED ----> RESTRICTED and see what records are
>>>missing.
>>>
>>>Another would be to write a query with the equivalent selection as the
>>>reverse of the validation criteria, such that any record that did not
>>>meet the validation is selected. The down side here is the query would
>>>have to be maintained.
>>>
>>>I am hopeful there is a more elegant way?
>>>
>>>Bob[/color]
>>
>>
>>[/color]
> The issue is that I have software at nonprofit clients. It has been
> installed for just over a year now. Some of the earliest versions were
> missing some appropriate validation rules since added.
>
> I had previously built a process of comparing the table structures and
> issuing VBA table edit commands. However, when addressing new tables, new
> fields, deleted fields, etc. that got complex. Once I thought I had it
> all working, tried it at a client and had problems. so before returning
> to what I found to be complex logic I took a new approach.
>
> Yes, I saw the response of COMPARE'EM, a free utility to do this, but this
> was after I was well along on my new process.
> http://home.gci.net/~mike-noel/CompareEM.htm
>
>
> I am using a new process to upgrade individual client versions. I take
> the complete new version, delete all records from the demo data and then
> INSERT INTO the new version tables from the clients existing database. MY
> logic includes deleting table content in the correct sequence and loading
> table content in the correct sequence.
>
> A nice advantage of this is that I KNOW that each client now has exactly
> the same software except for data. Relations, indices, etc are all
> exactly the way I want it.
>
> The issue as I see it is I get a generic error message that does not even
> tell me which table is the problem. I currently can determine which table
> by responding to the generic error message's option NO and selecting
> cancel and debug. I can then print the table name out in the immediate
> window. Then I resume the VBA and this time select YES and it continues
> on - minus the problem records. That way in one pass I can tell which
> tables have issues and how many issues per table. But it does nothing to
> tell me which rows/records or which fields.
>
> After I built this (generating SQL INSERT INTO statements) I realized I
> should have handled the read/write via vba code; much slower but at least
> I could trap the error and tell which record was causing the error.
>
> So I am looking for a better way to resolve these issues.
>
> Bob[/color]