Refer to a form in a pass-through query

[Liz C]

I'm using a pass-through query to query data out on a network, but I want it
to find the records with a date equal to a date I have on an open form. It
works if I do it on a regular query of data in the database, but won't work
on this pass-through query. Is there some special way to refer to forms in
SQL?

Thanks in advance for your help!

Liz

 

[Tom Ellison]

Dear Liz:

A pass-thru query sends exactly what is coded to another database engine,
not to Access Jet. The methods of referring to a control on a form that are
available through Jet queries are not applicable. You would need to code
the generation of the SQL for this query so that it picks up the value of
the control(s) and includes them in the code sent to that other database.
The detials of that depend on the requirements of that other database. In
terms of such queries, it is expertise in that other database that counts
for the most.

Tom Ellison

 

[Liz C]

Thanks, Tom. That clarifies it a little, but I still am not sure how to work
around this.

What I did was copy the SQL from a query written in Impromptu over to the
SQL in Access. The Impromtu query used prompts to get the date range and I
don't know how to do that using the pass-through query. In Impromptu, the
SQL doesn't exist until the prompts are answered, and then it puts the values
in the SQL. If I don't use a date range, it pulls a really large number of
records through.

 

[Duane Hookom]

You need to modify the SQL property of the p-t query. Very simplistic, this
would be DAO code like:

CurrentDb.QueryDefs("sptYourQuery").SQL = "SELECT ... FROM... WHERE
DATEFIELD > '" & Me.txtDate & "'"

 

[Liz C]

I'm sorry - I'm not quite getting what you're saying. Here's the SQL..

select T1."org_id", T1."acct_period_cym", T1."prod_period_cym",
T1."svc_pkg_id", T1."meter_number", T1."tos_code", T2."production_date",
T2."dly_beg_dth", T2."dly_park_dth", T2."dly_lend_dth", T2."dly_adj_dth",
T2."dly_end_dth", {fn ABS(T2."dly_end_dth")} "ABS End Bal"
from "pkl"."dbo"."dly_pkl_activity" T2, "pkl"."dbo"."pkl_bal" T1
where T2."pkl_key" = T1."pkl_key"

I want acct_period_cym to be equal to Forms!DateF!AcctPer

Thanks for your help, Duane.

Liz

 

[Duane Hookom]

You code might look something like this. I have no idea if the
acct_period_cym is text or date or numeric or even how your server would
expect this to be formatted or delimited.

Dim strSQL as String
strSQL = 'select T1."org_id", T1."acct_period_cym", T1."prod_period_cym", '
& _
'T1."svc_pkg_id", T1."meter_number", T1."tos_code", T2."production_date",
' & _
'T2."dly_beg_dth", T2."dly_park_dth", T2."dly_lend_dth", T2."dly_adj_dth",
' & _
'T2."dly_end_dth", {fn ABS(T2."dly_end_dth")} "ABS End Bal" ' & _
'from "pkl"."dbo"."dly_pkl_activity" T2, "pkl"."dbo"."pkl_bal" T1 ' & _
'where T2."pkl_key" = T1."pkl_key" And acct_period_cym ='' ' & _
Forms!dateF!AcctPer & ''''

Currentdb.QueryDefs("qsptYourQueryName").SQL = strSQL

 

[Liz C]

Duane -

I'm still trying to make this work. I'm not very good at writing code, but
can usually wing it through. Can you please tell me exactly what should be
in the module? You were asking about the acct_period_cym field and it is a
date field. The name of the query is PKLQ.

Are there characters I'm supposed to ignore in your reply below?

Anyway - thanks for your continued help!

Liz

 

[Duane Hookom]

I don't know the syntax for whatever database engine you are querying. Can
you reply with your full p-t query with a "hard-coded" date in it so that we
know what format the date field requires?

 

[Liz C]

Here's the p-t query...

select T1."org_id", T1."acct_period_cym", T1."prod_period_cym",
T1."svc_pkg_id", T1."meter_number", T1."tos_code", T2."production_date",
T2."dly_beg_dth", T2."dly_park_dth", T2."dly_lend_dth", T2."dly_adj_dth",
T2."dly_end_dth", {fn ABS(T2."dly_end_dth")} "abs_end_bal"
from "pkl"."dbo"."dly_pkl_activity" T2, "pkl"."dbo"."pkl_bal" T1
where T2."pkl_key" = T1."pkl_key" and T1."acct_period_cym" <= '2006-04-01
00:00:00.000' and T1."prod_period_cym" = '2006-04-01 00:00:00.000'

Duane - you mentioned that I need to modify the SQL property of the p-t
query. Are you talking about a module?

Thanks. Liz

 

[Duane Hookom]

You would need code in a module something like:
Dim strSQL as String
strSQL = 'select T1."org_id", T1."acct_period_cym", ' & _
' T1."prod_period_cym", T1."svc_pkg_id", T1."meter_number",' & _
' T1."tos_code", T2."production_date", T2."dly_beg_dth", ' & _
' T2."dly_park_dth", T2."dly_lend_dth", T2."dly_adj_dth", ' & _
' T2."dly_end_dth", {fn ABS(T2."dly_end_dth")} "abs_end_bal" ' & _
' from "pkl"."dbo"."dly_pkl_activity" T2, "pkl"."dbo"."pkl_bal" T1 ' & _
' where T2."pkl_key" = T1."pkl_key" and T1."acct_period_cym" <= ' &
''' & Format(Forms!DateF!AcctPer,"yyyy-mm-dd") & _
''' and T1."prod_period_cym" = ''' & _
Format(Forms!DateF!AcctPer,"yyyy-mm-dd") & ''''

Currentdb.QueryDefs("PKLQ").SQL = strSQL

 

[Liz C]

Duane - Are there extra characters in the code you provided?

 

[Duane Hookom]

I don't think I put any any extra characters. I don't know the SQL syntax.

All you have to do is understand your required syntax and then change the
SQL property of your pass-through query to match the requirements and
including your values.