MS Access 2000 and SQL Server Stored Procedures

[Tony]

I'm new to writing stored procedures in SQL Server (using
SQL Server 7.0 SP3) and calling them in
an MS ACCESS 2000 APP. The problem I'm running into is
that the stored procedure seems to be executing,
but not opening the recordset let alone returning any
records. This stored procedure
is to take 1 input parameter and return a single record
with just one field. I've tried eliminating the
input parameter to no avail. Below is the stored
procedure, the test in the SQL Server Query Analyzer,
and VBA code that I'm trying to get to work. Any help is
greatly appreciated.


Stored Procedure

if exists (select * from sysobjects where id = object_id
(N'[dbo].[NextAvailableDLCI]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[NextAvailableDLCI]
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

CREATE procedure NextAvailableDLCI (
@circuit int
)
AS
declare @TempDLCI int;

Begin
create Table #DLCItbl (DLCI int)
set @TempDLCI = 16
while @TempDLCI < 992
begin
insert into #DLCItbl (DLCI) values
(@TempDLCI)
set @TempDLCI = @TempDLCI + 1
end
delete from #DLCItbl where exists (select 1
from pvcbinding
as p
where p.networkid
= @circuit
and p.DLCI =
#DLCItbl.DLCI)
select min(DLCI) 'DLCI' From #DLCItbl
drop table #DLCItbl

end

GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

***********************
SQL SERVER Query ANALYZER

exec nextavailableDLCI 10169

***********************
VBA code to call the stored procedure

Private Sub cmdAllocate_Click()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
' the connection string used is for a trusted
connection
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial
Catalog=WNIDBMS01;Data Source=NEW-96RC6PJS9NR"
cnn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "NextAvailableDLCI"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@circuit", adInteger,
adParamInput, Len(intNetworkID))
prm.Value = intNetworkID
cmd.Parameters.Append prm

Set rs = New ADODB.Recordset
rs.Open cmd, , , , adCmdStoredProc
If Not rs.EOF Then
rs.MoveFirst
txtDLCI = rs("DLCI")
End If

If Not rs Is Nothing Then
If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
End If

If Not cmd Is Nothing Then
Set cmd = Nothing
End If

End Sub