Stored procedures with .mdb type app

Question

Stored procedures with .mdb type app

smknox

Hall of Fame

Points: 3275
More actions
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

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

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

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 1

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..

john cliffe Grasshopper Points: 16 More actions · Answer 2

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.

john cliffe Grasshopper Points: 16 More actions · Answer 3

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!

smknox Hall of Fame Points: 3275 More actions · Answer 4

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