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]

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" <tu****************@cox.net> wrote in message
news:C0uCf.14051$JT.12037@fed1read06...

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

[Bob Alston]

Larry Linson wrote:

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" <tu****************@cox.net> wrote in message
news:C0uCf.14051$JT.12037@fed1read06...

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

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]

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" <tu****************@cox.net> wrote in message
news:wnwCf.14065$JT.2978@fed1read06...

Larry Linson wrote:

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" <tu****************@cox.net> wrote in message
news:C0uCf.14051$JT.12037@fed1read06...

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

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