SQL, MS access VBA update

Question

I would like to seek help in my update statement in ms access. I don't know if possible but can we incorporate SELECT statement in UPDATE statement?

Here's the code that I have problem.

  DoCmd.RunSQL _
        "UPDATE tab_lists " & _
        "SET " & _
        "total_calls                = ((SELECT COUNT(filler3) FROM table WHERE [attempt_counter] = 5)  " & _
        "WHERE " & _
        "listid                     = " & listid1 & ""

June7 · Accepted Answer · 2017-03-28 20:50:18Z

2

Yes, you can.

I prefer CurrentDb.Execute:

CurrentDb.Execute ("UPDATE tab_lists SET total_calls = (SELECT COUNT(filler3) AS CountFill FROM table WHERE [attempt_counter] = 5) WHERE listid = " & listid1)

However, saving aggregate data is usually a bad idea. Should just calculate when needed.

Your table is named table? That is also not good.

edited Mar 28, 2017 at 20:50

answered Mar 28, 2017 at 20:42

June7

21.6k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kirou Over a year ago

What is the difference using the DoCmd.RunSQL over CurrentDB.Execute? My table name is actually tab_counter, thanks for your advise.

June7 Over a year ago

CurrentDb.Execute won't trigger warnings.

Collectives™ on Stack Overflow

SQL, MS access VBA update

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related