Overview

Continue the development started in Part I: Develop & deploy mock up. First, develop a stored procedure (sproc) which updates the Northwind DB to reflect that an order has shipped and then 'wire' a call to this sproc from the code-behind the order-to-ship selection form in the Access project. It would be possible to execute all the required SQL for this update from within the Access project but it is good practice to encapsulate DB logic within the DB via sprocs, functions, and views.

Deploying the sproc

In Northwind and order being shipped brings two updates (really one update and another set of updates): the ShippedDate for the order should be set to the current date and time (ShippedDate is a datetime field) and the UnitsInStock for all products associated with the order should be reduced by the ordered quantity of the product shipped. More for convenience in keeping things a bit simple, if the quantity ordered and shipped for a product exceeds the units in stock, the units in stock is reduced to zero; in 'real life' logging some kind of exception or alert would be appropriate because this shouldn't happen if inventory is well managed.

You should have enough exposure to SQL Server stored procedures to enter and 'save' the stored procedure in .spShipOrder SPROC Code to your Northwind database. The code is presented as as images (i.e., this code can't be easily copied and pasted) because practice with building SQL and sproc's is good exercise and this forces a little exposure to the details of the SQL.

Before updating the application to use the sproc it is critical to test spShipOrder; a test script spShipOrder Test Script has been provided (which can be copied and pasted). Even though our sproc will be called by a 'friendly' application every bit of code including sprocs need to defend themselves from mis-use by accident or intent. Your results should be something like the following. The first image shows the results tab in which the script shows the order details before marking it shipped and then after marking it shipped; the test script also 'unships' the order putting things as they were which is the third display of order details.The messages tab shows expected and actual return codes for a variety of possible 

Any users (e.g., AIST3410 for us) who will run this stored procedure need permission to execute it even if the user has permissions to access data directly.

To do this in SQL:

GRANT EXECUTE ON spShipOrder to [vm-1\AIST3410];

In SSMS:

In your Northwind DB open Security | Users and right-click the user or group to get execute permission. Then select Securables and click Search.

Choose Specific objects & click OK; then click Object Types on the next dialog.

Select Stored procedures & click OK; then click Browse on the next dialog.

Find and select the sproc to set execute permission for & click OK; on the next dialog click OK.

Verify you are working with the correct user or group and sproc. Then select Execute under Grant & click OK.

'Wiring' the application

Suggested is to make a backup of your NorthwindApps.accdb in your Documents folder (your 'dev' version) and then use the original to make the following changes. While we are in development our 'production' version in DevApps can still be used by our client but when finished deploy the updated application to the production folder.

From the Access menu use Database Tools | Visual Basic and find the code-behind the Ship Order button. In Part I this was just one line with a MsgBox but will be replaced with code to execute your sproc. Before entering the new code you should add a reference the latest version of Microsoft ActiveX Data Objects x.x LIbrary. From the Visual Basic window showing code use Tools | References, scroll down to the library, click the check box, and click OK. This makes these routines available to your project.

Carefully enter the following code as the Order Shipped click event; in Visual Basic the underscore _ lets a statement continue to another line for better readability. The only change required is that DATABASE in the connection string should be changed to your Northwind database. If done correctly your FulFillOrder form should actually update the database when an unshipped order is shipped. You might verify this in your database after a few tests by looking for Orders with shipped dates of today (when you shipped).