update/append from table "b"to Table "a"

  • Thread starter Thread starter Roby Geiger
  • Start date Start date
R

Roby Geiger

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
 
Roby Geiger said:
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.
Click to expand...

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
 
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

Roby Geiger said:
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.
Click to expand...

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
Click to expand...
 
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
 
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
 
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
 
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
 
TC said:
That won't do this part: "... if it does not exist then add it".
Click to expand...

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
 
Back
Top