How can I pass Parameters into an Access Query 1

[Viddy]

Viddy (IS/IT--Manageme) Sep 26, 2003
I originally posted this on th Access Queries and Jet SQL Forum thread701-664995, but was advised that I would be more likely to get a response from this thread.

I have a Query which, when run from Access displays a dialog box requesting that a value is entered. The query works fine and returns the required information.

From within VBA (Excel) when I call this query using ADO command and parameter objects, it does not seem to have any parameters passed to it.

Is it possible to do this in Access - call a parameterised query, and if so how?

Regards

Viddy

 

[substitute]

See my reply to your other post

 

[oharab]

Can't remember how ADO works (will look it up in a bit), but it's easy in DAO. You need to pass each parameter explicitly.

Dim db AS Dao.Database
dim qdf as dao.querydef
dim prm as dao.parameter
dim rst as dao.recordset

set db=currentdb
set qdf=db.querydefs("qryYouWantToRun&quot

qdf!EnterName=InputBox("Enter Name&quot
qdf!OtherParameter=Forms!frmOpenForm.txtparameter

set rst=qdf.OpenRecordset

With Access queries that contain paramters that are all references to Access controls on open forms, you can simulate the expression service that Access provides when the queries are run through the user interface, as follows:

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

set rst=qdf.OpenRecordset

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------