newbie question on updating Access mdb tables
-
I am new to mdb data bases and trying to copy data from a column in one table to a column in another table. Recently I wrote a short program to correct the data in one table of several mdb files. Below is the code I was using which works fine.
'removes unwanted characters from Optional Info23 Dim OpInfo23\_upd As New OleDbCommand OpInfo23\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO23 = mid(OPINFO23,'2','8') & mid(OPINFO23,'11','2') WHERE OPINFO23 > ''" OpInfo23\_upd.Connection = conn OpInfo23\_upd.ExecuteNonQuery() 'sets flag character in Optional Info25 Dim OpInfo25\_upd As New OleDbCommand OpInfo25\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO25 = mid(OPINFO23,'1','1')" OpInfo25\_upd.Connection = conn OpInfo25\_upd.ExecuteNonQuery()Now I am attempting to add the code to copy the data from the NAME column in the required information table (REQ_INFO) to the OPINFO29 column in the optional information table (OP_INFO). I tried the code below, but it generates an exception. Is it possible to update the table directly from another table?
Dim OpInfo29\_upd As New OleDbCommand OpInfo29\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO29 = REQ\_INFO.NAME WHERE OP\_INDEX = REQ\_INFO.REQ\_INDEX" OpInfo29\_upd.Connection = conn OpInfo29\_upd.ExecuteNonQuery() -
I am new to mdb data bases and trying to copy data from a column in one table to a column in another table. Recently I wrote a short program to correct the data in one table of several mdb files. Below is the code I was using which works fine.
'removes unwanted characters from Optional Info23 Dim OpInfo23\_upd As New OleDbCommand OpInfo23\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO23 = mid(OPINFO23,'2','8') & mid(OPINFO23,'11','2') WHERE OPINFO23 > ''" OpInfo23\_upd.Connection = conn OpInfo23\_upd.ExecuteNonQuery() 'sets flag character in Optional Info25 Dim OpInfo25\_upd As New OleDbCommand OpInfo25\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO25 = mid(OPINFO23,'1','1')" OpInfo25\_upd.Connection = conn OpInfo25\_upd.ExecuteNonQuery()Now I am attempting to add the code to copy the data from the NAME column in the required information table (REQ_INFO) to the OPINFO29 column in the optional information table (OP_INFO). I tried the code below, but it generates an exception. Is it possible to update the table directly from another table?
Dim OpInfo29\_upd As New OleDbCommand OpInfo29\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO29 = REQ\_INFO.NAME WHERE OP\_INDEX = REQ\_INFO.REQ\_INDEX" OpInfo29\_upd.Connection = conn OpInfo29\_upd.ExecuteNonQuery() -
-
I am new to mdb data bases and trying to copy data from a column in one table to a column in another table. Recently I wrote a short program to correct the data in one table of several mdb files. Below is the code I was using which works fine.
'removes unwanted characters from Optional Info23 Dim OpInfo23\_upd As New OleDbCommand OpInfo23\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO23 = mid(OPINFO23,'2','8') & mid(OPINFO23,'11','2') WHERE OPINFO23 > ''" OpInfo23\_upd.Connection = conn OpInfo23\_upd.ExecuteNonQuery() 'sets flag character in Optional Info25 Dim OpInfo25\_upd As New OleDbCommand OpInfo25\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO25 = mid(OPINFO23,'1','1')" OpInfo25\_upd.Connection = conn OpInfo25\_upd.ExecuteNonQuery()Now I am attempting to add the code to copy the data from the NAME column in the required information table (REQ_INFO) to the OPINFO29 column in the optional information table (OP_INFO). I tried the code below, but it generates an exception. Is it possible to update the table directly from another table?
Dim OpInfo29\_upd As New OleDbCommand OpInfo29\_upd.CommandText = \_ "UPDATE OP\_INFO SET OPINFO29 = REQ\_INFO.NAME WHERE OP\_INDEX = REQ\_INFO.REQ\_INDEX" OpInfo29\_upd.Connection = conn OpInfo29\_upd.ExecuteNonQuery()I think you need to do an inner join on the tables. Like:
UPDATE OP_INFO
INNER JOIN REQ_INFO ON OP_INFO.OP_INDEX = REQ_INFO.REQ_INDEX
SET OP_INFO.OPINFO29 = [REQ_INFO].[NAME];It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
modified on Thursday, June 17, 2010 5:20 PM
-
I think you need to do an inner join on the tables. Like:
UPDATE OP_INFO
INNER JOIN REQ_INFO ON OP_INFO.OP_INDEX = REQ_INFO.REQ_INDEX
SET OP_INFO.OPINFO29 = [REQ_INFO].[NAME];It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca
modified on Thursday, June 17, 2010 5:20 PM