Turning off "Microsoft Access can't update . . . " message?

[Peterout]

Hi,

I run many small update queries when working on various customer files. Numerous times I get the following message asking for continuance of the query. In all cases I select Yes.

-----------------------------------------------------------------------------------
Microsoft Access can't update all the records in the update query.

Microsoft Access didn't update *0 field(s)due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 2 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help."
-----------------------------------------------------------------------------------
* This is just an example of the messages I get. Depending on the file (table) I'm working with, the number of fields or records will vary.


Is there any way that I can prevent these notifications from appearing and just simple have the queries run to their completion? I have already deselected all the confirmations under Options > Edit/Find.

Thanks

Peter

 

[lespaul]

so you don't want to know that some of your records didn't pass the validation set up on the table and failed to update? That's what this particular message is telling you:

Microsoft Access can't update all the records in the update query.

Microsoft Access didn't update *0 field(s)due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 2 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?
To ignore the error(s) and run the query, click Yes.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Peterout]

That's correct Leslie. I don't want to know that some records failed to update.

The files I work with are address database files submitted by numerous clients. The file sizes vary, fields vary, and content varies. Many clients provide records with fields that are blank or contain garbage information. I don't expect that all records supplied by clients will follow Access validation rules. However, I still need to run certain queries before I can continue into other stages of data cleansing . . .

Thanks

 

[lespaul]

are you running this query from a macro or VBA? If so you can set warnings off....

 

[Peterout]

Hi Leslie,

Sometimes I just run the query by itself, other times it will be with a macro. The warning comes up using either.

As for the warning . . . I confess that I'm ignorant as to why they come up. Often in the past I would check the results of the query and everything looks as expected, yet I still get the warnings on some files.

For example, right now I am working with a client's file. I have imported their excel file of 21,500 records and created a table with all their address fields. In this case I have their fields: CnAdrPrf_Addrline1, CnAdrPrf_Addrline2, CnAdrPrf_Addrline3 (all text fields). For the purpose of keeping their original information intact, I create 2 additional fields called Add1, Add2. With an update query I update Add1 and Add2 with the client's original address information. In the case of Add2, I combine the original CnAdrPrf_Addrline2 and CnAdrPrf_Addrline3:

UPDATE 1 SET [1].ADD2 = Trim([1]![CnAdrPrf_Addrline2] & " " & [1]![CnAdrPrf_Addrline3]);

Running this query gives me the warning about validation rules not being met. It indicates that 20,355 records will not be updated. Which is a non issue since there are 20,355 records that don't have information in CnAdrPrf_Addrline2 or CnAdrPrf_Addrline3. I select Yes and allow the query to finish. I take a look at the results in field Add2 and I have all the information from CnAdrPrf_Addrline2 and CnAdrPrf_Addrline3 (1145 records were updated).

Sorry for the lengthy response, but I hope it helps you understand what I'm working with.

Thanks!

Peter

 

[randy700]

In code..

DoCmd.SetWarnings False
[i]run your queries here[/i]
DoCmd.SetWarnings True

In a macro..
The step before the query - SetWarnings -- No
The step after the query - SetWarnings -- Yes

Although I wouldn't recommend it, I believe you can turn these warning off permanently by going to Toos=>Options=>Edit/Find and deselecting the checkbox next to Action Queries.

Randy

 

[Peterout]

Hi Randy,

Thanks for the tip, however, I run many queries on the fly. Make them up as I go depending on the condition of the client's files. I process so many files during the day that imbedding them in a code would take time.

If the code you are suggesting, is like turning off the warning on Action Queries, then it won't work. Simply because I already have the warning turned off for Action Queries.

Thanks

Peter