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