Passing parameters via MS Query to Access

[Yoam69]

I have an Access query that contains a parameter called [AgeDate]. When I
run the query in Access, it does what I expect...it asks for the value of
[AgeDate] and then executes the query and uses that value correctly in the
calculations therein.

I want to connect to that query using Excel so I can analyze the data there.
So, I'm setting up an ODBC connection to the Access database. This works
just fine as long as there is no paramater value in the Access query.
However, with a paramater value specified in the Access query, I get an error
message from MS Query that says "Too Few Parameters. Expected 1."

So, my question is this: is there a way to pass a parameter value to the
Access query from Excel?

 

[Michel Walsh]

If you use ADO, you can use EXECUTE (or EXEC ) :



CurrentProject.Connection.Execute "CREATE PROCEDURE thisQueryHasOneParam(
itemIdparam LONG) AS SELECT * FROM somewhere WHERE itemID=itemIdParam "


and


CurrentProject.Connection.Execute "EXECUTE thisQueryHasOneParam 1021"


Note that you can have more than one parameter, and that you don't use ( )
around the parameters, in the EXEC statement.


CurrentProject.Connection is the default ADO connection already maintained
for you, by Access, when you use Access. Since your code is under Excel,
from what I understand, you will have to define your own ADO connection.


You can also use the more verbose parameters collection, with ADO.

You can also use DAO, probably. Take a look at
http://www.mvps.org/access/queries/qry0013.htm



Hoping it may help,
Vanderghast, Access MVP