473,574 Members | 2,625 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert into validation rule violation

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
Jan 27 '06 #1
3 6322
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.1405 1$JT.12037@fed1 read06...
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

Jan 27 '06 #2
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.1405 1$JT.12037@fed1 read06...
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
Jan 27 '06 #3
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.1406 5$JT.2978@fed1r ead06...
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.1405 1$JT.12037@fed1 read06...
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

Jan 27 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5168
by: Sudip Chakraborty | last post by:
Is there a way to see constraint validation errors while loading xml into a DataSet ? I'm interested in the line number in the xml file which is causing the error. I've enclosed the relevant stack trace below. at System.Data.DataSet.FailedEnableConstraints() at System.Data.DataSet.EnableConstraints() at...
2
2682
by: Joey P | last post by:
Hi all, I am doing a project for university whereby i have to implement a simple database related to a frozen foods company. I am having some trouble though creating a validation rule for one of my fields. I have a table called "Product" and two of the fields included in this table are "Cost Price" and "Retail Price". I need to create a...
5
3179
by: Jeremy | last post by:
I have a vb .net 2003 app that uses access 2k as a backend. The main table has some validation rules (legacy stuff brought forward inadvertantly, and now we're stuck with 'em). The problem is, I do a dataadaptor.update, and an exception is thrown. I would prefer to validate before the update & tell the user to fix the data....
5
3818
by: Stephen D Cook | last post by:
I'm trying to clear a form after the user clicks the Insert button so the textboxes are empty and the checkboxes are unchecked. When I try to put the code in the Click part of the Insert button, I get an error telling me the Serial Number text box is empty. If I put the code in the MouseUp part, it doesn't do anything. please direct me to...
8
15956
by: petebeatty | last post by:
I have created a SQL string the properly inserts a record in the table. However, the insert does not occur at the end of the table. Instead it inserts a record after the last record that I viewed. This would be OK, except it assigns a autonumber to be one greater than the last viewed record. This causes a duplicate autonumber. I know I...
1
3751
by: billa856 | last post by:
Hi, I am trying to insert Null value in column(ShipDate) in my table.That column(ShipDate)'s type id date/time and format is short date. I am using "" to insert Null in that column(ShipDate) but it shows warning that customer can't append all the records in the append query. Customer set 1 field(s) to Null due to a type conersion...
7
5963
by: sharsy | last post by:
Hi guys, I would like to setup a validation rule for a database in microsoft access that restricts data entry so that a certain field can only be filled in if another field has a specific answer (that is selected via a drop down list). Example Field1 - options are "In" or "Out" Field2 - options are "Join" or "Not Joining"
1
2076
by: MLH | last post by:
Anyone remember if A97 append query failure would ever report data breaking validation rule when such was not the case. I have an old SQL statement - several years old now. I've encountered a case in which the append fails and the reported error is validation rule. The table being appended to (tblClusters) has only 1 field with a...
18
6210
by: ChipR | last post by:
I have a text box with a validation rule and validation text. When entering a new record, if I put in invalid text, the validation text is displayed in a message box, but after clicking OK, another message box is displayed with: ----------- myApplication (i bubble icon) The value violates the validation rule for the field or record. For...
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8077
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7828
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5634
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5316
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3765
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2253
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1076
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.