Updating Fields in Multiple Table SELECT Queries

[Guest_imported]

Hi, all,

I'm new to SQL. I'm using a Visual Basic front end to a Jet Database, and am trying to use the VisData application provided with Visual Basic to produce recordsets on which I can work manually. I cannot use the Query Design option if I open the database in Access, since the database is too old. I cannot upgrade the database, as it will not then be useable with my Visual Basic front-end application. I think I'm in the right forum!

I can write queries using either of the tables in the code below, and can update fields in existing records at will in the resultant recordsets. However, although the code below does exactly as required in terms of displaying records from both tables, I cannot update any fields. Records from both tables are joined and displayed (one-to-many relationship) in the resultant recordset.

SELECT
Item.`ItemName`, Item.`Cat`,
Lot.`LotName`, Lot.`Exp`, Lot.`Rec`, Lot.`Quant`,
Lot.`Stock`
FROM
`Item` Item INNER JOIN `Lot` Lot ON
Item.`ItemNum` = Lot.`LinkNum`
WHERE
Item.`Show` = 1 AND
Item.`Type` = 2
ORDER BY
Lot.`LinkNum` ASC

The documentation I have been able to find suggests that I should be able to edit all updatable fields, even with joined tables. All of the fields in the SELECT statement are updatable, and the object is a Table Type object, as opposed to a Snapshot. Incidentally, the code was adapted from the SQL view in Crystal Reports 7.0 Pro. Can anyone explain or suggest what the problem is? Many thanks in advance to respondents, and to forum organisers for a great resource,

Johnny Jebel

 

[peekay]

You must open the recordset as a dynaset if you wish to update.
Alternatively you must check that both tables have indexes if they are joined and you wish to update them

PK Odendaal
pko@mweb.co.za

 

[Quehay]

[tt]Dynaset inconsistent updates [/tt] will allow updating of the joined fields which would otherwise not be updatable in a typical dynaset. Here's the pertinent Access Help section:

RecordsetType Property


You can use the RecordsetType property to specify what kind of recordset is made available to a form. For example, if you don't want data in bound controls to be edited when a form is in Form view or Datasheet view, you can set the RecordsetType property to Snapshot.

Setting

The RecordsetType property uses the following settings in a Microsoft Access database (.mdb).

Setting Visual Basic Description
Dynaset 0 (Default) You can edit bound controls based on a single table or tables with a one-to-one relationship. For controls bound to fields based on tables with a one-to-many relationship, you can't edit data from the join field on the "one" side of the relationship unless cascade update is enabled between the tables. For more information, see the topic that explains when you can update records from a query.
Dynaset (Inconsistent Updates) 1 All tables and controls bound to their fields can be edited.
Snapshot 2 No tables or the controls bound to their fields can be edited.