This works:

```
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM dbo.ftblTest(1,2,3)", CP.Connection, adOpenKeyset, adLockReadOnly

```

But it would be nicer to do this:

```
rst.Open "SELECT * FROM dbo.ftblTest(@Param1=1,@Param2=2,@Param3=3)", CP.Connection, adOpenKeyset, adLockReadOnly

```

If I try the second method I get the error: "parameters were not supplied for the function ftblTest"

Is it possible to use named parameters with multi-statement table-valued functions?

**Edit 1: Examples Added Using Command Object**

*First the SQL*

```
create function ftblTest (@Input int)
RETURNS @Results TABLE (
  OutputField int
  )
AS
BEGIN
INSERT INTO @Results SELECT @Input
Return
End

```

*Some Code* (run from inside an Access 2003 ADP, with a connection to the correct SQL DB)

```
Public Sub test()
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command

  'method 1 works
  rst.Open "SELECT * FROM dbo.ftblTest(2)", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
  Debug.Print rst.Fields(0)
  rst.Close

  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdTable

    'method 2 works
    .CommandText = "dbo.ftblTest(3)"
    Set rst = cmd.Execute
    Debug.Print rst.Fields(0)

    'method 3 fails
    .CreateParameter "@Input", adInteger, adParamInput, , 4
    .CommandText = "dbo.ftblTest(@Input)"
    Set rst = cmd.Execute 'error here:-2147217900   Must declare the scalar variable "@Input".
    Debug.Print rst.Fields(0)

  End With
End Sub

```

How can I get the named parameters to work in method 3?

**Edit 2: test code modified to use Parameters.Append**

```
Public Sub test()
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command
  Dim p As New ADODB.Parameter

  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdTable

    'Parameter Append method fails
    p = .CreateParameter("@Input", adInteger, adParamInput, , 4)
    Debug.Print p.Name, p.Type = adInteger, p.Direction = adParamInput, p.SIZE, p.Value 'note that name not set!
    With p
      .Name = "@Input"
      .Type = adInteger
      .Direction = adParamInput
      .SIZE = 4 'this shouldn't be needed
      .Value = 4
    End With
    Debug.Print p.Name, p.Type = adInteger, p.Direction = adParamInput, p.SIZE, p.Value 'properties now set
    .Parameters.Append p
    .CommandText = "dbo.ftblTest(@Input)"
    Set rst = cmd.Execute 'error here:-2147217900   Must declare the scalar variable "@Input".
    Debug.Print rst.Fields(0)

  End With
End Sub

```

this still doesn't work.

**Edit 3: I removed the @ from create parameter**

as suggested and tried the CommandText 3 ways and got 3 different errors:

```
.CommandText = "dbo.ftblTest" 

```

error: Parameters were not supplied for the function 'dbo.ftblTest'. 

```
.CommandText = "dbo.ftblTest()" 

```

error: An insufficient number of arguments were supplied for the procedure or function dbo.ftblTest. 

```
.CommandText = "dbo.ftblTest(Input)" 

```

error: "Input" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.