How can I put SQL result into variable in VBA

Question

I have example table like below

| ID | Qty |
| -- | ----|
| 1  | 5   |
| 2  | 7   |
| 3  | 8   |
| 4  | 9   |
| 5  | 12  |

How can I pass result of below query into VBA variable to use in next queries (update and insert)?

SELECT example_tab.Qty
FROM example_tab
WHERE ID = 4

In example that test_variable = 9

Does this answer your question? How to use a SQL SELECT statement with Access VBA — astentx
– astentx, Commented Mar 1, 2021 at 9:36
Is your VBA being hosted in Access, or in some other application? Also, do you mean to take the result of the query and use it in another statement? Some more context would be helpful. — user20416
– user20416, Commented Mar 1, 2021 at 9:41

sbgib · Accepted Answer · 2021-03-01 09:35:12Z

1

To get a single value into a variable, DLookup can be used:

Dim test_variable As Variant

test_variable = DLookup("[Qty]", "example_tab", "[ID] = 4")

answered Mar 1, 2021 at 9:35

sbgib

5,8583 gold badges22 silver badges27 bronze badges

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

Comments

Kostas K. · Accepted Answer · 2021-03-01 10:05:19Z

0

Is this what your looking for ?

sSQL = "SELECT example_tab.Qty FROM example_tab WHERE ID = 4"

Dim rs As DAO.Recordset     
Set rs = CurrentDB.OpenRecordset(sSQL)

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

answered Mar 1, 2021 at 9:31

newbie

6388 silver badges12 bronze badges