Updating Stored Procedures

[Shawn Repphan]

I am using a 2003 ADP to connect to a SQL Server 2000 database using sprocs.
I am having trouble updating sprocs when two or more tables are involved.
Apparently SQL server has no problem updating a sproc that selects from only
one table, but it cannot produce the logic to update more than one table on
its own. How would I create a single sproc that could select, update, and
insert data into multiple tables AND have Access be able to use bound forms
and controls to handle the updates? I cannot use raw ADO in this application
for reasons beyond my control.

 

[[MVP] S.Clark]

I don't know of a SQL command that can update two tables at once.

But in the stp, you can add as many update statements as you like.

e.g.
UPDATE table1 SET fieldname = "something" ...

UPDATE table2 SET fieldname = "something"...

SELECT * from T1, T2 Where T1.field=T2.field

 

[Shawn Repphan]

That is no problem. I could easily do this in ADO.NET, but for this project
I have to do it in Access using bound controls. If I create a sproc that
selects and another that updates, how does Access know which one to use for
selects and which for updates?

 

[Shawn Repphan]

Basically, my problem is: I must use bound forms and controls in this
project. Because I am running large queries over a slow WAN link I would
like to have SQL Server do all the processing. What is the best way to
accomplish this?

 

[Ron Hinds]

By not using bound forms and controls - at least, not bound to SQL Server
tables. The most efficient method for what you are trying to accomplish
would be to use DAO rather than ADO, and create some local temp tables to
bind the forms and controls to. Then use SQL Pass-Through queries to
populate the local temp tables and to perform the updates, etc. You can set
the SQL property of Pass-Through queries to be stored procedures, etc. This
will minimize traffic over the slow WAN link. With forms bound directly to
the SQL Server tables you will create a huge amount of network traffic.
Check out the book Microsoft Access Developer's Guide to SQL Server by
Chipman and Baron - it has all sorts of useful tips and techniques for
optimizing Access - SQL Server interaction.

 

[Br@dley]

That is no problem. I could easily do this in ADO.NET, but for this
project I have to do it in Access using bound controls. If I create a
sproc that selects and another that updates, how does Access know
which one to use for selects and which for updates?

In Access you bind a form to one recordset. It is different to ADO.NET.
If you want data to be updated (which happens automatically when the
record looses focus) then you must make sure the recordset used is
updatable (for stored procedures to be updatable the user must have
permissions to the tables used in it from memory?).

Hope that helps.

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response