By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,034 Members | 960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,034 IT Pros & Developers. It's quick & easy.

Show record count from a SQL Server Temp Table used in a form recordsource

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
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?


Return the value as an output parameter. Instead of binding the form
directly to the procedure, (I guess this is an ADP?), create the recordset
in code with ADO - then you can read the returned parameter and bind the
recordset to the form.
Nov 13 '05 #2

P: n/a
Laren,
=Count(*) in a text control on the form will count the rows in the form.
Doesn't matter what the source of the data is. Also, you can add an aliased
value ("CONTACTS" AS SELECT_SOURCE . . .) to your union query that
identifies which select statement contributed the result. You can then use
DCount to count only those from the select statement that has your contacts.

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

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?

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.