Recordset as Parameter to Stored Procedure

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm not sure why the SQL Server tables are more important than the local tables.  I also don't agree with the design at all.  Why would you not just link to SQL Server  and get rid of the Access BE entirely?  Having duplicate data opens you up to data anomalies should you have any updates to one side that don't make it to the other.  Have you checked recently to make sure the tables are 100% identical?

The one thing you need to consider when making the switch from Jet/ACE to SQL Server or other RDBMS is that your forms MUST be bound to queries and those queries MUST select the minimum set of records.  Once you get to that state, performance will be fine.  You will only have trouble with Access as a FE to SQL Server if you continue to use "Access" techniques such as binding forms directly to tables or unconstrained queries and using filters or simple scrolling to find the desired records.  If you ask the server to send all rows to the form, you haven't gained any efficiency from using a "real" server.  By using queries that select specific records, the server does all the heavy lifting.

To make this method work with a stored procedure, you could use "temp" tables on the server.  Although, it might be possible to create the stored procedure and have it link to tables in your Access database using ODBC.  

Unless all your edit code is in the sp, I'm just not sure this buys you anything.
I wish this conversation could take place over Beer. I fully understand the requirement and scope, and I have created this type of application several times.

There are two separate issues in the scenario above.
1) using stored procedures from Access. BingIT and you should find a number of code samples. The trick is in the events surround moving off records in grid controls as well as using a timestamp column for concurrency testing.

The other issue is passing recordsets;
  Interally to SQL server you can pass user defined types including tables, but from other clients (Access) you are limited to the supported data types in the connection object.

If the recordset is large, we can pass the select into  a procedure, stash the content and then refer to the object. I can go into the details later if required.

for smaller sets or objects, you can create an XML blob as a string and then pass that into the procedure to be used as the recordset object.  

This model is the root of high performance secure applications using any client including web sites. The only permission I grant is Execute. But populating recordsets in Access from stored procedures is a tedious affair and I understand the ROI is low there.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Declan_BasileITAuthor Commented:
<<I'm not sure why the SQL Server tables are more important than the local tables.>>

Because the data in local tables aren't committed to the company's database that is mission critical and that I've always had complete control over.  Users can enter any data they want into a local table without it negatively affecting the company's data.

The idea is that if the data is to be committed to the company's database it would have to be done via an approved stored procedure that would check the data for integrity before it's comitted.  It's the difference between giving other developers (one's that are just starting to develop applications for the company and don't yet understand the existing programs and data structure enough to have full access) direct access to changing data in the SQL Server tables or just giving them access to a few stored procedures that will change (add, delete, update) the data for them when data needs to be changed.  I need to open the applications/databases up so other developer's can contribute, but I need to do it in a responsible way that won't allow them to compromise the integrity of the data while they are learning and getting more experience.

<<Having duplicate data opens you up to data anomalies >>
It's not duplicate data.  The uncommitted data is entered in the front end and would not exist as duplicate data in the SQL Server database.  Once the user decides to commit the data, he/she would click a command button that would then *move* the data (via appending it to the SQL Server database and deleting it from the local table), similar to a website where a person fills out a form and then clicks a save button.  The data isn't duplicated anywhere.  It's data that isn't committed and then gets committed after the user clicks "Save" provided that the data passes the integrity check.

<<queries MUST select the minimum set of records.>>
The majority of the forms are bound to a minimum set of records, in many cases just the one record that is selected to be shown and sometimes it's related records in a subform.  I've got that part covered.  The part I'm asking for ideas about is not giving inexperienced developers access to bind a form to live *critical* data, yet giving them access to change data via a very restricted means.

<<To make this method work with a stored procedure, you could use "temp" tables on the server.  Although, it might be possible to create the stored procedure and have it link to tables in your Access database using ODBC. >>
I already thought of giving the developers access to a table in SQL Server with records identified to have been added from a particular instance of a program, then calling the stored procedure with that program instance id for it to know which records in the "uncommitted data" table the user wants to commit.  But how would I give a developer access to create a temp table and invoke a stored procedure from his/her front end program with a reference to that temp table?  What code would the developer write in VBA to create the temp table?  Is it an option, whether a good or bad one, to pass a recordset as a parameter to a stored procedure?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

By Temp, I didn't mean that the tables were transient.  I would use permanent tables just as you do now.  Just put them on the server.  Temp was a reference to the data since it is only in the tables temporarily.  If your current process is to use local rather than shared temp tables, you would need to add some columns to the table since it would now be shared so you could identify batches and their source because the commit action would need to work on a "batch" at a time.

Having all the tables in the same database also allows you to enforce RI which isn't a possibility now.

With the staging table on the server, you can avoid the problem of trying to have Access send recordsets to a sp.  The sp can easily get its own data from server-side tables.  You just need to pass in the batch ID.  You would then use SQL Server itself to manage permissions and who had what type of permission for each object.
Declan_BasileITAuthor Commented:
I already call stored procedures from the Access applications.  Thanks for letting me know the connection object doesn't support passing a recordset data type as a parameter.  At least I know now that that isn't an option.  I'm also glad you understand the need for what I'm trying to do.  I don't see concurrency as an issue.  Once the user decides to commit the data it would get deleted from the source and appended to a table in the SQL Server database.  

<<If the recordset is large, we can pass the select into  a procedure, stash the content and then refer to the object. I can go into the details later if required.>>
What type of object would you stash the content into?  A table?  Would the SQL statement be something like "INSERT INTO (list of fields) VALUES (list of data values)"?  I've never written this type of select statement to append more than one record.  Would I need an INSERT statement for each record (repeating the list of field names in each statement) or can the program create one INSERT statement for all the records?

I've never used XML.  Would it be text that both defined the design of the table (field names, data types, etc.) and data?  Would the stored procedure create a temporary table and populate the data based on the contents of the XML?
Declan_BasileITAuthor Commented:
Having a "temp" table in SQL Server with records identified in batches might be the simplest way to go.  I already do that in some parts of the programs.  A unique "ProgramSessionId" is assigned to a program .mdb each time an instance of a program is opened and I use that as the batch id.
I like simple.  The fewer the moving parts, the fewer the opportunities to introduce error.  We don't seem to talk about coupling and cohesion any more but moving the temporary data to the server reduces the coupling between Access and SQL server which is a good thing plus making the process more cohesive which is also a good thing.
Declan_BasileITAuthor Commented:
Thanks you.
Passing XML
 in Access code you build a string
    <col name="colname">colvalue</col>
    <col name="colname2">colvalue2</col>

declare @x xml='  <root>
    <col name="colname">colvalue</col>
    <col name="colname2">colvalue2</col>

 SQL Server Side 

declare @x xml='  <root>
    <col name="colname">colvalue</col>
    <col name="colname2">colvalue2</col>

select'.','varchar(50)') [colvalue]
   ,'./@name','varchar(50)') [colvalue]
 from (select @x [xd]) md
  cross apply md.xd.nodes('/root/col') td(nc)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.