I am using a stored procedure as the recordsource on an MS-Access2000
form:
Forms!frmName.RecordSource = "dbo.myStoredProcedure"
The stored procedure creates a temp table #Contacts and then inserts
matching KeyID values contacts into it. Then I use a union query in
the same stored procedure to create the actual recordset.
What I want to know is how I can return the number of records in the
temp table #Contacts back to MS Access and display it?
I can't just do a VBA recordcount because that will count all the
records returned from the union queries.
What I am doing now feels sloppy, which is to count the records in the
temp table, then display the count (@myCount) in every row of the
recordset, which I display in the header of the form.
Any help is appreciated,
LQ
Example below:
Alter Procedure "myStoredProcedure"
AS
set nocount on
DECLARE @myCount as integer
CREATE TABLE #Contacts (id int identity, ContactID int)
INSERT INTO #Contacts(ContactID)
SELECT
Contacts.ID
FROM
Contacts
WHERE
Contacts.LastName = 'Jones')
SELECT @myCount = count(*) FROM #Contacts
SELECT
#Contacts.ContactID,
NULL as DATA,
@myCount AS CNT
FROM
#Contacts
UNION SELECT
#Contacts.ContactID,
Addresses.AddressInfo AS DATA,
@myCount AS CNT
FROM
#Contacts LEFT OUTER JOIN
Addresses ON
#Contacts.ContactID = Addresses.ContactID
