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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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