 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / March 2005VBA Append Query Key Violations
I am running an append query in Access 2000 VBA. I need to count the number of records actually appended to the file, as they are appended. I am getting key violations, which I expected to get due to duplicates. I do not want the duplicates appended, but I need to be able to subtract from my counter whenever I get the key violation error. Does anyone know how to capture the key violation error? I cannot find it in Help or any of my books. I am new to VBA... Thanks! Steve > I am running an append query in Access 2000 VBA. I need to count the > number of records actually appended to the file, as they are [quoted text clipped - 5 lines] > Thanks! > Steve If you use the DAO Execute method to run your append query, you can then check the RecordsAffected property of the base object to see how many records were actually appended. For example: Dim db As DAO.Database Dim lngAppended As Long Set db = CurrentDb With db .Execute "YourAppendQuery", dbFailOnError lngAppended = .RecordsAffected End With Set db = Nothing MsgBox lngAppended & " records were appended."  SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) Thanks, but I couldn't get this to work. I'm not using DAO. Also, I need to know after each append whether or not it was successful. > Thanks, but I couldn't get this to work. I'm not using DAO. Also, I > need to know after each append whether or not it was successful. That doesn't give me much to try to help you with. If you are not using DAO, what *are* you using? And may I suggest that perhaps you *should* be using DAO? You cannot get the information you want if you use DoCmd.RunSQL or DoCmd.OpenQuery. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) I am using DoCmd.RunSQL. > I am using DoCmd.RunSQL. As I said, you cannot get that information using RunSQL. Use DAO instead. If you tried it and had trouble getting it to work, post the code you used and the error that occurred, and I'll try to help. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) As in the earlier post, you'll probably need to go DAO since it supports transactions which are the key to determining wether or not the query successeded and if not for way. Also, DAO allows record-level processing so you should be able to identify easily which records where not appended (assuming that you're moving records from 1 table to another). It also allows you to manipulate keys if you need to, I use a PPC to collect data on the run and then import it. I ran into a problem where the keys (which were AutoNumbers) began to conflict with the keys in the master DB. DAO allowed me to omit the primary keys for the parent records and then update the foreign keys (the corresponding values 564 becomes 783) for the child records. David H > I am using DoCmd.RunSQL. I finally got the DAO code to work. The problem was that I didn't have the "MS DAO 3.6 Object Library" checked in the Referance listing. I was getting an error that said "User-defined object not defined". That was very confusing. Thank you for your help. I REALLY appreciate it!! > I finally got the DAO code to work. The problem was that I didn't > have the "MS DAO 3.6 Object Library" checked in the Referance > listing. I was getting an error that said "User-defined object not > defined". That was very confusing. Thank you for your help. I REALLY > appreciate it!! You're welcome. I'm glad to hear you got it working. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.