Hierarchies revisited

[Guest]

I have a stored procedure on my SQL Server that can capture a downstream
expanding hierarchy of my employee table. It captures all employees
belonging to a supervisor, and any supervisors/employees under that
supervisor. In SQL Analyzer, I have a select statement with a variable that
when executed, uses the stored procedure to list those names. The code (from
Itzik Ben-Gan from SQL Server Magazine), modified for my purposes for both is
below:

CREATE FUNCTION ufn_GetSubtree
(
@supervisorid AS int
)
RETURNS @tree table
(
employeeid int NOT NULL,
supervisorid int NULL,
lname varchar(25) NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS

BEGIN

DECLARE @lvl AS int, @path AS varchar(900)
SELECT @lvl = 0, @path = '.'

INSERT INTO @tree
SELECT employeeid, supervisorid, lname,
@lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
FROM tblemp
WHERE employeeid = @supervisorid

WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1

INSERT INTO @tree
SELECT E.employeeid, E.supervisorid, E.lname,
@lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
FROM tblEmp AS E JOIN @tree AS T
ON E.supervisorid = T.employeeid AND T.lvl = @lvl - 1
END

RETURN

END
-------------------------------------------------------------
select * from ufn_getsubtree(00285) as s
where not exists(select * from tblemp as e
where e.supervisorid = s.employeeid)

Everything is working fine on the server side. Now I want to be able to use
the sp and the SQL statement in an Access routine. Going on the assumption
that an Execute (ADO Command) can return a recordset, I want to use that
recordset to populate a list box on a form (this is then used to run an
existing query which feeds a report). Can someone guide me through the steps
necessary to accomplish this?

thanks a ton

 

[Douglas J. Steele]

Actually, you can do it without ADO if you want.

Create a pass-through query that calls the stored procedure, and you're
done. A problem is that you can't pass parameters to pass-through queries,
but you can rebuild the SQL associated with the query each time and run it
that way.

Here's an example of call a SQL Server SP from Access using ADO, though:

Dim conCurr As ADODB.Connection
Dim rsCurr As ADODB.Recordset

Set conCurr = New ADODB.Connection
conCurr.Open "xxx", , adAsyncConnect

Set rsCurr = conCurr.Execute("Is_Security")
UserHasSecurity = rsCurr(0)

(I replaced the actual Connection String with "xxx" above)

 

[Guest]

Thanks for the quick reply. I really feel like I'm making some progress now.
However...

I misspoke when I called it a stored procedure; it's actually a user-defined
function. Does that make any difference? Also, since this is my first foray
into ADO, I'll need some help up the learning curve. Are there examples of
code that I can look at to get my hands around the syntax? Can someone here
give me a full-blown example I can learn from? I think I should be able to
build my sql statement to pass on the parameters; but if I had an example, I
can figure it out. Also, can you give me an example of a full Connection
String that will help me understand its use?

tia,
JMorrell

 

[kai]

Actually, you can do it without ADO if you want.

Create a pass-through query that calls the stored procedure, and you're
done. A problem is that you can't pass parameters to pass-through queries,
but you can rebuild the SQL associated with the query each time and run it
that way.

Here's an example of call a SQL Server SP from Access using ADO, though:

Dim conCurr As ADODB.Connection
Dim rsCurr As ADODB.Recordset

Set conCurr = New ADODB.Connection
conCurr.Open "xxx", , adAsyncConnect

Set rsCurr = conCurr.Execute("Is_Security")
UserHasSecurity = rsCurr(0)

(I replaced the actual Connection String with "xxx" above)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


both