Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    strSQL = "SET NOCOUNT ON;" & _
      " DECLARE @m_table1 AS m_table; " & _
      " DECLARE @ret int; " & _
      " INSERT INTO @m_table1 (id, val) " & _
      " SELECT 1, 3 " & _
      " union all" & _
      " SELECT 2, 4 " & _
      " union all" & _
      " SELECT 3, 5 " & _
      " union all" & _
      " SELECT 4, 6 ;" & _
      " exec @ret =  p_insert @m_table1, @ret = Null "

    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MyServer;UID=sa;PWD=MyPass;DATABASE=tempdb;"
    qdf.SQL = strSQL
    qdf.ReturnsRecords = True
    
    Set rs = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
    Do While Not rs.EOF
        Debug.Print rs.Fields("val")
        rs.MoveNext
    Loop

SELECT compatibility_level  
FROM sys.databases WHERE name = 'mydb'

use [tempdb]
go

-- Create the data type
CREATE TYPE m_table AS TABLE 
(
	id int, 
	val int, 
    PRIMARY KEY (id)
)
GO

create proc p_insert 
		@m_table1 m_table readonly
		,@ret int = Null
as 
select * from @m_table1
go