Pass Through Query Issue

[tkosel]

Sorry about the length of this question.

I am building a Pass Through Query to retrieve data from Linked SQL Database
Tables.

V_QueryString = "SELECT dbo.PRODUCT.PROD_ID, dbo.PRODUCT.PROD_NAME, . . . "
(I didn't include the entire String, but the string works fine. There are
some parameters being passed to the string based on user criteria.)

I then modify the Currently existing pass through query using:

CurrentDb.QueryDefs("RawDataPassThrough").SQL = V_QueryString

This query yields the results desired.

I am worried however, because I don't think good things will happen
when multiple users are using my shared application. (Bad when the query
is being used and someone else tries to also use it applying different
criteria

I could install a seperate front end on each client, but don't want
the update/maintenance problems.

I could build just the query on a local client machine and use it, but don't
know
how to do that. I know how to create a DB on the client machine;

Set dbNew = DBEngine.Workspaces(0).CreateDatabase(strDBName, dbLangGeneral)

And I know how to copy the object from current database to client;

DoCmd.CopyObject "C:\temp\file.mdb", "MasterRawDataPassThrough", acQuery,
"RawDataPassThrough"

But I don't know how to modify the "RawDataPassThrough" query on the client.
CurrentDb.QueryDefs("RawDataPassThrough").SQL = V_QueryString won't work,
but perhaps there is something else? I could then link local client query to
the master shared application and run the query from there once it is built.

Any ideas/suggestions?

 

[Tom van Stiphout]

On Fri, 19 Sep 2008 06:38:01 -0700, tkosel

First off, if you have attached tables, you don't need to use
Passthrough queries, and vice versa.

You should really give every user their own copy of the FE. Tony Toews
has an updater program that can remove the maintenance hassle.

-Tom.
Microsoft Access MVP

 

[tkosel]

Thanks for your prompt reply. I would like to be able to use regular
queries, that would eliminate my problem. I have been led to believe by
experts, that using a pass through query will be a lot faster. I have read
lots of documentation that explains why they are faster, and it makes sense
to me. I have also experienced this personally on my big SQL DB.

 

[John Spencer]

Sometimes pass through queries are faster and sometimes you can't detect
the difference between a pass-through and a query using linked tables.
It depends on the tables involved and the query.

I have queries against an MS-SQL table of 2 million records that return
at most 150 records from the table. I can't detect a difference in
response between a pass-through and an query against linked tables.

If you are going to use the scheme you have proposed I would suggest
that you use a separate copy of the front-end database application for
each user.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Duane Hookom]

Each user should have their own front-end. There are lots of easy ways to
accomplish and maintain this from Tony Toews updater at
http://www.granite.ab.ca/access/autofe.htm to just running a script that
copies a new front-end to the user's PC every time.

 

[Tom van Stiphout]

On Fri, 19 Sep 2008 07:13:01 -0700, tkosel

Performance is a YMMV issue. I just tried to point out that you have
options.

-Tom.

Thanks for your prompt reply. I would like to be able to use regular
queries, that would eliminate my problem. I have been led to believe by
experts, that using a pass through query will be a lot faster. I have read
lots of documentation that explains why they are faster, and it makes sense
to me. I have also experienced this personally on my big SQL DB.

<clip>

 

[Sylvain Lafontaine]

In your case, as you are changing the sql statement of your querydef, you
should use temporary querydef objects in order to limit the growth of your
database. For an example, take a look at the second example of code in the
article http://support.microsoft.com/kb/131534

Like other poeple have said, a better idea would be to make ordinary queries
against ODBC linked tables or views. The performance will be a little lower
but the difference will be small so you shouldn't notice any real
difference.

The use of passthrough queries should be reserved to thing that you cannot
make using ordinary queries against ODBC linked tables; for example calling
a stored procedure or doing a complex query with lot of subqueries and other
joins.