I've prepared a query in Access that calculates a number that I want to display in Excel, using VBA and ADO. Here's the query in Access:

PARAMETERS [p_month] DateTime;
SELECT Count(*) AS [Number]
FROM (SELECT DISTINCT Occupancy.PersonID
FROM Occupancy WHERE (((Occupancy.Month)=[p_month]))
) AS [Table];

So, the query accepts a parameter and returns a value. Because it's just a value, I figure I won't need to construct a recordset object. All examples I've seen so far returns a recordset, but I hope to make this simple and fast. How would you call this query from VBA and how do you extract the return value?

Gustaf

Not that familiar with Acess, but most likely you could do this be adding an
"out" parameter to your procedure.

http://groups.google.com/group/micro...4f98c5257ffa54

Tim


"Gustaf" wrote in message
...
I've prepared a query in Access that calculates a number that I want to
display in Excel, using VBA and ADO. Here's the query in Access:

PARAMETERS [p_month] DateTime;
SELECT Count(*) AS [Number]
FROM (SELECT DISTINCT Occupancy.PersonID
FROM Occupancy WHERE (((Occupancy.Month)=[p_month]))
) AS [Table];

So, the query accepts a parameter and returns a value. Because it's just a
value, I figure I won't need to construct a recordset object. All examples
I've seen so far returns a recordset, but I hope to make this simple and
fast. How would you call this query from VBA and how do you extract the
return value?

Gustaf