Pass Parameter to Access Query

[Al]

I'm using the following code to open a query in Microsoft
Access.

Sub Test()
Dim appAccess As New Access.Application

appAccess.OpenCurrentDatabase "c:\reports\test.mdb"
appAccess.DoCmd.OpenQuery "query3"

End Sub

I've made the query, query3 a parameter query and i would
like the query to pick up a parameter value from my Excel
worksheet. How might I do this? Thanks in advance.

 

[Dick Kusleika]

Al

There's really no easy way to do that. First, why are you running the query
from inside Excel? There may be a better way to do what you are trying to
do.

If you must do it this way, there is one option that I can think of. Remove
the parameter from the Access query and rebuild the SQL of the QueryDef
object to include the cell's value in the criteria.

 

[Jake Marx]

Hi Al,

As Dick said, unless you have to, I wouldn't recommend opening Access and
running the query using DoCmd. There's no reason to add the overhead of the
Access interface to Jet if you don't have to. You can do all of this using
ADO (or DAO). Basically, you set up a connection, then execute the query as
a command with parameters.

Here's an article that should get you started (it uses VBScript, which is
very similar to VBA):

http://support.microsoft.com/defaul...port/kb/articles/Q200/1/90.ASP&NoWebContent=1

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[onedaywhen]

Remove
the parameter from the Access query and rebuild the SQL of the QueryDef
object to include the cell's value in the criteria.

Alternatively, assuming MS Query, keep the parameters and rebuild the SQL as e.g.

EXEC MyStoredProc 'paramter1', 'parameter2'

or the ODBC Direct syntax

{Call MyStoredProc('paramter1', 'parameter2')}

--