SQL or VB?

[shacket]

I want to change the field in all records with certain criteria through code. I have tried DoCmd.RunSQL but I am having some difficulty with the SQL statement. (I have made it with the query builder, but I'll spare you the details for now about what is difficult)

I know how to do it with VB, i.e.:

rs.MoveFirst
Do Until rs.EOF
If rs![Field] = Criteria Then rs![Field2] = Update
rs.MoveNext
Loop

Is there a reason (speed, efficiency, etc.) that I would want to struggle with the SQL code in lieu of doing it with the above VB code?

Thanks.

Dave

 

[Pat Hartman]

Queries are far faster than a code loop. Of course you won't see a differnce until your table gets larger than a few thousand rows.

If you use the query builder it shouldn't be a struggle. Even if you do it yourself, it's not so tough.

UPDATE AccessAndJetErrors SET AccessAndJetErrors.ErrorString = "????", AccessAndJetErrors.ErrorCode = 11111
WHERE (((AccessAndJetErrors.ErrorCode)=111));

 

[shacket]

OK Pat,

maybe you can help me, then. My problem with the updatequery is that the updates are based on information that can only be found in another query (MAX of [field]). When I put the SQL together (from the query builder) it won't let the update query execute because it says that it is not an updateable query (the query with the MAX field) even though I am asking it to update a table linked to that query.

(I hope that makes sense) How would I get around that?

Dave

 

[Pat Hartman]

Jet considers all recordsets that make use of domain functions (such as Max()) to be not-updateable. That leaves you three options. Turn the totals query into a make-table query. Then join to the tempory table to get your new values. The second option is a two-file match using VBA. The third is to sequentially read the table or query and use DMax() to retrieve the max value for each record. This last option (DMax()) could be extremely slow if you have a recordset with more than a few thousand rows.

However, storing calculated data violates the rules of proper table normalization. See the following article from Microsoft on relational database design:
http://www.microsoft.com/TechNet/Access/technote/ac101.asp