How to insert multiple values into a Lookup Field in an Access database via SQL?

Question

How do you insert multiple values into a Lookup Field in an Access database using ASP?

(I've tried a few approaches, so I'm not even sure which code to show as an attempt.)

Answer 1

For a sample table named [Agents] with a multi-value Lookup Field named [Languages] ...

the following VBScript code represents one way to add a new Agent named "Maria" who speaks both English and Spanish

Option Explicit
Dim con, cmd, rst, newID
Const adInteger = 3
Const adVarWChar = 202
Const adParamInput = 1
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set con = CreateObject("ADODB.Connection")
con.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\Public\Database1.accdb"

' insert all fields *except* multi-value Lookup Field
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO Agents (AgentName) VALUES (?)"
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, "Maria")
cmd.Execute 
Set cmd = Nothing

' get AutoNumber ID of newly-inserted record
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT @@IDENTITY", con, adOpenStatic, adLockOptimistic
newID = rst(0).Value
rst.Close
Set rst = Nothing

' insert multi-value Lookup Field values
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO Agents (Languages.Value) VALUES (?) WHERE AgentID=?"
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255)
cmd.Parameters.Append cmd.CreateParameter("?", adInteger, adParamInput)
cmd.Prepared = True
cmd.Parameters(1).Value = newID
' first value
cmd.Parameters(0).Value = "English"
cmd.Execute
' second value
cmd.Parameters(0).Value = "Spanish"
cmd.Execute
Set cmd = Nothing

con.Close
Set con = Nothing

While this may answer the immediate requirements of the question, it is important to note that:

1. Access SQL support for manipulating Lookup Fields is incomplete and can be inconsistent from one development environment to another,

2. "Microsoft strongly recommends against using Access in web applications" (ref: here), and

3. Seasoned Access developers recommend against using Lookup Fields (ref: here) except in very specific circumstances (e.g., for integration with SharePoint).