Pass throughs / UDF / Stored Proc / Views.. The differences in my upsize..

[munkee]

Hi all,

I am jumping in to the world of SQL server with an access front end. I am going to be converting an existing database over so we can use it across a wide area network.

I have been doing a lot of reading around stored procedures, user defined functions, views and pass through queries.

All of the information in isolation has been great and not so hard to understand at a glance but now I have come to the point I am trying to differentiate all of these options to come up with the best solution for my app.

For anyone who has done the conversion from a mdb to sql server what are the general situations when you would typically use each of the above options? I can see how a view is beneficial purely for narrowing down your large tables in to workable columns but what about the others?

Also would you be able to comment on their general performance? My database contains both in line sql statements within VBA which may require parameters, but it also contains standard access querydefs which may also require parameters.

Thanks for any help,

Chris

[NeoPa]

A View is pretty well a QueryDef. There are various reasons why you would define Views on the server instead of relying on the FE. Mainly performance and efficiency though.

A UDF is just that. A function. You may define one or two to give SQL Server the ability to do some of the things you're used to in Access. All server side though.

A Stored Procedure is a mixture of the two previous items in a way. Pre-compiled SQL just like a View, yet not limited simply to returning records. You may use these as equivalents to action queries.

Pass-Thrus are where you get to send T-SQL commands across directly to the server. Very flexible, but typically for use to handle things your FE (Access) doesn't support.

When using a Client / Server FE / BE structure, one of the main things to bear in mind is the minimising of data transfer across the link. These facilities can all help you do that in one way or another. Use them wisely.

[munkee]

Thanks for the input NeoPa.

If I want to optimise for usage over a wan how can I handle bound forms? At present my main form for updating records is bound to a table with a number of sub forms linking to other tables.

If I wanted to stay bound and also see performance improvement through the usage of sql server would I be looking to bind to the results of a stored procedure?

[NeoPa]

No Chris.

You can only bind the forms and their controls to local record sources (Tables or Queries). This isn't much of an issue though, as data sources bound to objects are not likely to be under much stress anyway. It's all about context. You need to consider the context.

[munkee]

Thank you once again I have been reading numerous articles again to get my head around all of this and it seems in essence I can probably break everything down in to pass through queries for all of my continuous data/searches, utilising views if necessary to reduce the number of joins being sent in the query if applicable. After my searches across multiple records are complete I can then drill in to specific records, only selecting one at a time to bring up a detailed view in a form with a record source to my linked tables.

These detailed forms contain a number of tabs but I will not allow these to load until a user specifically selects them so there is a reduction on in the information being pulled across the wire unnecessarily.

My question for this is when selecting the single record how will I drill down to this single view? At present i just use a filter on open to the specified id but i assume this willl end up just pulling everything down from the server and then filtering.I want to try and stay bound if possible for ease of editing.

Also how do you handle subforms?

[NeoPa]

I'm sorry Chris. You got that advice from elsewhere. It's not what I suggested. I don't feel I'm able to explain why some other advice makes sense if it does (My instinct is that it doesn't but don't quote me on that).