Database Table1 Update Table 2 Problems!

[Cheyenne]

Hello,

How do you update one table based upon another?

I need to get five fields out of database table one and save those values
into the same field names into table2. I'm using DAO and Access 2000.

The ProductID number is the same in both tables and so is a CID.

example

'Define what to get out of the database prior to displaying
Cost.Cs Val(Form1.Text1.Text)

How do I do that?

Thanks for all the help in advance.

[beasty1711]

you could use SQL

sSql = "Select Field1, Field2, Field3, Field4, Field5 form Table1"


then build another SQL statment to re-insert

"Insert Into Table2 Values in(myArray1,myArray2,myArray3,myArray4,myArray5)"


myArray are the values from the first ssql statment that have been passed into an Array.


I am sure there is probabally a better clean'er way of doing it, but this should work

SjR would probabally know a better way

[tpiano]

this one will insert all records in table2 to table1

db.execute "INSERT INTO table1 (field1, field2, field3, field4, field5) select field1, field2, field3, field4, field5 from table2"

this one will insert a specific record from table2 to table1 based on product id inputted

db.execute "INSERT INTO table1 (field1, field2, field3, field4, field5) select field1, field2, field3, field4, field5 from table2 where
product_id = '" & text1.text & "'"

[Cheyenne]

Just an thought about the above code:


Do the fields have to be in order or position 0,1,2,3...etc. in either tables? In table2
they are in order ( consective . . . Field1 ... Field 5), but not in table 1.

So the fields come from table 1 ( where they are not in order) and go to table 2 ( where are ordered/next to each other).

Do I have to open up the database in order to this?

Also will this work if the ProductID has more than 1 row in the database tables?
For example say ProductID = 1 and there are two 1 's ( two rows in table)
in the table 1.

[tpiano]

your table doesnt need to be in order. it will do a one to one match base on how you order it on your insert select statement.

what the where clause do is insert all those records from table2 to table1 for all same product_id.

if you use product_id as your primary key in table1 without duplicates, then you'll encounter a primary key constraints.. do the necessary trapping.

[Cheyenne]

I can't seem to solve this problem. I'm getting the error # 3061, Too few
parameters, expected 2.

So what is wrong? How do I fix this ?

Below is what I'm trying to do.


Thanks again

Set wrk = DAO.Workspaces(0)
Set db = mywrk.OpenDatabase(App.Path & "\cstmer.mdb")
 
db.Execute "INSERT INTO P_D(DT,E,K,Mde,C) select D,E,K,Mde,C from Cstrs where ProductID = '" & Val(Form1.Text1.Text) & "'"

[tpiano]

dim db as database
dim sql_statement as string

Set db = OpenDatabase(App.Path & "\cstmer.mdb")

sql_statement = "INSERT INTO P_D (DT,E,K,Mde,C) select D,E,K,Mde,C from Cstrs where ProductID = Val('" & Form1.Text1.Text) & ')"

db.Execute sql_statement

[Cheyenne]

I still get the same problem.

The sqlstatement had to be rewritten since it wouldn't compile.

Im still stuck on this one

Dim sql_statement As String
 
Set wrk = DAO.Workspaces(0)
Set db = OpenDatabase(App.Path & "\cstomrrr.mdb")
 
 
sql_statement = "INSERT INTO PD(D, E, K, Mde, Cst) select D, E, K, Mde, Cst from Cstrs where ProductID = '" & Val(Form1.Text1.Text) & "'"
 
db.Execute sql_statement     < - _ - - - -  - -error hERE!

[tpiano]

debug the program, put a breakpoint on the execute.. then run.. once it stops do a ? sql_statement on your immidiete window copy the result of your ? then open your access database.. then go to query tabs paste the query then run the query to check if your query is correct in syntax.

if the product_id is numeric in form your sql_statement when ? should look like this

INSERT INTO PD(D, E, K, Mde, Cst) select D, E, K, Mde, Cst from Cstrs where ProductID = Val('1')


your sql_statement should look like this if product_id data type is numeric :

sql_statement = "INSERT INTO PD(D, E, K, Mde, Cst) select D, E, K, Mde, Cst from Cstrs where ProductID = Val('" & Form1.Text1.Text & "')"

if datatype is not numeric it should be like this

sql_statement = "INSERT INTO PD(D, E, K, Mde, Cst) select D, E, K, Mde, Cst from Cstrs where ProductID = '" & Form1.Text1.Text & "'"

[Cheyenne]

STUMPED! confused: confused: confused:


I put the sql_statement in the watch window and I get the below:

INSERT INTO PD(D, E, K, Mde, Cst) select D, E, K, Mde, Cst from Cstrs where ProductID = Val('1')

I put the ProductID in the watch windown. . . Val('" & Form1.Text1.Text & "')"
and I get 1 - - which is the right number.

So I don't get the problem with the execute statement.

Do I need to use the recordset or something? Also how do I do an update?
The table 2 is PD and table 1 is Cstrs. I nned the values from table 1 to go into
table 2 and edit/update those fields in table 2.

Do I need to check if the recordset is open or something?

[tpiano]

when you do an insert you must make sure that what you are inserting doesnt exist on table2 specially if you have a primary key defined without duplicates. As i told you

INSERT INTO table2 (field1, field2) SELECT field1, field2 FROM table1 where product_id = 1

will insert all records in table1 where product_id = 1 in table2 executed directly. And if the records you are inserting already exists in table1 you will get an error if product_id is defined as primary key without duplicates if no primary key is defined it will just insert the record again.

for update statement query goes like this

UPDATE table2 SET FIELD1 = NEW_VALUE, FIELD2 = NEW_VALUE
WHERE PRODUCT_ID = 1

to check if record exists you can do like this

dim rs as db.recordset

set rs = db.openrecordset "select * from table2 where product_id = 1"
if not rs.bof and not rs.eof then
'record exist do your update statement here
else

endif

[tpiano]

when you do an insert you must make sure that what you are inserting doesnt exist on table2 specially if you have a primary key defined without duplicates. As i told you

INSERT INTO table2 (field1, field2) SELECT field1, field2 FROM table1 where product_id = 1

will insert all records in table1 where product_id = 1 in table2 executed directly. And if the records you are inserting already exists in table1 you will get an error if product_id is defined as primary key without duplicates if no primary key is defined it will just insert the record again.

for update statement query goes like this

UPDATE table2 SET FIELD1 = NEW_VALUE, FIELD2 = NEW_VALUE
WHERE PRODUCT_ID = 1

to check if record exists you can do like this

dim rs as db.recordset

set rs = db.openrecordset "select * from table2 where product_id = 1"
if not rs.bof and not rs.eof then
'record exist do your update statement here
else
'record not exist do your insert statement here
endif
rs.close
set rs = nothing

[Cheyenne]

More details below. I hope to solve this soon...(((

Please help! This is becoming frustrating.

ProductID is not defined as a primary key.

The data will already be in both tables . What I need is to do edit/update/revise
the data in table 2 to reflect the changes that were made in table 1.

So do I use the edit/update ? I just want to confirm what I have to do here.

I'm not sure I understand the below code, excuse my ignorance! What is new_value defined as? I'm trying to find out what exact steps, and how to exactly write them to solve this problem, since I don't really hve a clue as to what in the world im doing. :9

UPDATE table2 SET FIELD1 = NEW_VALUE, FIELD2 = NEW_VALUE 
WHERE PRODUCT_ID = 1 
 
'so it should be writne like this ? ?
 
Update PD set D = D, E = E, K = K, Mde = Mde,Cst=Cst where Val('" & Form1.Text1.Text & "')"