problem trapping in append query

[wollihas]

Hi everybody,

I'm new to this forum and not very experienced with VBA or Access. Let me describe my problem:

I want to append records to my customer table (Customer No is PK) on a monthly basis. I am taking these customer records from a .CSV file and we speak about 300 records here. As expected most of the incoming customers exist in my customer table and are therefore rejected due to PK violation. So far no problem. Now: I would have expected clear message from Access what went in successfully and what not, not just sums.

My defined goal is to achieve a list (either a table or an external log file) where this information is clearly given, i.e. for EACH record I want to have an entry in that log file if it the record appended succesfully or not, and if not, what problem and which field caused the problem. I find the standard upcoming window how many records were 'lost' not very helpful.

I browsed through lots of topics in your (by the way EXCELLENT) forum but did not find the 100 % answer. One difficulty seems to me that Access considers key duplications not as an error, therefore a real error trapping will not work.

I hope somebody can give me a shove into the right direction as this must be a common requirement (otherwise, how do I control 10,000 records instead of 300 ?).

Thanks in advance.

Wolfgang

 

[llkhoutx]

Use DAO or ADO code to do this.

Cycle though your customer records, before appending, check to see if the PK already exists, if it does, add that customer to a tremorary delete table, similarily, if it doesn't exist, append that customer and add it to a temporary add list.

With just 300 records, this will run very quickly.

 

[wollihas]

Hi llkhoutx,

thanks for the answer. I am sure it would work.

But your solution would mean that I have to implement the validation logic that is given in the table design twice. Besides the cheek for a key duplication I also have other checks imbedded in the field description (e.g. ZIP code must be 5 digits etc). I want to avoid to code this validations a second time because in case I need to change the rules behind the fields of the table I need to change it in this new routine as well.

What I am thinking of is the following: As everybody has seen a million time, when I try to append records, Access first pops up a confirmation window and when I confirm there comes a lengthy statistic of type conversion failure, key violations, lock violations and validation rule violations, followed by the question if the user wants to run it anyway. That means the records have been checked record by record with an individual result. It should be possible to feed in one record at a time and to store the reaction (the content of the a.m. window) in a log file. Question1: How to read that window. Question 2: How to avoid that the user has to press OK all the time.

Maybe this would be stupid or not very elegant solution but I would go that way if it works. In addition it shows the nature of my problem in better way.

What astonishes me is that Access users seems to be happy with that sort of statistic like : 1000 records went in , 300 caused problems due to some errors. Damned!!! Which ones ???

OK, maybe somebody can shed some light.

Thanks

Wolfgang

 

[wollihas]

No new thoughts ?

 

[llkhoutx]

There's probably a way to run a query one row at a time, but the programming logic would be considerable. DAO, ADO. It will run drammaticaly faster than you think and you'll be albe to simultaneous build your log file.

You can't avaid answering the message, but you can aviod the message toggling setwarnings (docmd.setwarnings false/true)before and after your query.

 

[wollihas]

Hi llkhoutx,

thanks for the answer.

Obviously I have an exotic problem here.

What I do not understand is the increased programming logic due to not 300 or 500 records but only one. But we'll see. Maybe you are right.

The key question seems to be how to read the content of this window (you know what I mean: MS Access can't append all the records in the append query. ... type conversion failure, key violations, lock violations and validation rule violations bla bla bla)

I wouldn't mind this window flickering several hundreds of times on the screen.

So, do you know how to detect this window and read out it's content ? This would really give me the initial spark.

Thanks in advance for any advice.

Wolfgang

 

[llkhoutx]

Not to belabor a point, but with DAO or ADO code, you'd get an error or which you could trap for the error number, log it with appropriate information and continue without the user ever seeing anything, except maybe a message you generate at completion to advise what was done.

Access is smart, but not as smart as you evidently would like it to be.

 

[wollihas]

To be honest I don't have the faintest idea how to use DAO or ADO code for my purpose. Would I ask too much if somebody could show me an adequate example that relates to my problem ? I really would apprexiate that.

Thanks.

Wolfgang

 

[llkhoutx]

Get Litwin, Getz, et al. Access Developer's Handbook for what ever version of Access you're using. Alternatively, get Steven Roman's Access Database, Design & Programming, 3rd Edition published by O'Reilly.

Your issue is not exotic, but ordinary and not at all unusual.;