Updating a recordset containing a join using ADO2

[steven_lee_100]

I have created a recordset using ADO2 that contains a join of 2 tables in my oracle database. Reading the data from the recordset causes no problems, however when I try to update the recordset, ADO spits out an unhelpful error message.

Please help somebody.

Thanx in advance,

Steve

[JHausmann]

I don't believe you can update "joined" data. You should look into creating a view which should be updatable (although under SQL Server 6.x, you cannot update more than one table in an update statement, you have to use an update statement for *each* table you need to update. You also cannot delete rows from a view). The view can contain everything in your join (well, in Sql Server anyway).

[Clunietp]

That reminds me of a question, JHausmann... what is the difference between a view (like in SQL Srv) and a query (like in Access)?
What features does a view provide (or lack) that a query does [not]

thanks

[Crazy D]

A view is a select query that you use as if it is a table (eg, you can open it by rst.open "MyView" instead of rst.open "SELECT ....."
A query in Access can AFAIK contain an update statement too, which a view can't (but you can use a stored procedure for that)

[JHausmann]

Crazy D is correct with a minor exception. You can actually issue an update against a view, *as* long *as* there's only one table affected by the update command. So, if your view has 3 tables to update you need to issue 3 update commands...

Views tend to be fast because they're pre-compiled (like stored procudres).

[Clunietp]

I hate to keep bugging you guys but I still do not see any advantage to using a view. You can open an access query just like a table using rst.open "querynamehere" while stored procedures are precompiled and can be SELECT statements that return a recordset (like a view). Can you give me a situation where using a view would be the best choice?

Thanks

Tom

[André]

Hello, I'm actually in the same situation:
that is I have a Join and want to update it.
I have no problems updating, my problem comes when I want to insert, but I'm posting this question later.

Now here is what I do:
1. I'm working with disconnected recordsets.
2.Here is my example code:
Under the Update button :
(remember to place the cursor in the right record)
rs.move (to desired row)
rs!Type = NewValue
.
.
.
rs.Update

reconnect the recordset and update:
rs.UpdateBatch

this has worked for me, even when joining several tables.

(Imperative though to have a primary key in all tables)

regards
André

[JHausmann]

Tom,

1) Views are not available in Access. So if that's your DBMS of choice, it will make no difference.
2) Views allow the DBA to control access to data. For example, a personnel database might have employee data that is sensitive, salaries and bonus information, perhaps. By creating a view that prevents exposing this information, the database is more secure (with regard to sensitive data).
3) views are like tables. You can update, delete and insert data into them. You cannot do this with a simple select statement.
4) views can be used to simplify queries. This allows you to query a complex data structure as if it were a single flat table. They can be used to hide complex joins. They can make life easier for the programming staff if the staff has little or no experience with SQL (programmers won't have to worry about debugging complex SQL statements)

[Clunietp]

That helps, thanks! one question... if you create a view that joins a few tables (to hide the complexity) will you be able to add/delete records using that view?

Thanks

Tom

[JHausmann]

Yes.