Help with Updatable Views, etc.

[HenryE]

Hi All,

We just upgraded from Access to SQL Server 2000. I'm trying to use ADP's to do in SQL Server what I used to be able to do in Access, namely create queries that would be used as the Record Source in forms to update several linked tables. I've tried to do this with views, stored procedures, and user-defined functions, but none have worked. The only success I had was using views, where I was able to update one of the tables. It's obvious that I have very little idea about what I'm doing. Is there any way to do this, using ADP's or anything else?

Thanks.

Henry

 

[Databaseguy]

You've gotta use stored procedures.

I'm guessing your old queries had paramaters. Here's a trick. Suppose you've got the following SP.

Alter PROCEDURE sp_YourProc(@Param NVARCHAR(10))
AS
Select * From YourTable
Where yourfield = @param

In the form that is bound to the SP, go to desgin view, data tab and in the Input Parameters propery type:

@Param = me.txtYourField (where txtYourField is a text box on the form)

This will pass the parameter from the form to the SP.

 

[cmmrfrds]

One difference between Access and SQL Server is that Access has dynasets that allow you to update more than 1 table in an SQL Statement. To my understanding, this is not standard ANSI SQL practice where only 1 table is updateable in an sql statement. Access ADP has a property on the Form data tab called unique table where you can specify the table to be updated in an sql statement. You can use a View name, Stored Procedure, or an SQL Statement as the recordsource for your Form. You will need to update each table in a separate sql statement.