Recordset as Parameter to Stored Procedure
Posted on 2014-04-24
For many years I've been using Access 2003 with linked SQL Server tables. I've written parts of the program where a the user would enter data into an Access table contained in the .mdb, and then click a button which would run an append query to append the records in the local access table to a linked SQL Server table. This has always worked very well and still does, with minimal development time and making the program easy to maintain.
Now other developers are starting to contribute to the development of applications. They have read only access to the SQL Server tables; however, in some cases they need to be able to change data in the SQL Server tables, yet I need to keep the core data in these tables secure and protected. I want for them to 1.) Only have ready only access to the tables (which they already have), and 2.) be able to cause data in the SQL Server tables to change *only if* they invoke a stored procedure that I wrote. I don't want to give them direct access to changing any of the data until they have enough experience and understand the database enough.
How can I change the "local access table/append query" design I described in the first paragraph to a SQL Server stored procedure that other developers can call upon to change data in the SQL Server tables? Can they pass a stored procedure a recordset of the records in the temporary table? What would the basic parts of the stored procedure look like? Is there another way to accomplish this other than passing a recordset to the stored procedure?