0

this is more conceptual. I have a query I currently run in SQLServer that I'd like to make into a passthrough query in Access. However, it's a monster query that must be limited by a WHERE clause with the ID#s I want (there are too many ID#s to not have the WHERE clause; it'll freeze up). The ID#s I need change everyday, so I can't set this passthrough up once and be done.

So I don't really know how to get it so the user either 1. imports a table with the ID#s for that day and that table links to the passthrough, or 2. the user pastes in the ID#s into an input box and those become the WHERE clause conditions.

As far as I can see I can't JOIN the table in Access with the ID#s to the Passthrough, and I can't use WHERE ID# = [table].[ID#] in the WHERE part of my passthrough. Taking out the WHERE in the passthrough and then making a simple select query where it is joined with the access table doesn't work either, because the query just takes so long it times out.

Is this even possible or should I explore completely different strategies?

msim
  • 353
  • 7
  • 25
  • Why not keep everything on SQL Server side? Don't import the table of IDs locally but keep it externally and then run pass-through query with joins of both external tables. Also, recall WHEREs are implicit JOINs. So setting up IDs in where clause or as a joined table are equivalent processes according to the query optimizer. See this lively [discussion](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause). – Parfait May 24 '15 at 02:26

2 Answers2

0

Build the query in SqlServer as a stored procedure that returns data and accepts paramenters. Build a form in which you request the ID from the users, or build a function that get those IDs if possible. Have the paramenters in the stored procedure be the IDs you need in the WHERE clause.

You can dinamically redefine the SQL of a query by using querydefs like

CurrentDb.QueryDefs("myAccessQueryPassT").sql = 
     "EXEC mySqlServerStoredProc (" & par1 & ", " & par2 & ")"

where par1, par2, ... par(n) are the IDs you want to filter.

Then you can run a msAccess query as usual:

SELECT * FROM myAccessQueryPassT

Note that you can also define an unfiltered view in SqlServer (mySqlServerView) and then in the passthrough (in the example I include only one parameter, you can include more of course):

CurrentDb.QueryDefs("myAccessQueryPassT").sql = 
"SELECT * 
FROM mySqlServerView 
WHERE ID = " & par1 & ")"

Or course you can always build the entire query dinamically, in that case it will be like:

CurrentDb.QueryDefs("myAccessQueryPassT").sql = "SELECT ... FROM ... WHERE..."

Same as before you can query the passthrough:

SELECT * FROM myAccessQueryPassT
Manuel Castro
  • 138
  • 6
0

I use another technique when i have strange pass through queries
First i create a dummy pass through query where everything that is subjective are represented by dummy constants
e.g if i have changing of year i have a dummy year 1900 and i use replace to change it to whatever i need
The changed SQL is then passed to another pass through which serves as the
So my code is like this 

Const DummyToReplace = "1900"
Dim strSql as String
Dim qdf as QueryDef
strsql = vbnullstring
set qdf = currentdb.QueryDefs(SourceDummyPassThroughQry)
strsql = qdf.SQL
set qdf = nothing
strsql = replace(strSql,DummyToReplace ,TheCriteriaYouwantToPass)
set qdf = currentdb.QueryDefs(ModifiedPassThrough)
qdf.SQL = strsql
set qdf=nothing

At this point you can either execute the ModifiedPassThrough or use it as a RecordSource. In the case of WHERE you can use string splitting to split the SQL to 2 parts
1st part up to WHERE e.g 

 strSql = SELECT * from my TABLE WHERE

2nd part just put the Where Criteria

  WhereCriteria = " ID IN (Various IDs)"

so you concatenate then and feed them to the modified Pass Through

qdf.SQL = strsql & " " & WhereCriteria

The rest are easy

John
  • 974
  • 8
  • 16
  • I'd like to try this but I'm wondering, let's say my IDs are in a column in a table, like row 1 = 100, row 2 = 101, row 3 = 102 etc. How would I convert these values to a string that could be stored in a variable, to be then used in the where criteria string variable? – msim May 26 '15 at 17:07
  • WhereCriteriawill be modified like this `WhereCriteria = " ID IN (select IDs from IdTable)"` – John May 26 '15 at 17:10
  • I'm getting `Item not found in this collection` on the `set qdf = currentdb.QueryDefs(SourceDummyPassThroughQry)` step. I changed SourceDummyPassThroughQry to the name of my original passthrough. I'm also wondering if I can do the replace to replace my WHERE criteria from "Dummy" to "(select IDs from IDTable)", and then just run the passthrough query. Am I not understanding it? – msim May 26 '15 at 19:22
  • replace the `SourceDummyPassThroughQry` with the name of your passthrough....remember that you will have 2 pass through 1 source and 1 that everytime has SQL the modified SQL of source – John May 26 '15 at 19:32
  • I did, I wrote `Set qdf = CurrentDb.QueryDefs(qry_PT_Test_Before)` – msim May 26 '15 at 19:40