Updating Recordset???

[Stevie]

Is it correct that you can not edit/update a recordset which uses more than one table?

If so is the only/best way round this to create a temporary table with the results of the SELECT, then edit/update the temporary table, then use an UPDATE query to update the original table from the temporary table?

That sounds horrible when you try to say it

(Using Access)

Any help much appreciated.

[paulw]

No it is not true. However you can create joined queries that are not updatable.

If the query does a GROUP BY, it will not be updatable, if the query processor cannot determine the precise relationship between the joined fields (i.e. it might be many to many) it won't be updatable, calculated fields are not updatable.

Too many rules to tell you, just try creating the query and see if you can modify it - suck it and see.

Paul.

P.S. If you show me what you are trying to do, I might be able to offer more concrete help.

[Stevie]

I've dummed down the problem by testing with two tables, each with two fields. The tables have one common field.

If I open a recordset with the SQL

strSQL = "SELECT * " & _
         "FROM TableA, TableB " & _
         "WHERE TableA.FieldA = TableB.FieldA"

I get an error when I try to edit the recordset.

The error is : 3027 Can't update database or object is read only.

This seems to me that you can't edit a recordset which has linked tables.

[paulw]

Aaah! the light dawns...

Re-write using a JOIN

strSQL = "SELECT TableA.*, TableB.* " & _
         "FROM TableA INNER JOIN TableB " & _
         "ON TableA.FieldA = TableB.FieldA;"

Should work...

P.

[Stevie]

Eerggh! Jet SQL! Yuk!

Tried it and it works.

Don't know whether to thank you or cry.

I can't stand Jet SQL.

[paulw]

Originally posted by Stevie
Eerggh! Jet SQL! Yuk!

Tried it and it works.

Don't know whether to thank you or cry.

I can't stand Jet SQL.

Hate to say it Steve, but the JOIN construct is the standard way of doing it now and is fully ANSI compatible. The old method of creating JOINS in the WHERE clause is supported in SQL Server 7 but not recommended!

Get used to the JOIN syntax, it is far easier to write when you get used to it.

Cheers,

P.

[Stevie]

So it'll work with any database, like Oracle for instance?

[paulw]

Should do. Depends on the level of ANSI compatability. All the major DBEs (Oracle, Informix, Sybase, SQL Server) will support JOIN. Detailed syntax does change from Engine to Engine so you have to know the specifics by reading the documentation. But, in general, yes.

P.

[Stevie]

Cheers I'll keep it in mind, although I'm pretty sure JOIN is not used in Oracle, as this is why I never use them when writing SQL.

[SmackAttack]

What the heck version of Oracle were you using? 1.0? I can tell you from experience that JOINs are **definitely** supported in any somewhat-recent version of Oracle and SQL Server. That's a promise.

[Stevie]

When I say join I mean the sytax INNER JOIN. Is that incorporated?

I've (in the past) always been told to stay clear of them as not all db's use them. So to keep the SQL in a state where it does not need to be changed within my app I have always used joins in the WHERE clause.

[paulw]

INNER is usually implicit and by convention INNER is usually omitted. In the same way LEFT OUTER JOIN is often written LEFT JOIN. However, INNER and OUTER are actually part of the ANSI spec.

Cheers,

Paul.

[Stevie]

I'm not saying it's not part of ANSI now, what I'm trying to say is I don't think it works in every db, specifcally Oracle.

Here is a quote from one of my (too numerous) books.

It is preceded by a join in a where clause, and followed by an inner join.

The Join you have been using so far is called an EQUIJOIN - a join based on the testing of equality between two tables. This kind of join is also called an INNER JOIN. In fact, some DBMS's use a slightly different syntax for these joins specifying the type of join explicitly. The following SELECT statement returns the exact same data as the preceding example (this will not work with Oracle):

I'm not trying to be argumentative honest

[paulw]

You've missed out the juicy bit of the quote. As far as I was aware though, Oracle 8+ is certified fully ANSI compliant. That doesn't mean that they won't have their own extensions...

I'll do a bit of digging, but why don't you just try a query? I can't, I don't have Oracle available.

Cheers,

P.

P.S. If you argue any more, it'll be round the back of the bike sheds for you my lad!!!

[Stevie]

I don't have Oracle available here either. I used to connect to an Oracle database on one of my projects in my last job, but we have SQL Server and Access here.

Cheers for you thoughts anyway.

Good to see another Londoner about.

By the way "Round the back of the bike sheds" means something different from my time.

[paulw]

Yeh, I did wonder about that, but if you'd seen the bike sheds at my school, they were ideal for fighting - out of sight, but shagging would have been a big no, no - it was right on the main road<grin>

Cheers now,

P.

Incidentally, a few of us are getting tgether for beers next week in Bank/Moorgate area. Mail me w/ contact details if you are interested.

[Stevie]

Cheers for that, I'll give you a mail if I'm going to be about next week.