Update an opened recordset

[bulrush]

Access 2003
I am looping through records in a recordset. While looping and reading, for each record I want to write to one field of one of the tables and store a calculated value.

I want to update a recordset, but this recordset is composed of multiple tables, and cannot be updated. I just want to update one field on one table, that is part of the recordset. Can this be done?


Here is the recordset I am looping through.

    Set Myset = mydb.OpenRecordset(sqlstuff, dbOpenDynaset)
    Myset.MoveFirst
    Myset.MoveLast
    cnt = Myset.RecordCount
    Myset.MoveFirst

The recordset involves 2 left joins and a total of 3 tables. Can I even do what I want inside the loop, or do I have to store a list of keys and values I want to update, and update them after the recordset is closed, and outside the loop?

Thanks.

 

[datAdrenaline]

>> .... and store a calculated value. <<

That is not such a good idea. The results of calculations should really not be stored. Store the operands, and calc the result 'on the fly'.

-----

With respect to updateing a record in a recordset, that is quite feasible, however, with a SQL statement with JOINS, the ability to update can easily be hampered. Is the field on the "1" side of your JOIN indexed uniquely?

Ultimately there are many ways to update a record ... one of which is to simply execute an UPDATE type SQL Statement to the database engine in order to UPDATE a single field on a single table.

For more help, it would probably be wise to show us your SQL statement and the fields you are wishing to update.

 

[HiTechCoach]

If it were me, within the loop, I woudl run an Update query that updates the desired record. This assumes that the recorset you are processing has the primary key for the table you want to update.

note: Normally it is considered "best practice" to NOT store calculations.

 

[bulrush]

>> .... and store a calculated value. <<

That is not such a good idea. The results of calculations should really not be stored. Store the operands, and calc the result 'on the fly'.

I understand the terms you are using, but not sure how they apply to database programming. Not sure what you are saying.

All the time I loop through a recordset, do some calculations, like concatenating strings. Especially when I am created tab-delimited text files, which is what I am doing in this case. The issue is, I am creating a new code number in this loop, based on info in the recordset, and I need to store this new code on the table on the 1 side of the JOIN.

If I have a string stored in my recordset, for every record, I want to prefix this string with "PLU" and store it in another field on one of the tables in my recordset. It gets really complicated if I do this inside a query, and I don't now SQL that well.

-----

With respect to updateing a record in a recordset, that is quite feasible, however, with a SQL statement with JOINS, the ability to update can easily be hampered. Is the field on the "1" side of your JOIN indexed uniquely?

Yes.

Ultimately there are many ways to update a record ... one of which is to simply execute an UPDATE type SQL Statement to the database engine in order to UPDATE a single field on a single table.

For more help, it would probably be wise to show us your SQL statement and the fields you are wishing to update.

It has proprietary information, and the customer name is part of the table name, and that is repeated about 30 times, so I can't do that.

Is there a good website with a summary of SQL commands? That would help me a lot. It seems like SQL really comes in handy sometimes in Access. And I can use Docmd.Runsql to do my SQL.

I could search Google and spend 60 minutes perusing 100,000 useless results, but I thought someone might have a good website already.

 

[bulrush]

I guess my question really was, is it possible to do an UPDATE sql statement while the recordset is open and pointing to my current record? My thought was that record would be locked and there would be no way to update it through any method.

 

[DCrake]

If you open up your recordset and perform your calculations to give you the answer.

You now want to update a particular record in a table. Now it just so happends that this table is part of the recordset, which incidently is not updateable. Lets assume you know which record you want to update in your table, you need the following pieces of info

A The table you want to update
B The field you want to update
C The primary key of the record you want to update
D The value you want to place in the nominated field
E The Primary Key Field Name

If you have all this info then simply do the following

DoCmd.SetWarnings False
DoCmd.RunSQL "Update A Set B = D Where E = C"
DoCmd.SetWarnings True

David

 

[bulrush]

Thank you Dcrake. Now I know it's possible, I do have that information, and I'm only updating one field on one table for each record I'm looping through.

Oh, since I'm not an expert in SQL, doesn't every SQL statment need a semi-colon at the end?

 

[DCrake]

Not necessarily, it won't fallover if you omit it.

 

[gemma-the-husky]

but if the query is non-updateable, then opening it as a recordset will also be non-updateable