Updating an Access table - using a join?

[pulsedav]

Hi there,

I am filling a dataset with a select query which involves a 2 table join (using 2 tables in Access)

It need to go through every record through the dataset and update some of the rows on just 1 of the tables, but...updating the underlying tables fails.

I have searched MSDN and Google fo help but to no avail.

Does anyone know how to do this?​

 

[jmcilhinney]

We would need to see some code to know what the problem might be. Also, operations can fail for all sorts of reasons. We would also need some information about what actually happens, like do you get an exception and on what line of code, or does your app proceed but your database doesn't reflect the change. I would suggest posting your SELECT statement, your UPDATE statement and the section of code that performs the update, along with details of any exception that occurs and where exactly it occurs.

 

[kulrom]

If i'm not wrong you could use the update method of the SqlDataAdapter objects to modify the two tables ... correct me if i'm wrong.


Dim sqlConn As SqlConnection = New SqlConnection(...)
Dim sqlAdpt1 As SqlDataAdapter = New SqlDataAdapter("select * from table1", sqlConn)
Dim sqlAdpt2 As SqlDataAdapter = New SqlDataAdapter("select * from table2", sqlConn)here goes commandbuilder objects

Dim commandBuilder1 As SqlCommandBuilder = New SqlCommandBuilder(sqlAdpt1)
Dim commandBuilder2 As SqlCommandBuilder = New SqlCommandBuilder(sqlAdpt2)

then:

sqlAdpt1.Update(blah, "Table1")
sqlAdpt2.Update(blah, "Table2")


Cheers


http://www.devnewsgroups.net/link.aspx?url=http://support.microsoft.com/kb/313485/EN-US/

 

[jmcilhinney]

Kulrom's suggestion will work if you use two separate queries to retrieve the two tables. You cannot, however, use a command builder to automatically generate non-query commands from a query that involves more than one table.

 

[kulrom]

The .NET Framework offers many different ways to solve the same problem.
You should always tend to find easier one ... Cheers