Pass thru Query for Form

[Nyrubi]

Can someone please help with this, I would like to use a pass thru query with a form. Is this possible? Is the syntax below correct? I took the sql from the current query copied it and created no query as pass thru query. However, not able to run. Can someone please review? Any help with this will be greatly appreciated. Thank you.



SELECT DISTINCTROW
BSH_ ID_NO,


IIf([MBH_SEX_CD]="M","Male","Female")AS Gender,
IIf([SRC_ID_CD]="001","Yes",
IIf([SRC_ID_CD]="002","Trans",
IIf([SRC_ID_CD]="004","Special","No"))))))AS INITIATED,
RODM.OPEN_DT,
RODM.CLOSED_DT,
IIf([DGNS].[ICDD_CD]<>" ",[CLSD_DT]-[OPN_DT],"") AS days,
cs_mgmt.Rprt_cd, " Cvrge Termed "& BSH_EX_DT AS Prognosis,
BSH_NM

FROM
(DGNS INNER JOIN
(RODM INNER JOIN
(SPME INNER JOIN BSH_ID_NO
ON SPME.ID_NO= MBSH_ID_NO)
ON RODM.ID_NO= SPME_ID_NO)
ON DGNS.ID_NO= RODM.ID_NO)
INNERJOIN mgmt_cse
ON STS_DISP_NM= cs_mgmt.[RSN_CD]

GROUP BY
BSH_ID_NO,
IIf([MBH_SEX_CD]="M","Male","Female"),
IIf([SRC_ID_CD]="001","Yes",
IIf([SRC_ID_CD]="002"," Trans",
IIf([SRC_ID_CD]="004","Special","No")))))),
RODM.OPEN_DT,
RODM.CLOSED_DT,
IIf([DGNS].[ICDD_CD]<>" ",[ CLOSED_DT]-[OPEN_DT],""),
DGNS.ICDD_CD,
DGNS.DESC,
cs_mgmt.CM_Rprt_Wrdg, "Plan CoverageTerminated " & ACUMBSD_EXP_DT,
GATV_ACUMBSH_V01.CUST_NM,

HAVING
(((BSH_CUST_NO)=[Forms]![frm_tstg]![txtCustomer])
AND ((RODM.ACT_DT)Between [Forms]![frm_tstg]![txtstartdt]
And[Forms]![frm_tstg]![txtenddt])
AND ((BSH_EXP_DT)<>#12/31/9999#));

[pbaldy]

Pass through to what? There may be syntax differences. Form references will not work, as the engine won't know how to interpret it.

[Nyrubi]

Sorry don't understand your question Pass through to what? I had the query referenced in design view. I used this query when using Form. However my form takes very long to open and someone suggested that I use a Pass through query to minimize the time it takes to open form and filter which is what I provided. I am asking if you can use Pass through queries when using a form? If so, is the query's syntax correct? Thank you.

[pbaldy]

In the navigation pane, does the query have the normal icon or a little globe? A pass through query would have the globe. A pass through query is not processed by Access, it's processed by the database engine it is pointed to (which is what my question was about).

[Nyrubi]

Hi thank you so much for responding. Yes the icon on the query has a little globe. The db is ODBC. Can this type of query be used by Form? If so can you help on the syntax of the query?

Thank you

[pbaldy]

ODBC isn't a db, it's a connection. Is the db SQL Server, Oracle, etc? The SQL has to be in the syntax expected by the db.

[pbaldy]

The connection string property of the query will help if you're not sure.

[Nyrubi]

Hi this is what I have for ODBC Connect Str: DSN=(name of table);UID=(Password);MODE= SHARE;DBALIAS=DB. I hope this is what you are asking. IF not can you please tell me where to find? thank you .

[pbaldy]

I was hoping for one like this, which includes the db, in my case SQL Server:

ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=ServerName;Trusted_Connection=Yes;DATA BASE=LimoTS

Is there somebody in IT that may know what database you use? The syntax of your query must be appropriate to that engine. It isn't appropriate for SQL Server, not sure about others.

[Nyrubi]

Hi it is SQL Server.

[aytee111]

That looks like an Access query, not from SQL Server. Before worrying about a form, get the query working in Access. But before even that, get the query working in SQL Server.

So:
1 - get the query working in SQL Server
2 - copy and paste the SQL to Access
3 - run the query in Access, make sure it works

Then you can move on to how to use it in Access. It is usually not a good idea to use pass-thru queries as record source for forms, reports, etc. Rather create a make-table query and use the local table, it will make it much faster and easier for the user. Your make-table query will be a "SELECT * FROM pass-thru query ....INTO ..... "

[pbaldy]

You'll need to convert to T-SQL then. Most of it is fine, but the double quotes need to be singles and as mentioned you can't include form references. Only later versions of SQL Server recognize IIf() function; if yours is an older version, you'll need to use CASE. I'd start with something simple, then start adding to it. That way you're only debugging one thing at a time.

[Nyrubi]

Hi, okay I can clean it up and use CASE. If pass through query can't reference forms, what do you recommend?

[pbaldy]

I build SQL in VBA and concatenate the form references into the SQL, then pass that to a function that modifies the pass through query. Search on "pass through fix up" and you'll probably find something similar.

[Nyrubi]

Hi, can you please provide sample how to form reference in SQL? Thank you.