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" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:wnwCf.14065$JT.2978@fed1read06...[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" <tulsaalstonsNOSPAM@cox.net> wrote in message
>> news:C0uCf.14051$JT.12037@fed1read06...
>>[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]
