Access VBA SQL命令INSERT FOR MULTIME VALUE

发布于01月30日

我需要在VBA Access中将值添加到表中的列. 但列是数字和查找(从其他表)和多个值

我try 了许多命令:

 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

我try 过的strSQL选项:

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.

推荐答案

Some examples for MultiValue fields.
INSERT and DELETE

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

在表格中插入行

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

向多值字段插入新值

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

将第二个值插入多值字段

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

在多值字段中插入下一个值

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

BNUM中的值必须是唯一的.

从子查询插入

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