Unanswered: Linked ODBC SQL tables vs Pass-Through Querys
Can anyone tell me what is the diference between linked ODBC tables and pass-throw querys.
I think i've seen a thread some were in this or SQL Server forum, i'm not sure. But if it existes i appologies for being repetitive.
I know that with pass-through querys the table is processed in the server and not on the client's machine. But doesn't the linked ODBC SQL Server tables do the same? I mean, aren't them processed also in the server?
Not necessarily. MS Access will attempt to send a query involving linked tables as a pass-through query, but if it cannot then the query will be evaluated on the client side, leading to significant performance degredation.
Examples of statements that could not be sent as pass-through queries are queries involving both linked and local tables and those involving tables linked from different sources, but also some queries that may only involve linked tables but which may be too complex for Access to translate into a pass-through query.
When at all possible, code your queries on the database server as views, procedures, or functions, and then call them from Access. That is even preferable to pass-through queries, and makes administration of your application much easier. A thin client is always better, unless you are running a weight-loss clinic.
That is why I almost always create my small applications as Access Data Projects rather than .mdb files, and use SQL Server as the back-end.
If it's not practically useful, then it's practically useless.
I did not know that Access first attempts to convert a linked-table query to a pass-through query. From my experience with using both, I think it's risky to ever believe Access would do this on any worthwhile query (ie - a query with any predicate or criteria).
For instance, in the project I'm working on right now where we're changing many linked-table queries (on a network Access db) to pass-throughs against a SQL-server backend, even flat data dumps (SELECT * FROM table) seem significantly faster as passthroughs rather than linked-table queries. I'm going to test this later today, with a little form that pops up with the total processing time after each query.
But for any meaningful query - such as (SELECT * FROM table WHERE field = 'M'), the passthrough query should always be faster. Do you believe that Access actually converts JET SQL to SQL Server syntax prior to executing the corresponding linked-table query? That would be awesome, but I haven't found this to be the case.
I very much agree that stored procs are the way to go. I've always felt that it was a mistake to market Access as a database, when its true power is as a front-end for enterprise databases. Ideally, Access should only 'access' and present the data, not process it (I guess we would call it MSProcess03 if anything else were the case).