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

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.

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

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