Solved

Recordset as Parameter to Stored Procedure

Posted on 2014-04-24
9
1,164 Views
Last Modified: 2014-04-28
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?
0
Comment
Question by:Declan_Basile
  • 4
  • 3
  • 2
9 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 10

Accepted Solution

by:
Banthor earned 250 total points
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Declan_Basile
Comment Utility
<<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?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:Declan_Basile
Comment Utility
Bantor,
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?

<<XML>>
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?
0
 
LVL 1

Author Comment

by:Declan_Basile
Comment Utility
PatHartman,
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 1

Author Closing Comment

by:Declan_Basile
Comment Utility
Thanks you.
0
 
LVL 10

Expert Comment

by:Banthor
Comment Utility
Passing XML
 in Access code you build a string
  <root>
    <col name="colname">colvalue</col>
    <col name="colname2">colvalue2</col>
</root>

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

 SQL Server Side 
[code]

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


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

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now