Re: duplicates query help & strategy for update queries with SetWarnings = False

As the English would say "Sod that!!" After playing around with duplicates, and a funtion to generate an occurence number, it looked like you would need a handful of queries per table!!

So in 15 min's or so, I came up with a function that will take care of dup's and nulls in one pass. I'll post the code in a new post for those searching for such things, as it works quite nicely.

Andy
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message news:46f3612c$0$28173$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
The best way do do imports might be to sort out the problems before the data appended to the real table. You create a table with all *Text* fields (so you get no data mismatch errors), no validation rules, no relationships to other tables, and an AutoNumber primary key field last in the table (so the data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes any data in the temp table (from previous imports), and performs the TransferText into the temp table. Your code then runs a series of tests for everything that could go wrong: zero-length text fields, wrong data type, bad dates, values that don't match anything in foreign key fields, and so on. You flag these records and load them into a list box or the form itself (typically in Continuous view), so the user can fix up these situations.

Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table(s). If any error occurs during this step (typically something you forgot to check for), you can trap this error by running using the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final table, so you probably want to wrap that operation in a transaction so you can roll the whole thing back. For an example of using a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <acolonna35@xxxxxxxxxxx> wrote in message
news:8cIIi.51180$Um6.6515@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello all,

So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to include the occurence #. For example, in a pay types table, if "Discover" is duplicated (and you can't get rid of one due to it potentially being in use), then I'd like to run an update query that would update the 2nd one to: Discover (2). (Yes, I know you should just set the pay type description to NOT allow duplicates, but the original db did allow it, and the import db will not allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import utility: When running a series of update queries to update data from one database to another, I use a docmd.setwarnings false statement before running each update or append query.

The problem is, if a query fails due to data validation rules, other misc. table rules, etc., the "setwarnings = false" command is suppressing the one warning that you actually do want to see, and it's blowing right by that with no messages. If you don't put the setwarnings=false, then the user get's 2 dialogs for every update query, which is not at all desirable since we're talking 50 or so append queries. Does anyone have an alternative? Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that the user can never be warned about the 2 standard messages: Running a query that will change data, and Confirming the appending of records. But...I have to know if a query fails to append any data due to key viloations, or any other reasons.

Any strategy here would be helpful. I don't even think you can trap an error code, because if any data fails to append, it doesn't trigger the On Error events. Any ideas?



Thanks!

Andy


.