How to include a parameter in Pass Through Query (1 Viewer)

Harry_38 · Sep 3, 2012

Got an extensive Access program on my PC with a backend mysqldb with a provider. I know how to get a Pass Through Query running for a simple query but I get stuck with the following SPT query (SQL Pass_through):
SELECT Bankbook.Date, Bankbook.SeasonID, Bankbook.Data1, Bankbook.Data2
FROM Bankbook WHERE (Bankbook.SeasonID=GetParameterWaarde('strActiefSeason'))
The parameter (season) is set during the startup of the program.
What do I have to include in the query so that the query accepts the parameter?
I need a very detailed answer as I don't know much about SQL.
Thanks, Harry

mdlueck · Sep 5, 2012

First decision you need to make is if you need the query results in an Access FE table, or would VBA variables be acceptable.

If you need the results in a table, then a nested DAO.QueryDef will be the way to accomplish that.

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

If you just need the results in VBA variables, then an ADO.Command object would be the way to go. Those work with ADO.Parameters objects to pass in variables to the database to be plugged in at the correct places to the SQL query.

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Other than the connection being to an Access FE, this example would work.

For your use, you would need a different connection (ADO.Connection object) which I was recently helping describe here...

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259

Harry_38 · Sep 8, 2012

Thanks Michael for yr reply. The query result is used to make reports. A max of 1500 records if I can include the CourseYear as a parameter in the SPT else some 6000 records. I use a second query to further narrow down the selection for more specific records (e.g. to enter a course number or teacher); with the 2nd query I avoid the difficulty with including the parameter in SPT. Result so far: I improved the response time for a report of 50 pages from over 90 secs (Access queries) to 6 secs (SPT).

I would think that the result of query is best kept in the memory until the report is closed (most often the report is printed as pdf.) I will need some more time to study the link you sent me; all this stuff is new to me.

For your info: my program is used by a ' Volksuniversiteit' in The Netherlands; (volunteer work for a pensioner).
Regards, Harry

Harry_38 · Oct 6, 2012

Hello Michael and others,
I am afraid I need a bit more help; the links and 'Similar Threads' do not seem to fit my problem or, more likely, I am too dumb to understand.
When my program is opened, one can select a number of cases: For the year 2012, I chose 'Case 2012':
Case 2012
SetParameterValue "strActiveSeason", "12"
strvalue = GetParameterValue("strActiveSeason")
Hence the Seasonparamater is set at 12 and it is being used by a large number of queries that support reports and forms. Many of my reports take longer that 60 secs and that I find unacceptable. Please help me with a step by step procedure to incorporate that variable in my query.
Thank, Harry

mdlueck · Oct 8, 2012

Harry_38 said:
The query result is used to make reports.

In that case I think you best download records into an FE temp table (using the DAO.QueryDef example I linked to) and bind the report to the FE temp table.

I do not see the connection between needing to provide a parameter to the query and for that to make the query run a long time. Could you elaborate a bit in detail?

Harry_38 · Oct 8, 2012

It concerns a Course Administration program. Until now the SQL Backend dB has been running for 3 yrs; hence 3 years of data have been stored in the dB. At the opening screen of the program you select the academic year; that selection is stored as a variable. ( For the year 2012, I chose 2012-2013. and the string is set to 12) In the access queries in the field (or column) I enter as criteria: GetParameterValue("strActiveSeason"). The query then returns only data of the selected year.

You suggested to download the PTquery into a temp tbl. ; and then run a standard access query on that PC based table. Sounds ok to me; is that possible with a PT query involving in some cases 6-8 tables?
And how do I go about it?

mdlueck · Oct 8, 2012

Harry_38 said:
You suggested to download the PTquery into a temp tbl. ; and then run a standard access query on that PC based table.

No, run the Pass-Through query to obtain exactly the records you need for the report. Download exactly those records into the FE temp table. Bind the Report to the FE temp table... no additional queries required. Reports act sort of like a query. You are able to, for example, sort and group in Access Report objects.

Harry_38 · Oct 8, 2012

Thanks Michael,
Here is my reaction in blue:

mdlueck said:
No, run the Pass-Through query to obtain exactly the records you need for the report. To do that, I would need to include the year-parameter;
Download exactly those records into the FE temp table. I presume I would first have to create the FE table manually; that's no problem. (I would have difficulty in doing that in VB.
Bind the Report to the FE temp table... no additional queries required. Reports act sort of like a query. You are able to, for example, sort and group in Access Report objects.Ok, that';s no problem
[/QUOTE]

mdlueck · Oct 8, 2012

Harry_38 said:
To do that, I would need to include the year-parameter

Yes that is correct. And my sample code showed how to pass a parameter to queries. Oh... I am sorry... I actually did not provide a sample of this scenario

The way I used to do that with Pass-Through queries was to pass those variables inline in the SQL string being built. Here is an example of such passing in the dynamic name of the database table. (In your case, use the same sort of syntax, just put the syntax in the WHERE part of the SQL rather than in the FROM as my sample code has it:

Code:

  'Define the query
  strSQL = "SELECT [piw].[aid],[piw].[partnumber],[piw].[title],[piw].[qtyper],[piw].[oldqtyper],[piw].[addpartrecordflg],[piw].[doneflg]" & vbCrLf & _
           "[COLOR=Purple][B]FROM [" & Me.FETempTableName & "] AS [piw][/B][/COLOR]" & vbCrLf & _
           "ORDER BY [piw].[aid];"

.

It is just straight up VBA string concatenation syntax.

Harry_38 said:
I presume I would first have to create the FE table manually; that's no problem. (I would have difficulty in doing that in VB.

Yes I would leave the FE temp table static in the FE DB. Simply keep it emptied when you prepare a new FE DB to distribute to production machines.

That way you do not need to on-the-fly create indexes and set the Subdatasheet Name to None, etc...

Harry_38 · Oct 9, 2012

'Define the query
strSQL = "SELECT [piw].[aid],[piw].[partnumber],[piw].[title],[piw].[qtyper],[piw].[oldqtyper],[piw].[addpartrecordflg],[piw].[doneflg]" & vbCrLf & _
"FROM [" & Me.FETempTableName & "] AS [piw]" & vbCrLf & _
"ORDER BY [piw].[aid];"

Thanks again, Michael. Question: where do I insert the string with the 'Year-parameter'?

mdlueck · Oct 9, 2012

Harry_38 said:
Question: where do I insert the string with the 'Year-parameter'?

mdlueck said:
In your case, use the same sort of syntax, just put the syntax in the WHERE part of the SQL rather than in the FROM as my sample code has it:

For dates I believe it is sometimes necessary to wrap the date string inside of # characters. You might specifically search for how to do a WHERE date type query against your BE DB to verify syntax.

Harry_38 · Oct 29, 2012

Thanks for your effort Michael. My initial question was: 'What do I have to include in the query so that the query accepts the parameter?'and that question remains unanswered. I hope others can help me.

mdlueck · Oct 29, 2012

The query string needs to reference the Form field control's value in order to incorporate that dynamic value into the static SQL statement. The sort of syntax we have been highlighting is the way to concatenate a dynamic value of a field control with a static SQL statement.

Harry_38 · Jun 20, 2013

The reactions I received on this thread hasn't led to an acceptable solution. However eventually I found a way around it:
- I gave up the idea of automatically including the parameter in a PTQ.
- I removed the parameter entry that entered the course year in the query and replaced it with 'Submit parameter'. When i now enter the required course year the query reacts within 3 secs. which is acceptable.
Problem solved

How to include a parameter in Pass Through Query (1 Viewer)

Harry_38

Registered User.

mdlueck

Sr. Application Developer

Harry_38

Registered User.

Harry_38

Registered User.

mdlueck

Sr. Application Developer

Harry_38

Registered User.

mdlueck

Sr. Application Developer

Harry_38

Registered User.

mdlueck

Sr. Application Developer

Harry_38

Registered User.

mdlueck

Sr. Application Developer

Harry_38

Registered User.

mdlueck

Sr. Application Developer

Harry_38

Registered User.

Similar threads

Users who are viewing this thread