passing parameters to a subquery in Access db 1

[Jaco]

Hello,

I have a SQL statement which is based on two grouping subqueries which are defined and saved in an Access database.
e.g.

Select subquery1.SumOfSomefield, subquery2.SumOfSomefield, ..., form subquery1 inner join subquery2 on...

My problem is that I need to pass criteria parameters to those subqueries from code (e.g. to see data from a given period of time)
Could anyone help me?

Thanks in advance

Jaco

 

[MissyEd]

Are you passing the paramaters from VB and could you post a sample of your code ? Missy Ed - Bolton, UK

 

[Jaco]

Yes I'm trying to pass this parameters from code but I have no idea how to do it. If there were just a single query there would me no problem at all but the problem is that I have to use parametrized queries in a "main" query
i.e my main query text looks like this:

Select subquery1.SumOfSomefield, subquery2.SumOfSomefield, ..., form subquery1 inner join subquery2 on...

subquery1 and subquery2 are grouping queries defined in an Access db and I have to pass criteria parameters to those subqueries and then create a recordset based on the main query.

Jaco

 

[bjd4jc]

Here is some code that may help you. It uses ADO

Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

con.Provider = "Microsoft.Jet.OLEDB.4.0"

con.Open "D:\Documents And Settings\Bdenning\My Documents\db3.mdb"

With cmd
.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "Query2"


'Add As Many Parameters as you need
'For Access, the names here don't have to match the actual parameter name

.Parameters.Append .CreateParameter("MyDept", adInteger, adParamInput, 4, 450)


'Open the recordset here,,, there are other ways of doing this

Set rs = .Execute
Debug.Print rs.EOF
End With

 

[CCLINT]

>"Yes I'm trying to pass this parameters from code but I have no idea how to do it"

Jaco: You asked this same question in the Database forum under:

thread709-367902

You should reference the old thread when starting a new thread in another forum, in your efforts to get additional help.

Then people who post answers know what has already been posted and aren't making uneeded efforts by duplicating the same answers, or can build their answers upon the posts already given.

I gave you a first response yesterday, and you have stated that you still cannot figure it out.
Today I would have gotten back to you.
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Jaco]

Thank you for your answer, bjd4c but I'm afraid it doesn't solve my problem. Your code shows how to open a parametrized Access query (in your code "Query2&quot but I need to to use this "Query2" in another query (let's say "main query&quot and build a recordset based on the "main query".
It doesn't make any difference if I make a command based on the Query2 and pass parameters to it. I can execute the Query2 but I can't use it in my main query. Thanks for help anyway.

CCLint, you're right. Next time I will do so. I was hesitating to start a new thread in this forum (normally I don't do it) but the problem is a bit urgent for me and it seems there are more readers in this forum (more posts for sure) than in the Database forum. Sorry for the inconvenience.


Jaco

 

[bjd4jc]

Jaco -

My queries do what you want.

In My query2 I am selecting * FROM Query1

In Query1 I am selecting * from Table WHERE DEPT=MyDept

Then I open the recordset using query2. I should have posted the queries for you. Here they are:

Query1

SELECT *
FROM EMPREC
WHERE DEPT=MyDept;

Query2

SELECT *
FROM Query1;

My Parameter actually resides in Query1.
Sorry for the confusion.

 

[CCLINT]

Jaco: Then we'll try to get you an answer as soon as possible.

Questions:
Query1 is a parametrized Access query?
Query2 is a parametrized Access query?

Sql statement in VB should create a recordset based on both?

SELECT AccessParameterQuery1.FieldX, AccessParameterQuery2.FieldY
From AccessParameterQuery1, AccessParameterQuery2

Is this how you want it? [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Jaco]

CCLINT that's exactly what I want.

Jaco

 

[Jaco]

It works! Thank you for the tip, bjd4c. I just pass parameters to my main query and it works although the query itself isn't a parametrized query.
I only had to change the CommandType to adCmdTable due to an ADO error described here

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q225918&

Jaco