 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2005update/append from table "b"to Table "a"
Hello, I am a newbie to VBA. I am trying to find some code samples that would update the records in table"a" from the records in table"b" or the title of a reference book that would help me solve this problem. I need to read records from table "b" and then check table "a",if record in table "a" exists update it from table "b" if it does not exist then add it. I cannot use replication for this. I need to do it from a VBA code module. thanks in advance for any advice. robert > Hello, > I am a newbie to VBA. [quoted text clipped - 9 lines] > I cannot use replication for this. I need to do it from a VBA code > module. This will do it: DoCmd.RunSQL "UPDATE [Update Table] LEFT JOIN [Transaction] ON [Update Table].MemberNumber = Transaction.MemberNumber SET [Transaction].MemberID = [Update Table].MemberID, [Transaction].MemberNumber = [Update Table].MemberNumber, [Transaction].TransAmt = [Update Table].TransAmt, [Transaction].[Transaction Type] = [Update Table].[Transaction Type];" Where Update Table is your Table b and Transaction is your Table a. Of course my field names are different than yours also so you'll have to change them. Tom Lake That won't do this part: "... if it does not exist then add it". TC > That won't do this part: "... if it does not exist then add it". It certainly will. Access SQL doesn't act the same as SQL Server or other SQLs you may be used to. Try it and see. It really does work. Tom Lake Geez I seem to be making a lot of mistakes these days! Thanks for the correction, I will try it myself in du course. TC Tom, thanks for quick reply. I forgot to say that to find unique records in each table I haveto compare to fileds "jobnumber" and "dwgname" because every job has many drawings and these tables have no primary key. how would this be accomplished. thanks again robert >> Hello, >> I am a newbie to VBA. [quoted text clipped - 23 lines] > >Tom Lake Why do the tables not have primary keys? TC TC I do not know that answer. I did not create them and cannot change them yet. I hope to gain the confidence to prove I can improve this database. thanks robert >Why do the tables not have primary keys? > >TC You need to tell us what is the "primary key" of each table. If you don't know what a "primary key" is, your tables are probably not designed correctly. The magic word is "normalization". I'm not a huge fan the following article, but I can't find the one I used to quote, and at least it's a start: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/o dc_FMSNormalization.asp HTH, TC TC, I do know what a primary key is and these tables are probably not designed correctly but I cannot change that at this time. For the same reasons I cannot use replication to handle this challenge. thanks robert >http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/o dc_FMSNormalization.asp > >HTH, But if they don't have primary keys, then in theory, there is no way to uniquely identify a record in either of them. So you won't be able to write a reliable update procedure. OTOH, if you /can/ uniquely identify each record in each table, I can't see why you wouldn't define the primary keys. Robert, I've done tons & tons & tons & tons & tons of SQL over the years, and in my experience, it all goes "pear shaped" very quickly, if you do not have proper primary keys :-) Cheers, TC TC, what I had in mind and do not know how to do yet is as follows: open table "B" open table "A" read each record in table "B" check to see if there is an matching record in table "A" (based on the "jobnumber" and the "dwgname" field matching) if it exists update it from Table "b" else add it to table "A" end if after all Table"B" records are processed close all tables. thanks robert >You need to tell us what is the "primary key" of each table. > [quoted text clipped - 6 lines] >HTH, >TC Ok, off the top of my head: (UNTESTED) dim db as database, rsA as recordset, rsB as recordset set db = currentdb() set rsA = db.openrecordset ("a") set rsB = db.openrecordset ("b") while not rsB.eof ' got next record from B - look for mathing record in A. rsA.findfirst "jobnumber=" & rsA![jobnumber] & " AND dwgname=""" & rsA![dwgname] & """" if rsA.nomatch then ' none - add it. rsA.addnew rsA![jobnumber] = rsB![jubnumber] rsA![dwgname] = rsB![dwgname] rsa.update else ' found - update it. rsA.exit (can't suggest any code for this part, because you haven't said what fields you want to update, and how) rsA.update endif rsA.movenext wend set rsA = nothing set rsB = nothing set db = nothing or somesuch! But IMO, there is no appartent reason why you cn not define the primary keys, and you should do that, unless you can clearly explain why you can not possibly do that! HTH, TC Oops: rsA.exit <== rsA.edit rsA.movenext <== rsB.movenext TC thanks to both of you, I will try it from here and see what happens. robert >Hello, >I am a newbie to VBA. [quoted text clipped - 13 lines] > >robert |
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.