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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<<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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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.
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.
ASKER
Thanks you.
Passing XML
in Access code you build a string
<root>
<col name="colname">colvalue</c
<col name="colname2">colvalue2<
</root>
in Access code you build a string
<root>
<col name="colname">colvalue</c
<col name="colname2">colvalue2<
</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)
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.