Overview

Create the development copy of the application in your Documents folder. When it is completed it will be 'deployed to production' by copying it to your folder in C:\DevApps on the remote desktop VM; your users will use this copy of the application for their work. In addition to creating the project using Microsoft Access you will need to allow your users read access to the deployed project as well as read and update access to your Northwind database.

Your user is the login AIST3410 (with AIST3410 also the password). In 'real life' this might be several users or, better, an established user group allowed access to the application. The idea is that only this user (and you) can operate the deployed version of the project.

Note: In the development and accompanying video you will see tschultz as the login ID of the developer. You should substitute your own login ID when trying the development for yourself. Importantly, the name of your owned copy of the Northwind database is assumed to be {LoginID}Northwind so if the database is named differently be sure to adjust your development.

The Data Model

Start Microsoft Access creating a Blank desktop database project named NorthwindApps.accdb saved in your Documents folder (recommended but put it where you want). If presented with a new table definition form just dismiss it (and if a table was accidentally created just delete it).

Use External Data | ODBC Database to create a linked table and create a (or use an existing) SQL Server connection to your copy of the Northwind database using Windows authentication. Use this connection to link to the Orders, Order Details, and Products tables.

Using Database Tools | Relationships create and save the within-project data model shown below:

Using Create | Query Design create and save qryUnshippedOrders using this design:

which generates the following 'hidden' SQL:

SELECT dbo_Orders.OrderID, dbo_Orders.CustomerID, dbo_Orders.RequiredDate, dbo_Orders.OrderDate, dbo_Orders.ShippedDate

FROM dbo_Orders

WHERE (((dbo_Orders.ShippedDate) Is Null))

ORDER BY dbo_Orders.RequiredDate, dbo_Orders.OrderDate;

The Application Form

Use Create | Form Wizard to create the FulFillOrder form (and a sub-form) using these guidelines.

From the qryUnshippedOrders select CustomerID, RequiredDate, and OrderDate. Don't click Next yet but instead use the Tables/Queries pull-down to refer to the Order Details table from which you want to add Quantity and ProductID to the fields list. Then from Products add the ProductName. After all fields have been selected click Next and choose to view your data by qryUnshippedOrders in a Form with subform(s)

 

In the next Form Wizard step selecting Tabular for the sub-form provides the most formatting control; click Next. Name your Form as required and (recommended) the sub-form with sfrm pre-pended. Leaving Open the form is suggested to see the basic layout ... modifying the form design comes a bit later. Click Finish.

You will get the  template form that looks something like this. The format needs work but the functionality is what is needed: as the user advances through the unshipped orders the list of products and their quantities for the order appears. This would allow a packing department worker to select an order and see what needs to be picked and packed to fulfill the order.

With a little work on formatting the form can be more presentable

Stubbing in Updates

In Part II we look at getting updates to occur when the user indicates the items have been picked and packed. For now we'll add the button but when clicked it will just popup with the OrderID selected for shipment. Key properties for the button are giving the control a name and setting its Event | On Click property to an event procedure something like the following.

 

Deploying the Application

By convention Northwind network applications are placed in the C:\DevApps\{JagNetID} folder where users will find them. Exit Access and copy NorthwindApps.accdb from your Documents to this folder. You should find that the application works for you from this folder though the first time in there will be a warning about active content -- do test that the application works for you before moving on.

Trying to use the application from user AIST3410 perspective leads to issues until we allow the AIST3410 permission to the application and to the underlying database. From a command prompt type 

runas /user:aist3410 "C:\Program Files\Microsoft Office\Office16\MSACCESS.EXE"

using AIST3410 as the password and try to open the NorthwindApps.accdb in your DevApps folder. An error occurs to the effect that either the application is in exclusive use or you (i.e., user AIST3410) don't have permission to view and write its data. Update security on the copy of NorthwindApps.accdb in your C:\DevApps folder so user AIST3410 has read and execute permission (e.g., see left screen-cap below).

Repeating the runas command above now gets a bit further ... Access opens the project starts and user AIST3410 gets the warning about active content and another that the Access project is read-only but when user AIST3410 tries to open the FulfillOrder form there is an error about the ODBC connection failing. Since we chose Windows Authentication for how the application would authenticate itself to SQL Server the connection fails for user AIST3410 because they haven't (yet) been given permission to read and update your {JagNetID}Northwind database.

The DBA has added Windows user AIST3410 to server users but not to your Northwind DB. Using SSMS (or other tool) add AIST3410 to your Northwind DB users giving the db_datareader and db_datawriter role memberships. You should find that this allows user vm-1\AIST3410 to use the application.

To do this with SQL:

CREATE USER [vm-1\AIST3410] FOR LOGIN [VM-1\AIST3410] WITH DEFAULT_SCHEMA=[dbo];

ALTER ROLE db_datareader ADD MEMBER [vm-1\AIST3410];

ALTER ROLE db_datawriter ADD MEMBER [vm-1\AIST3410];

To do this with SSMS:

: