Access VBA SQL command INSERT for multiple value

Question

I need to add values to column in table in VBA Access. But column is number and lookup (from other table) and multiple value

I tried many commands:

 Private Sub buttRec_Click()
   Dim db As DAO.Database
 
   strSQL = "INSERT INTO TABB (BNUM) VAULE (3)"
   Set db = CurrentDb
   db.Execute strSQL
   Set db = Nothing
 End Sub

options for strSQL which I tried:

strSQL = "INSERT INTO TABB (BNUM) VALUES (1), (2), (3);"
strSQL = "INSERT INTO TABB (BNUM) VALUES (1,2,3);"
strSQL = "INSERT INTO TABB (BNUM) VALUES ('1,2,3');"
strSQL = "INSERT INTO TABB (BNUM) VALUES (1;2;3);"
strSQL = "INSERT INTO TABB (BNUM) VALUES ('1;2;3');"
strSQL = "INSERT INTO TABB (BNUM) SELECT '1' UNION SELECT '3'"
strSQL = "INSERT INTO TABB (BNUM) SELECT 1 UNION SELECT 3"

How to do this?
Thanks for help.

Answer 1

Some examples for MultiValue fields.
INSERT and DELETE

Test data:
table TABB with columns Id - primary key, RowName - text,BNUM - multivalued field.

Insert row to table

INSERT INTO TABB ( RowName )
VALUES ("New row 1");

Insert new value to multivalued field

INSERT INTO TABB (BNUM.[Value])
VALUES (11)
WHERE TABB.RowName="New row 1";

Insert second value to multivalued field

INSERT INTO TABB (BNUM.[Value])
VALUES (22)
WHERE TABB.RowName="New row 1";

Insert next value to multivalued field

INSERT INTO TABB (BNUM.[Value])
VALUES (1)
WHERE TABB.Id=1;

Values in BNUM mast be unique.

Insert from subquery

INSERT INTO TABB ( MField.Value )
SELECT  MainValue *10 from (select DISTINCT MainValue FROM Head)
WHERE ((([RowName])="New row 1"));

Multiple value fields operations thru Recordset.
Examples for Add and Delete operations

Public Function TestMulti() As String
Dim rsTab As Recordset
Dim rsM As Recordset
    Set rsTab = CurrentDb.OpenRecordset("SELECT * FROM tbTestMulti WHERE MultiName='New row 1'", dbOpenDynaset)
    If Not (rsTab.EOF And rsTab.BOF) Then
        rsTab.Edit
        ' access to multivalued field members thru recordset !
        Set rsM = rsTab!MField.Value

        With rsM
            .AddNew
            !Value = "New prog value1"
            .Update
            
            .AddNew
            !Value = "New prog value2"
            .Update
        End With
        rsTab.Update
    End If
    TestMulti="Ok"
End Function

Public Function TestMulti2() As String
Dim rsTab As Recordset
Dim rsM As Recordset
    Set rsTab = CurrentDb.OpenRecordset("SELECT * FROM tbTestMulti WHERE MultiName='New row 1'", dbOpenDynaset)
    If Not (rsTab.EOF And rsTab.BOF) Then
        rsTab.Edit
        Set rsM = rsTab!MField.Value
        rsM.FindFirst "Value = 'New prog value1'"
        If Not rsM.NoMatch Then
            With rsM
                .Delete
            End With
        End If
        rsTab.Update
    End If
    TestMulti2="Ok"
End Function

Answer 2

Doing one insert per row you wish to insert in a loop is an option:

Private Sub buttRec_Click()
   Dim db As DAO.Database
 
   Set db = CurrentDb
   For i = 1 To 3
        strSQL = "INSERT INTO Tabb (BNUM) VALUES (" & i & ")"
        db.Execute strSQL
   Next i
   Set db = Nothing
 End Sub

This will be slow if you have a large number of rows to add, but for a few, this will be very quick.

Optionally you could use that UNION query route you were headed down and hijack another table to keep it from throwing an error.

INSERT INTO Tabb (BNUM)
SELECT *
FROM (
    SELECT TOP 1 1 as BNUM FROM MSysObjects
    UNION ALL SELECT TOP 1 2 FROM MSysObjects
    UNION ALL SELECT TOP 1 3 FROM MSysObjects
   ) dt