Post reply

Stored procedures with .mdb type app

  • February 2, 2004 at 5:12 pm

    #76121

    Novice question: I have SQL 2k as a backend with Access frontend and would like to use stored procedures to populate forms in order to decrease network traffic (prefer to send fewer records across network). I prefer .mdb apps to Access projects (.adp).

    I could write pass-through queries (and have some) but those are not updateable on the frontend. Is my best (maybe only) option to connect through an ADO connection to the DB to access a stored procedure?

    THanks,

    SMK

  • February 3, 2004 at 3:14 am

    #492583

    I have been using ADO for some time against procedures and am not aware of any other method for updating. All my ADO is based on the examples in Microsoft Access Developer's Guide to SQL Server published by SAMS..

  • February 4, 2004 at 4:07 am

    #492724

    I am a newbie to SQL Server, but had the same problem from one of my customers. He has no option but to use MDB front end.

  • February 4, 2004 at 4:11 am

    #492725

    I am a newbie to SQL Server, but had the same problem from one of my customers. He has no option but to use MDB front end. The solution was to create a view rather than a stored procedure. This can then be treated as a table in the access front end. Using file > get external data > link, link to this view and it will be treated as a table in access. Remember all the conditions required for updateable views in SQL Server! Indexes, schemabinding, updating one table only, all the relevant SET options ON etc. Don’t know what went wrong with original post!

  • February 4, 2004 at 5:48 am

    #492734

    Thought others might benefit from Mary Chipman's comments (from newsgroup):

    An mdb is a better choice since you also have the flexibility of

    storing static data locally in Jet tables, allowing you to decrease

    round trips across the network to fetch data that rarely changes.

    You can't update the results of a pass-through query, and that is the

    only way to populate a form from a stored procedure in an mdb.

    however, you can create unbound forms, populating the controls

    manually and performing any updates in code by passing parameter

    values to stored procedures. This involves writing a fair amount of

    code, which you may not want to do.

    If your main concern is network traffic, you can achieve a good result

    by not binding editable forms to entire tables and still have bound

    forms without the hassle of an unbound app. Create a "query by form"

    interface that forces uses to only load a record at a time and set the

    form's recordsource property to load the record with a WHERE clause

    that only loads a single row (and its related rows in a subform) for

    editing. Base browse forms, combo boxes,  and reports on stored

    procedures called through pass-through queries. Perform all

    aggregations in stored procedures, and any formatting or presentation

    code in Access.

    -- Mary

    Microsoft Access Developer's Guide to SQL Server

    http://www.amazon.com/exec/obidos/ASIN/0672319446

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply