Stored Proecedure VS Linked Table Question

[hokiewalrus]

I'm using SQL linked tables in my Access program and just came across an interesting statement in this old thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=44095

I normally link my ODBC tables and so don't need to use pass-through queries.

Now this is my first time working with SQL server in any meaningful way, so I may be missing something, but what do linked tables have to do with pass-through queries?

And, more importantly, have I been bashing my head against the wall trying to figure out stored procedures for nothing?

 

[Banana]

Well, I say everything has their place.

There are some people who thinks that the proper way to handle ODBC queries is to use unbound forms, passthrough queries executing stored procedures and so forth. While that's one way of that, I would argue that this basically defeats the point of using Access in first place. Access's strength lies in bound form, and we can in fact use linked tables as recordsource to good effect. Furthermore, a properly written application would perform just as well as the unbound form & passthrough queries. The key is in how we use this.

This FAQ discuss the nuances of binding ODBC sources to form.

This is not to mean we can't use stored procedure. As matter of fact, it is perfectly possible to bind a form to the stored procedure's resultset but the catch is that you have to use ADO and VBA to achieve this.

But regardless of what you do, it's significant to remember that for most cases, forms are made with intent to be able to manipulate the data source in some fashion. This then puts some requirements on what kind of recordset we can bind the forms and still maintain updatability.

So, there may be cases where a passthrough query and stored procedure makes more sense, perhaps in a context of batch processing or to perform resource intensive performance on server's side, but at the end of day, we are supposed to deliver something to client what they need. Given a choice between a bound form and a lot of VBA & SQL code, it's usually easy to choose bound form over the latter.

This is what make Access great; you have the freedom to choose and adapt for each cases.


I hope this helps.

 

[pbaldy]

If the tables are linked, you can simply use Access queries against them. JET is actually pretty good at having the server process them anyway, though a pass-through obviously guarantees that. If the Access query includes something the server can't understand (IIf function for instance), the server will have to pass the data back over the wire for JET to handle it. I use Access against SQL Server most of the time, and a majority of my queries are probably Access queries.

 

[hokiewalrus]

Thanks for the replies and the FAQ, both have been very helpful.

 

[ions]

Hokiewalrush,

I have also been researching this topic.

Access MVPs correct me if any of my statements are wrong.

<<Now this is my first time working with SQL server in any meaningful way, so I may be missing something, but what do linked tables have to do with pass-through queries?>>



When using ODBC Linked Tables to SQL Server, queries designed in Access have to be translated for SQL Server. This translation also involves optimization and is done for you (not sure if done by JET, ODBC Driver Manager or both)

To bypass this translation / optimization you can send a Pass-Through Query to SQL Server. By making a pass-through query you know exactly what you are sending to SQL Server. With a regular Access query you cannot be certain what is being sent.

The danger of Pass-trough queries is that if there is any syntax in the query that SQL Server doesn't understand (but Access may have understood it) you will receive an error.

Also pass-through queries are usually read-only and cannot be updated.




<<And, more importantly, have I been bashing my head against the wall trying to figure out stored procedures for nothing?>>


As mentioned by Banana, stored procedures are called by ADO and can be updated. Stored Procedures are built in SQL Server, not in Access, using SSMS (SQL Server Management Studio) ****

I want the opinion of the MVPs on this statement, “If you know how to write and call stored procedures there is no need to write pass-through queries because stored procedures can do everything a pass-through can and more.”



**** You can build stored procedures in an Access project (.adp). There is a mixed opinion of ADP’s in the Access community and Microsoft does not support ADP’s with SQL Server 2008.


I am interested in confirming that my statements are true.

Thanks for any clarifications

Peter.

 

[LPurvis]

Hi

I'm sure those other than MVPs may have opinions to offer...
None the less...

>> When using ODBC Linked Tables to SQL Server, queries designed in Access have to be translated for SQL Server. This translation also involves optimization and is done for you (not sure if done by JET, ODBC Driver Manager or both)

Kinda. Optimisation is a database engine consideration and is performed, if possible, regardless of the means of query execution. The translation occurs first with the local engine using the ODBC specifications to "interpret" the statement as closely as possible. As Paul said - that which can't be will be reslved locally when the results are returned. (It's not an all or nothing affair).


>> To bypass this translation / optimization you can send a Pass-Through Query to SQL Server. By making a pass-through query you know exactly what you are sending to SQL Server. With a regular Access query you cannot be certain what is being sent.

You can be pretty darn sure of what's being sent with well formed queries. (You can be 100% sure of you carry out a trace the execution).
Just to reiterate, the passthrough alleviates the Jet wrapping (which includes syntax translation into ODBC neutral).


>> The danger of Pass-trough queries is that if there is any syntax in the query that SQL Server doesn't understand (but Access may have understood it) you will receive an error.

That's not really a danger though. Your query will, naturally, fail the first time you run it if it's incorrect syntax. Access will invoke no parsing on it at all as you create it. As far as its concerned it's a black box of which only the result are of interest. (That's the state of passthroughs as they stand).
It's more a caveat that you need to be familar with the syntax of the target server.


>> Also pass-through queries are usually read-only and cannot be updated.

They are, by definition read only. Hence can never be updated. It's part of the reason why they're so efficient.


>> As mentioned by Banana, stored procedures are called by ADO and can be updated. Stored Procedures are built in SQL Server, not in Access, using SSMS (SQL Server Management Studio) ****

SP's can be called by any technology capable of accessing the server directly.
This includes ADO, DAO ODBC Direct (RIP ;-) and Access Passthroughs.
You can build them using anything which can access the directly - i.e. any of those as well as SSMS.
ADPs offer a UI to facilitate this - but only with versions of SQL Server equal or older to that of the Access installation.
It is the results of the SP which can be updated - but that's not a given and is dependent on the method used to execute them.


>> “If you know how to write and call stored procedures there is no need to write pass-through queries because stored procedures can do everything a pass-through can and more.”

Not necessarily. Passthroughs are entirely capable of calling stored procedures.
They are not mutuall exclusive concepts.
Indeed some of the most common use of PT's is in the execution of SPs.
If you're thinking of ah hoc querying - there is benefit if you don't have design permission on the server, or want to be able to write dynamic SQL (adapting the definition of the PT at runtime in Access before execution).
In generaly you'd use PT's in preference to ad hoc querying (for subtle performance gains etc) but there are reasons for each.
The T-SQL you write in an SP does have greater scope for functionality than that executed at runtime in an SP. But for single shot querying - there's not all that much difference.


>> You can build stored procedures in an Access project (.adp). There is a mixed opinion of ADP’s in the Access community and Microsoft does not support ADP’s with SQL Server 2008.

It's not that ADPs are bad. They've good technology.
It's just a question if they offer enough to make them the implementation of choice.
Advantages like direct binding to SP's are repleat with caveats - such that you need write permissions on the underlying tables to update (as the way ADPs work mean an update is executed by the form upon commital using those base tables).
Forms and Reports have the InputParameter property to provide parameters for SP's by design refering to Access objects (which normally involves coding otherwise).
You need to be familiar with ADO to use an ADO effectively really.
But Every version of SQL Server is supported by ADPs of any version.
That SQL 2008 doesn't expose a design interface to Access (until 2010) doens't mean its data isn't accessible.

Cheers.

 

[ions]

Thanks LPurvis

I want to confirm. If I want to run a SP and I don't need to to bind the recordset to a form I have the option of calling the SP using a PT.

If I want the the results of the SP bound to a form I need to call the SP using ADO or DAO. (Note: In my Access 2007 book, it states that ODBC Direct is not recommended instead use ADO. )

Access 2010 Projects will be able to design SQL 2008 objects? Is Microsoft reviving ADP? I heard it was dying.

 

[Banana]

Leigh's point was that you can use ADO, DAO, passthrough query and/or pony express to call SP. You can bind the form to ADO recordset, DAO recordset or even passthrough query.

Think of those as means of implementations. What really matters is whether they are the correct tools for your specific needs. For one case, passthrough query may be good enough, but for other case, we need ADO for updatability. It really depends more on your specific needs at the moment.

As for ADP, I think someone mentioned that it isn't 'dead'. Yes, Microsoft has recommended using .mdb, but they haven't killed it off for whatever reason. Furthermore, you may recall one other poster, Bob McCellan stating success with his ADP - SS 2005... it works well because he uses ADP as his front-end client and does all changes in SSMS rather than in Access.

 

[LPurvis]

Again with the quoting for clarity.. (hopefully)
(Some already addressed by Banana).

>> If I want to run a SP and I don't need to to bind the recordset to a form I have the option of calling the SP using a PT.

Binding to a PT is entirely possible - but the result is that the recordset is inevitably read only. If you want an updatable form you'll need to employ binding through another means or no binding at all.
Using ADO or DAO you could execute the SP and bind a recordset from that to the form for direct updatability.


>> If I want the the results of the SP bound to a form I need to call the SP using ADO or DAO. (Note: In my Access 2007 book, it states that ODBC Direct is not recommended instead use ADO. )

As above really. Yes ODBC Direct has fallen by the wayside with Access 2007 (hence my RIP comment earlier). But it did used to be a viable option (and still is in earlier versions).
I personally would never use it instead of ADO in an application. But it's another option (ish).


>> Access 2010 Projects will be able to design SQL 2008 objects? Is Microsoft reviving ADP? I heard it was dying.

Yes it'll be able to use the object designers. But as far as dying... there's nothing to be revived. They've not been deprecated.
It's a misreported trend. MS are (as Banana says) encouraging ACCDB with linked tables as their preferred solution. Yes that might be for a future where ADPs do become deprecated (everything will be one day). But they're as present in Access as ever.
They're not as visible in 2007 onwards, but they remain as was.
MS simply haven't added to their feature set. But they do have to make substantial investments to continue to support ADPs in each new version of Access. So it can't be said that they're even letting them die off.

I think Banana's comment of
>> Think of those as means of implementations
is important to really take on board. It's the crux of what I was getting at.
You have server objects and you use those server objects or you don't. You use them and perform ad hoc querying by many of the same means. (Except linked tables ;-)

FWIW I've always advocated not worrying about the object designer in ADPs.
For me SSMS offers so many more tools that it only makes sense to use it.
The ADP still connects to the data - and that's what's important... in a database after all. ;-)

Cheers.

 

[ions]

Thanks to you both Banana and LPurvis.

One final question please. Do you recommend working with SQL Server Express 2005 or 2008 Express? My current need is for learning purposes. I want to learn how to use SSMS.

Is SQL Server 2008 Express stable enough? I haven't heard of anyone using it. Which do you recommend for a rookie learning SSMS?

Thank you

Peter.

 

[LPurvis]

I only have 2005 Developer installed on my development machine (but I have had 2005 Express installed previously for some time - to see what it offered). 2008 SSMS should give you better performance (if I've had one grumble with SSMS since it came out it's the relative slowness of the application). There are several other improvements too - though I don't know if they'll help you for learning purposes (intellisense could be considered a mixed blessing in that sense ;-)

I'd suggest you use whichever you're likely to work with for real. (e.g. in your place of work - whatever they have their).
Few things are as annoying as being familar with an application but being unable to use functionality you now take for granted because you're having to work on an older version.

Cheers.

 

[ions]

Thanks LPurvis