passing parameters to an Access subquery 3

[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

 

[CCLINT]

I haven't tried this with an Access stored query, but maybe the following will work:

Normally you would use the command object when using parameters.
With the command object and using a Jet MDB you can set the command text to a sql statement with defined parameters:

Dim param1 As ADODB.Parameter
Dim Comm1 As ADODB.Command
dim rs As ADODB.Recordset

With Comm1
.CommandText = "PARAMETERS [Lower Bound] Long;" _
& "SELECT Field1, Field2 " _
& "FROM myTable " _
& "WHERE Field1 = [Lower Bound]"

.CommandType = adCmdtext
End With

Set param1 = comm1.CreateParameter("[Lower Bound]", adLong, adParamInput)
Comm1.Parameters.Append param1
Param1.Value = 10
Comm1.Execute

Set rs = New ADODB.Recordset
rs.Open Comm1

You may want to change your ACCESS query into a Parameter Query (In the Query designer build the sql like the above CommandText - use the Parameter names with brackets in the criteria section)
Then change the command text to just the query name and change the parameter CommandType to adCmdTable. The rest remains the same.

As I said, I am not sure if this will work this way on an ACCESS query. You might just want to copy the query sql statement that is in the Query designer under VIEW|SQL and use it here in the command text - doing away with the Access query.
If the query is being ran more than once in the same session, you can use the property "Prepared" to have the command text compiled first, and then succeeding calls will run faster.

I hope there were not any typos here - have a look in MSD´N or in your VB help from correct syntax and further advice. [/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]

Thanks for your answer but I'm afraid I didn't make myself clear. The problem is that I have to use parametrized queries in "main" query (it's crutial unfortunately) and that's the problem.
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.
The only way I see now is to create temporary tables which I could use in the main query.


Jaco

 

[CCLINT]

Ok Jaco. I tested this (the above code that I posted) creating a Select statement from 2 Access parameter queries and it worked just fine.

Please show me your select statement and I will write the necessary code for you (and let me know the names of the parameters) [/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]

Yes for me it also works..., I didn't know that passing parameters to a non-parameter query based on parameter queries will work until I tried to do this Thanks for help.

Jaco

 

[carry1]

can one of you guys show me the actual working code that does this?

and can it be done with something like "exec [qry] p1, p2 ..." (to the main query that selects from a query with parameters? i can't get it to work

 

[Jaco]

Dim rs As New ADODB.Recordset
Dim cmnd As New ADODB.Command
Dim cnn As ADODB.Connection

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=database.mdb"
cnn.Open
With cmnd
.ActiveConnection = cnnPolaczenieUst
.CommandText = "QueryName"
.CommandType = adCmdTable 'it has to be set to adCmdTable not adCmdStoredProc (although it's not actually a table) due to some ADO or VB error
.Parameters.Append .CreateParameter("date1", adDBTimeStamp, adParamInput, , "02/27/2002&quot
.Parameters.Append .CreateParameter("date2", adDBTimeStamp, adParamInput, , "02/28/2002&quot
Set rs = .Execute()
End With


Good Luck

Jaco

 

[rschast]

I get a "object variable or With block variable not set" error in the fillowing code at the .CommandText line. Any ideas? Thanks!
/Ryan
---------------------------------
Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter
Dim param3 As ADODB.Parameter
Dim Cmd As ADODB.Command
Dim rsReports As New ADODB.Recordset

With Cmd
.CommandText = "qryGetInstructorEmail"
.CommandType = adCmdTable
End With

Set param1 = Cmd.CreateParameter("[ChooseGroup:]", adChar, adParamInput)
Cmd.Parameters.Append param1
param1.Value = Forms!frmEOCSurveyReport![ChooseGroup]
Set param2 = Cmd.CreateParameter("[ChooseCourse:]", adChar, adParamInput)
Cmd.Parameters.Append param2
param2.Value = Forms!frmEOCSurveyReport![ChooseCourse]
Set param3 = Cmd.CreateParameter("[ChooseDiscipine:]", adChar, adParamInput)
Cmd.Parameters.Append param3
param3.Value = Forms!frmEOCSurveyReport![ChooseDiscipline]

Cmd.Execute

Set rsReports = New ADODB.Recordset
rsReports.Open Cmd
-------------------------------------------

 

[rschast]

Okay, I fixed the problem by adding the set Cmd line. Now I have a "Parameter object is improperly defined. Inconsistent or incomplete information was provided.

Thanks for your help.
---------------------------------------------------------

Dim param1 As ADODB.Parameter
Dim param2 As ADODB.Parameter
Dim param3 As ADODB.Parameter
Dim conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim rsReports As New ADODB.Recordset

Set conn = CurrentProject.Connection
Set Cmd = New ADODB.Command

With Cmd
.CommandText = "qryGetInstructorEmail"
.CommandType = adCmdTable

End With

Set param1 = Cmd.CreateParameter("[ChooseGroup:]", adChar, adParamInput)
Cmd.Parameters.Append param1
param1.Value = Forms!frmEOCSurveyReport![ChooseGroup]
Set param2 = Cmd.CreateParameter("[ChooseCourse:]", adChar, adParamInput)
Cmd.Parameters.Append param2
param2.Value = Forms!frmEOCSurveyReport![ChooseCourse]
Set param3 = Cmd.CreateParameter("[ChooseDiscipline:]", adChar, adParamInput)
Cmd.Parameters.Append param3
param3.Value = Forms!frmEOCSurveyReport![ChooseDiscipline]

Cmd.Execute

Set rsReports = New ADODB.Recordset
rsReports.Open Cmd
End Sub

 

[CCLINT]

Please see MS Kb article Q181716 [/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!

 

[DarthMantis]

Hi CCLINT

i have this problem where i can only use parameters thru the CommandType adCmdStoredProc, and not thru adCmdText, i keep getting an errormessage saying its expecting 4 parameters. I specified parameters both ways, with the parameters collection and with the Parameters argument in the Execute method and both wont work. As a matter of fact the only way i can get parameters thru is with Stored Procedures and the Parameters argument. But in this case, i need to call a SQL statement with a SHAPE language sentence, which can only be reconized with the adCmdText command type, so my problem is that. i need that SHAPE-SQL sentence to work and receive the parameters, and there is no way i can get rid of my parameters because they are deep inside database (i have one mother SQL procedure that puts together other statements which contain the parameters)
thanx