how to update access tables?

[BlueByte]

hi!
i have 2 tables in access.
need to update table A depending on the
values in table B.

any ado functionality that helps me do this?

 

[codeee]

Hi,

You can do a direct update in access while joining the 2 table by the relevant criteria,
or
you can use a recordset within a recordset in VB...

 

[CCLINT]

sql = "UPDATE Table2 " _
& "INNER JOIN Table1 " _
& "ON Table2.KeyField = Table1.KeyField " _
& "SET Table1.SomeFiled = Table2.SomeField " _
& "WHERE SomeCriteriaIfNeeded"

 

[BlueByte]

hi codee!
am tryin a direct update...

table A:
compCode ID1 ID2 Status add1 add2

table B:
compCode ID1 ID2 Status

-----
am using a sql kind of query

update tableA
set status = (
SELECT A.STATUS
FROM NSDL_STATUS AS A, NSD AS B
WHERE
A.COMP_CODE=B.COMP_CODE AND
A.DP_ID=B.DP_ID AND
A.BP_ID=B.BP_ID)
where
A.COMP_CODE=B.COMP_CODE AND
A.DP_ID=B.DP_ID AND
A.BP_ID=B.BP_ID;

this is not working
access syntax is different mebbe..

kindly reply asap...

thnx

 

[BlueByte]

thanxamillion cclint!!

i used

UPDATE A
INNER JOIN B
ON (A.BP_ID = B.BP_ID) AND (A.DP_ID = B.DP_ID)
SET A.STATUS = B.STATUS
WHERE (AD.BP_ID = B.BP_ID) AND (A.DP_ID = B.DP_ID);

voila!it works..

 

[CCLINT]

You're welcome.
But you do not need this:

WHERE (AD.BP_ID = B.BP_ID) AND (A.DP_ID = B.DP_ID)

because the JOIN ON takes care of it.

 

[BlueByte]

hi codeee!
wud u pls let me know how to get the
same result using a recordset within a
recordset in VB??

 

[Daisy450]

.execute "Query"

 

[codeee]

Hi BlueByte,

I see you have the direct update working, great.

To use a recordset within a recordset in VB:
1. Open Master Table Recordset
RS1.OPEN "SELECT * FROM TABLE A"

2. Loop through the master recordset and for each record either execute an update statement
(i.e CN.EXECUTE "UPDATE TABLE B SET STATUS = " & RS1!STATUS & " WHERE BP_ID = " & RS1!BP_ID & " AND DP_ID = RS1!DP_ID&quot
or open a new recordset
(i.e. RS2.OPEN "SELECT * FROM TABLE B WHERE BP_ID = " & RS1!BP_ID & " AND DP_ID = RS1!DP_ID&quot
Loop through RS2 and set RS2!STATUS = RS1!STATUS...

3. Remember to close recordsets to free memory.

Hope this helps...

 

[BlueByte]

thanx codeee..