20

Securing, Deploying, and Maintaining Access Applications

WHAT'S IN THIS CHAPTER?

• Review how to track and identify users and changes to the data in the databases solution
• Walk through development lifecycle environments
• Look at different deployment methods
• Learn how to maintain your database solution

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wiley.com/go/proaccess2013prog.com on the Download Code tab. The code files are in the Chapter 20 download, and are individually named according to the code filenames noted throughout the chapter.

In this chapter you will read about methods to secure who can access your solution and how to set permissions to the users who access the database. You will also learn about different methods to set an audit trail to capture and log changes users make to the data once they are allowed into the database.

The chapter discusses the development cycle and the different environments you need to support development and your database solutions. You will read how to update and maintain the database and the front-end (FE) file once you deploy the solution to your users.

USER LEVEL SECURITY AND AUDIT TRAIL

Some of the database solutions you design will require you to include security features. The types of features will range from setting different permission levels and access limits for different users, to creating an audit trail to capture who did what with the data.

It can be important to a company to control who gets to access the solution. Not every employee needs to be able to log into the database. Additionally, even when a user is authorized to log into the database, permission to specific data elements may be restricted. For example, only users who work in payroll should see the salary information for the company's employees.

The following discussion points will help you and the business owner identify and set up the permissions:

• Who can open a specific form/report?
• Who can edit the data in a form?
• What fields do they need to see or edit?

The security matrix you design for each database can vary in complexity. It can range from setting permissions to specific forms and reports, to setting permissions to specific controls on a form, to showing a defined or custom ribbon. Additionally, you can set read-only or read-write permissions for the different users. The sample database, Security.accdb, included in the online material, has sample code for you to review the techniques.

Figures 20-1 and 20-2 show you what a form, My Profile, looks like to an Admin and to a User.

FIGURE 20-1: Form viewed in Admin mode

FIGURE 20-2: Form viewed in User mode

There are a couple of ways for you to set a security matrix: user-based, and role-based.

The first method is user-based — each user is listed along with what he or she can see or do in the database solution. This method allows you to customize the access level per user. Although it may sound like a good way to set up the security matrix, it is not a recommended way to go.

As you enter the users and the various permissions, you will find out that you are duplicating security records. Additionally, when a user changes jobs within the same company, you end up with a heavier maintenance load when you change that user's security profile.

The second method is role-based security. In this method, you create different roles — for example: Administrator, Data Entry, Read Only, and so on. You designate what each role can access and do in the database solution. When you add users to the database, you assign the user to a role.

Because a user must be attached to a role, the role-based security method provides for a consistent security matrix with much simpler maintenance. When a user changes jobs within the company, all you need is to change the role of the user. Additionally, if the system is audited, as some systems are, it is easy to generate an audit report to show who can do what in the database solution.

In the following sections, you will read about different ways to set the security matrix in your database applications. The discussion uses the role-based method because it's considered to be the better way to set up a security matrix.

Local User Table

In each database solution you design, you include additional tables to store information about users, roles, and the different form, report, and control objects in the database.

When you, or the database solution administrator, add users to the solution, you first set up a username and a password for each user before you assign the user to a role. When a user wants to log into the database, he needs to use the username and password you created. Most database solutions offer users an option to change the password once they are logged in.

The benefit of this security method is that it works in any environment: network with domain, network without domain, and standalone.

The list of downsides of this method is longer. Users need to remember yet another user ID and password. The user ID and the password are stored in a table in Access. If someone gets into the tables, they can use the information to log into the database as someone else. Additionally, when users leave the company, they need to be deactivated in the database along with other systems.

This method can work well in small, standalone environments with a low employee turnover rate. It also works for environments where a few users with different access levels to the solution share the same workstation with a common login.

Network Identity

Most companies use Active Directory (AD) to manage the network domain and the users. AD lets you set various groups and assign users to one or more groups. You can leverage that feature and create the different group levels for the database solution in the AD. After the groups are set, you can assign the security matrix in the database file to each group.

After you create the security groups in the AD, you assign the users to the group they belong to. If the security matrix is not hierarchical, a user may belong to more than one group.

When the user tries to open the database solution, the system will identify the Windows user ID and, based on the group the user belongs to, the solution will open, or the system will provide a message to the user that he's not authorized to open the solution. Similarly, you will test users' permissions to forms and controls as they navigate the database solution.

Using this method does not require the users to log into the database. As long as they are logged into the workstation, the solution recognizes them. The users' information is stored in a central location that is already managed by IT. Additionally, if the solution you design uses SQL as a back end, this method works well with the server's security. Another benefit of this method is the ability to use the same groups across multiple database solutions as long as the roles and the users in each role are the same.

You will need to work with IT to set up this method. In most companies, the IT department is responsible for creating the groups in the AD and managing the users in each group. After deployment, the database business owner will work with IT to manage the users in each group.

This method relies on each user logging into a workstation under his or her own Windows network ID. If a few users that belong to different security groups share the same workstation without logging in and out, it will not work.

http://www.accesssecurityblog.com/post/2011/02/05/Securing-
Access-databases-using-Active-Directory.aspx

Network Identity with a User Table in Access Database

This method is a hybrid of the two you read about in the previous pages. It combines the use of the users' network IDs to validate who logs into the database, but the security matrix and the roles are stored locally in the database.

This method is good when you use a hierarchical model for security. In the hierarchical model, each group can access or do everything the previous group can, plus additional permissions. For example, the “Reporting” group can only run reports in the database solution; “Read Only” can run reports and view data using the forms; “Data Entry” can do everything “Read Only” can do, plus edit data; and so on, all the way to the “Administrator” group, which can do everything, including add and inactivate users.

The benefits of this method are also a combination of the benefits from the previous two methods: Users do not need an additional login for the database; it uses the Windows network ID. Control over the groups and the users stays with the database business owner. There is no need to work with IT when you add a new user or change permissions for an existing user. Although you are storing the user ID in a table, you are not storing the passwords in the database. Passwords are managed by IT on an enterprise level.

In systems using Windows 2000 or newer, even if there is no domain, you can read the user's Windows login name using the fOSUserName() function written by Dev Ashish: http://access.mvps.org/access/api/api0008.htm.

Much like the previous method, this method relies on each user logging into a workstation under his or her own Windows network ID.

Different Front-end Files for Different User Roles

Another method you can use to ensure a different level of access for different users is to create and distribute different FE files. For people who only need to run reports, the FE will offer only the reporting menu and the reports. For the users who only need to view the data, the FE version will have the forms' properties set to Read Only in advance, and it can offer a specific set of forms based on the business rules.

It's a good way to deploy a reporting-only version for users who do not need to edit data in the database. The reporting FE provides a simpler UI and, in many cases, does not need to be updated as frequently as the full version of the FE file.

This method can be very simple to implement: after you create the full FE solution, you make copies of the file and modify the objects to support the different users. It can be an effective method if the solution requires only two or three different user group levels. Additionally, it can also be cost effective for small organizations where the ongoing maintenance is minimal because this method does not rely on any other factor outside of the content of the FE file.

The downside of this method is maintenance. If you need to go back and make revisions to the FE solution, you may need to make the same revisions in multiple FE files. This can cause a duplication of effort and increase in maintenance cost.

Security Summary

There is no one right way to implement a security matrix. Each security method described earlier has its pros and cons. Your role as the developer is to work with the business during the design phase of the database solution to identify the method that best fits the business model and resources available.

Make sure to interview the appropriate people from the IT department to learn the processes and procedures they use to manage the users and groups. This will help you analyze the best way to create and implement the security matrix for the database solution.

Converting from .mdb with User Level Security to .accdb

Access .mdb files provide a User Level Security (ULS) feature. ULS uses .mdw files to record and maintain the permissions for users and groups. After the database administrator added users to the database and assigned them to a group, users needed to log in to the database with the assigned username and password. Microsoft deprecated the ULS feature when it came out with .accdb files in Access 2007. Although you can run mdb files utilizing ULS in newer versions of Access, ULS cannot be used with .accdb files.

Although ULS sounds like a powerful and easy-to-use security feature, in practice it is a very advanced topic that few developers implement correctly, and it is vulnerable to password crackers and hacks.

To upgrade an mdb file that has ULS to an accdb format, your first step is to unsecure the database. You will need to upgrade both the FE and back-end (BE) files, including all of the necessary database objects.

Essentially, you have two options. The easiest approach is to use the backup file, usually named DatabaseName.bak, and convert it to .accdb. The backup file was created when the ULS was added to the .mdb database solution. It is a copy of the database file without the ULS. You can either open the backup file in Access 2013 and use the Save As option to create the DatabaseName.accdb file, or create a new .accdb file and import the database objects from the backup file.

An alternative approach is to use the Access 2003 (or earlier to match the file format) to remove the ULS before you import the objects into an .accdb file.

You can use the following steps:

1. Open the database file using Access 2003 (or earlier as appropriate) and log in to the secured .mdb database file as an administrator.
2. Use the User Level Security Wizard to change the Users group permissions to Administer for all database objects (see Figure 20-3).

   

   FIGURE 20-3: ULS Wizard

3. When done, exit the .mdb file and Access.
4. Open Access 2013, create a new .accdb file, and import the desired objects.

The new .accdb file is devoid of ULS and is unsecured. The database objects can be modified and you can proceed to implement a different method of security to the database solution.

Audit Trail

Depending on the specifications of the database solution you design, you may need to add an audit trail module. In its core, an audit trail collects and stores information about changes made to the data, the user who made them, and the time the changes were made. It typically does not store information about read-access to the data and who viewed it.

If you develop the database solution with SQL Server Enterprise Edition as the database engine, the audit trail option is available to you on the server side. When you or the Database Administrator (DBA) set the database, you can set the audit trail options and the server will take care of the process.

Access, on the other hand, does not offer a built-in audit trail tool. It is up to you to design and implement it in the database solution. In versions prior to Access 2010, you could only design an audit trail for changes performed through the forms. Changes made directly in the tables, through manual entry or the execution of an action query through the QBE, could not be tracked.

With the data macros available to you since Access 2010, you can build the audit trail to capture changes wherever they occur, including changes made directly in the table.

When you use VBA to design the audit trail, the code needs to fire on the Before Update event of the form. This allows you to capture both Control.OldValue and the Control.Value of any bound control and store them in the audit trail table as the Before and After values.

When you use data macros, you use the After Change event to capture the values the user changes in the table. Data macros retain the Old.FieldName value after the record is changed for you to use as the Before value.

The actual data components you capture in the audit trail record can vary based on the business rules and relevant regulatory guidelines. At the least, to make the audit trail an effective tool, you should record and store the following data elements:

• Table name
• Record primary key value
• Field name
• Field value before change
• Field value after change
• Date and time of change
• User ID making the change

Optional values you can capture if they add benefit to the audit trail are:

• Changed through (the name of the form used)
• PC IP Address used to access the solution (may not work in all environments)
• Record inserted (new) or record updated

The Audit Trail sample database accompanying this book, AuditTrail.accdb, has sample code for an audit trail using the VBA method and the data macro method in the tables.

Using VBA

To create an audit trail module for your database, you start by designing the table to store the information you want to track. After the table is ready, you will create a module in your FE file. In your code, you will call that module in the Before Update event of the form (code file: Deploy_Maintain.txt CaptureAuditTrail):

'------------------------------------------------------------------------------
' Function  : CaptureAuditTrail
' Author    : DougY
' Date      : 4/1/2013
' Purpose   : Insert data into the audit trail table
' Arguments : strTableName = Name of table
              frmName = Name of the form used
              strRecordPK = Primary key value of the record that was changed
'------------------------------------------------------------------------------

Public Function CaptureAuditTrail(strTableName As String, _
                                    frmName As Object, _
                                    strRecordPK As String)

    Dim ctl As Control
    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("tblAuditTrail")

    For Each ctl In frmName.Controls

        Select Case ctl.ControlType

            'not all controls have ControlSource property
            'acControl ID below are: 106, 111, 110, 109, 122
            Case acCheckBox, acComboBox, acListBox, acTextBox, acToggleButton

                If Not IsNull(ctl.ControlSource) Then
                    If ctl.Value <> ctl.OldValue Or IsNull(ctl.OldValue) Then

If Not IsNull(ctl.Value) Then
                            rst.AddNew
                            rst("ChangedThrough") = frmName.Name
                            rst("TableName") = strTableName
                            rst("RecordPK") = strRecordPK
                            rst("FieldName") = ctl.ControlSource
                            rst("BeforeValue") = ctl.OldValue
                            rst("AfterValue") = ctl.Value
                        End If
                        rst("ChangeUser") = fOSUserName()
                        rst("ChangeTime") = Now()
                        rst.Update
                    End If
                End If

            Case Else
                'do nothing

        End Select

    Next

    'clean up
    rst.Close
    Set rst = Nothing

End Function

When called, the CaptureAuditTrail() function cycles through the controls on the form, compares the control's value to the control's old value. If the values are different, the code will insert a new record to the audit trail table with the data components you decided to capture (code file: Deploy_Maintain.txt Call Audit Trail):

If Me.Dirty Then

     Call CaptureAuditTrail("tblClass", Me, Me.txtSubjectID)

End If

Using Data Macros

If you go the data macro route, you will add an After Update macro for each table you want to track. In the macro, you will list the condition it will meet to activate, and the values to be recorded in the audit trail table. Figure 20-4 shows you the audit trail data macro being set.

After the macro is set and saved, every time a record is added or modified in the table, the information is recorded into tblAuditTrail.

FIGURE 20-4: Setting audit trail with data macro

Although the setup of the audit trail with data macros is more cumbersome than the VBA function, the benefits may outweigh the extra work. Data macros enable you to capture data changes in situations where VBA cannot be used, for example, when the data is changed directly in the table.

Remote Query

In some cases, the audit trail data can be highly sensitive. When this is the case, consider taking an extra step to hide the table from possible prying eyes. Instead of linking the audit trail table to the FE file, use a remote query technique to append the data to the audit trail table in the BE database or a different database file altogether. The following SQL script shows you the syntax of a remote append query (code file: Deploy_Maintain.txt Remote Query):

INSERT INTO tblAuditTrail
  (TableName
, RecPK
, FieldName
, BeforeValue
, AfterValue
, ChangeTime
, ChangeUser )
IN '\COFFEEMyFolderMyDatabase.accdb'
SELECT <list of values to insert>;

The line IN '\COFFEEMyFolderMyDatabase.accdb' tells the query to find the table tblAuditTrail in the database file MyDatabase.accdb without the need to link the table to the FE file. If you use the QBE to construct the query, you change the query property Source Database, as shown in Figure 20-5.

FIGURE 20-5: Setting remote query in QBE

You cannot use the remote query option if you use data macros. The audit trail table needs to be either local in the FE or linked to the FE file.

Audit trail can be an important feature of the database solution you design for your clients. However, it is not a replacement tool for tables that hold historical data, such as changes in product price, or changes in salary for staff. It is meant to help you and the business to track changes, and if needed to reverse erroneous or malicious change.

DEPLOYMENT CONSIDERATIONS

After you are done with the development phase of the database solution, it is time to deploy it to the users. The initial deployment step is usually different from subsequent deployment of updated versions. In the following sections you will read about both deployments and the different methods you can use for each of them.

When you review deployment options you look at a few key factors:

• Size of company — Large companies usually have an IT department with resources, both systems and personal, to manage and support a large deployment.
• Number of users — It can be faster and cheaper to manually install the solution on a handful of workstations versus using a commercial deployment solution.
• Location of users — If users are in remote locations, it may be easier and more cost-effective to use a deployment solution instead of traveling to the different locations. Alternately, you can e-mail the deployment files to the remote users for them to deploy.

Based on those factors, you can choose the method that best fits your process and the environment of your clients. There is no right way or wrong way; as long as the method works for you efficiently it's the right one.

Depending on where you work and your agreement with the client, you may choose to deploy an accde FE file instead of an .accdb file. An .accde file secures the design of forms, reports, and code from the user. If you do that, remember to safeguard the original copy of the .accdb file; you will need it to maintain and update the FE file.

If your solution uses your own reference library (you can read about creating a reference library in Chapter 16), you should store the reference library file in a centralized location on the network, accessible to the users and to the FE file. This allows you to update the code in the library file and still retain the reference link to the FE files.

We recommend you review the code before you deploy the solution to look for hard-coded values of paths, file folders, and so on — for example, strPath = "C:UsersDoug". You and your users will be better served if you use configuration tables within the database solution to store the information and call it from the code. For example: strPath = Dlookup("Path", tblPath", "PathType = SaveReports"). If the path needs to be changed after the solution is deployed, you change the value in the table without the need to edit and test the code itself.

Additionally, it is better to use Universal Naming Conventions (UNC), sometimes referred to as Uniform Naming Convention, when storing the path of folders and files. It is possible for different workstations to map to various drives using different letters. For example, the network drive \CoffeeData can be mapped to drive Y: on one machine, and to drive S: on another. If you use Y:Data in your database solution, it will generate an error on the workstation using S as the letter for the same drive.

If different versions of Access are installed on different workstations, it is best practice for you to develop in the lowest version available to the users. This ensures that the users can open the FE solution and that you did not use features available in newer versions of Access, including the file format .mdb or .accdb.

Deploying Front-end Files

When you deploy the solution, you deploy the FE file to the users. The BE file, which holds the database tables, remains on a network location available to all users, and it is not deployed to the users' workstations.

Usually, the FE file is deployed to the workstation hard drive. However, depending on the business rules, you may need to deploy the FE to the user's personal network drive space. This option prevents data breach if someone removes the workstation from the premises because the file is not stored on the workstation. In either scenario, each user ends up with a separate FE file connected to the BE database engine.

Although different users may get different flavors of the FE solution, it is important that they use the same release version of the FE for each flavor. It's easier and less hassle to support one version of a solution throughout the organization. It also ensures that users have the latest features for the FE file they use.

Although the actual location of the FE file can vary, it is common practice to place a shortcut to open the solution on the workstation desktop. Ideally, that shortcut is placed in the C:UsersPublicDesktop folder, so it appears for all users on the machine. Make sure that you copy the files to a folder on the workstation that all users have access to. In previous operating systems that would be C:Program Files, but in modern versions of Windows, a folder below C:UsersPublic is a safe place.

Much like other topics we discussed, there is more than one way to accomplish the initial deployment task. In the following sections you will read about two methods, manual and automated. Because each deployment can be different, you, as the developer, are best positioned to decide which one to use.

Manual Install

If you need to deploy the system to a small number of users located in the same office, one of the options at your disposal is to go around the office and copy the FE file to each user workstation.

In a small office environment, or for a small department within an organization, this is a simple and effective way to deploy the FE solution. It also allows you to verify that the solution starts properly on each workstation.

What you copy to the workstation depends largely on how you plan to publish FE updates to the workstations. If you use a batch file to get updated versions, you copy the batch file only to the appropriate folder. If you use an auto-updater tool, you may need to copy the FE solution file along with the tool.

Consider creating a deployment folder on the network drive. In the folder, you have all the files you need to deploy the solution to the workstation.

Push to Workstation

Deployment of the FE solution to a large number of employees may require a different approach. Although doable, it is not efficient to go around and manually install the FE file on 50 or more workstations.

In larger companies, the IT department usually has network and workstation management tools. One of the features in those tools is the ability to remotely push files to the workstations connected to the network.

When you use this method to deploy, you work with the IT department to identify the users who need the database solution and the location of the files. The management tool will do the rest. After the FE is deployed, it is prudent to walk around and test a few different workstations to make sure the solution is deployed properly.

Many network and workstation management tools are available. In most cases, the IT department has already chosen the tool they want to use. As a developer, you work with the tools available in each client's site and with the IT department to push the new FE solution to the users.

In smaller companies, you may need to revert to manual deployment if the client does not have an IT department with network management tools. One possibility is to e-mail the users instructions about what files to copy from the network drive and the location where they can save the FE files.

Automation of deployment can also be accomplished with the use of deployment packages. Access comes with a built-in tool to create a Package Solution. The package generates a Windows Installer Package you distribute to the users. Additionally, there are third-party deployment tools that will create the package you will provide to users. The following are a couple of tools for you to review if you want to take this route.

• Inno Setup — http://www.innosetup.com/isinfo.php
• Sagekey — http://www.sagekey.com

Auto-Updating the Front End

Now that the FE file is deployed, you may be asked to work on enhancements and updates to the database solution. When the new version of the FE file is ready, the next step is deploying it to the users.

Either of the two methods described for the initial deployment can do the job for subsequent updates. However, there are better ways to update the FE on a user workstation. The auto-updating methods allow you to automatically update the FE file, even multiple times during the day. All it takes for the user to get the latest version is to exit the database solution, if they are using it, and open it using the shortcut icon you provided.

Auto-Updater Tool

The main concept of the auto-updating process is using an auto-updater (AU) tool to check the version of the FE file located on the user's workstation and compare it to the master version of the FE file located on the server.

When the user double-clicks the shortcut to the database solution, the shortcut opens the AU tool first. The tool validates that the local version matches the version on the network. If the versions match, it proceeds to open the local database solution file. If the version number is different, the AU tool will copy the FE file from the server to the designated location on the user's workstation and then proceed to open the file.

There are a few AU tools and products available for you to download and use. Some have a cost associated with the license, and some are free. The following is a list of some AU products for you to review to see which one is right for you:

• Auto-FE-Updater by Tony Toews — http://autofeupdater.com/
• DBUpdate by Gunter Avenius — http://www.avenius.de/?Produkte:DBUpdate
• mdb-Loader by Henry Habermacher — http://www.dbdev.org/down42.htm
• UtterAccess Code Archive — http://www.utteraccess.com/forum/Easy-Front-Autoupdater-t304808.html

Batch File

Another approach is to use a Windows batch file. In this method, when the user clicks the shortcut icon, it executes a .bat file with a code to copy the FE file from the server to the user's workstation.

The code that follows copies the Octothorp.accde file from the location on the server, \JavaHutDataMathDB, to the user's personal network folder, \CoffeeMathDB. The code opens the database file after it is copied over (code file: Deploy_Maintain.txt Batch file to Copy FE):

@echo off

@echo.

@echo Please wait The NEW database is updating...
@echo.

xcopy /Y /Q /H \JavaHutDataMathDBOctothorp.accde \CoffeeMathDB
\CoffeeMathDBOctothorp.accde
:End

With this method, the user always gets a fresh copy of the FE file, even if it's not an updated version. This method not only assures that the user has the most recent version of the database FE solution, but it also takes care of the need to manage the Compact and Repair process for the FE file located on the user's workstation. As long as you compact and repair the network copy of the file before you release it to production, the users get the compacted file every time they open the database solution. You will read more about Compact and Repair later in this chapter.

Deploying Back-end Changes

In some cases the changes you are asked to do will involve changes to the database structure. You may need to modify existing tables or add new tables to the database. Deployment of a revised back end requires a different approach from deployment of FE files. The data stored in the database must be the same after the update as it was before it to ensure the integrity of the database solution.

You apply the same development cycle principles to the BE changes as you do to the FE. You gather the requirements and make the changes based on the specifications you design for the new BE database.

After you complete the changes to the database in the development environment and you test the changes, it is time to promote the revised back-end file to the production environment. To do so, you need to take full control of the BE file for the duration of the update.

The first step is to make sure all users are out of the database solution. Common practice is to notify the users in advance of the system downtime. The date and time for the update are determined in cooperation with the business owners to allow for minimal disruption to the workforce. In many cases, the update is done overnight, or over a weekend.

To ensure users cannot connect to the BE during the downtime, you can change the FE file on the server to a file that is not connected to the BE, and use a startup form with a message to the users that the system is down for maintenance. The auto-updater tool will copy the temporary FE file to the users' workstations should they try to open the file. You will release the new production FE version after the deployment of the BE is completed.

When you have control of the BE file, the next step is to make a backup of the current production version. Before you make any changes to the database structure, you want to ensure you can revert back without data loss should anything go sideways.

After the backup is done and the copy is secured, you can proceed in one of two ways: modify the database structure and reapply the changes you made in the development copy to the tables in production, or append the data from the current production database into the updated database. If you choose to append the data, you need to be extremely careful with the values in the autonumbers fields. If not done correctly, you may break the relationship between tables and render the database useless.

When you are done with updating the new production database, your next step is to validate that all the records from the production tables are still there, or were appended properly to the new database. To do so, consider using a checksum.

For the checksum, you should total not only fields that store amounts, such as Price, Cost, and so on. Look at other fields that hold numerical values and add them. A good example is to sum the value of the table primary key if you are using an autonumber (long data type) for it. Be creative with selecting the various checksum fields; it will help you succeed with the validation of the data migration.

If the database engine is SQL Server, you can use the CHECKSUM_AGG() function to compare the values in the tables.

After you validate the data in the new BE, you promote the new file to production, along with an updated FE file that has the tables linked to the new BE, and the UI to expose the new data components to the users.

MAINTAINING DIFFERENT ENVIRONMENTS

Throughout your career as a developer, you design new database solutions and maintain and update existing solutions. When you work on the database solution, it is best practice to use different environments for different stages of the development cycle.

A standard development cycle consists of the following stages:

• Requirements
• Analysis
• Development
• Testing
• User Acceptance
• Deployment

The standard development cycle calls for at least three different environments for you and other developers to work in when you develop:

1. Development (DEV)
2. Testing (TST or QA or UAT)
3. Production (PROD)

Some companies have additional environments, such as Adhoc, Support (SUP), Proof of Concept (POC), and so on. You will read more about the optional environments further in this chapter.

DEVELOPMENT, TEST, AND PRODUCTION ENVIRONMENTS

Development Environment

After you collect the requirements and get the specification approved both by you and the business owner, you start the development of the solution or the requested changes in the DEV environment. This environment is usually accessible only to developers, and end users do not have permission to access it. If there are only three environments, this is also the environment you use to experiment with new concepts and test new approaches for the system you maintain.

When you are done with your development and tests, it's time for users to test the new solution in the Test environment.

Test Environment

The Test environment may have different names in different companies: Test, QA (Quality Assurance), or UAT (User Acceptance Testing). User testing, also known as User Acceptance Testing (UAT), should be done in the QA environment. Using a separate testing environment allows you to have the users test a specific feature while you keep on developing the next feature without a conflict.

Unlike the DEV environment, the QA environment usually has more recent data for the user to test with. This helps users identify issues more easily because they can compare the results to the data in the production environment.

When users finish the tests and approve the changes, the next step is to move the application to the PROD environment.

Production Environment

The PROD environment is the most important one of all environments. When you promote the database solution to this environment, it is as ready as it can be for primetime. It is also the environment that gets the most attention from those who support the network, usually the IT Department.

The main focus of the IT department is to support the production environment. The health of the PROD environment is related directly to the operational health of the business and the productivity of the staff. If there are issues with the PROD environment and with another environment, the highest priority for resources goes to the PROD environment. Test or QA can wait for the next available analyst after PROD is up and running.

Additional Environments

The additional environments are usually copies of PROD with varied data refresh cycles to allow you to support the system, experiment with proposed changes to the database solution, and to help define the scope for new projects. They are optional, and the business may not have the infrastructure or the resources to support the multiple environments on top of the basic three. Additionally, some of the environments can be collapsed into a single environment.

Adhoc

When you or the business analysts need to data mine the database, an Adhoc environment eliminates the processing overhead to the PROD environment. The database solution may support built-in reports; however, analysts may need to create and run queries to analyze data. A large number of complex adhoc queries executed against the production environment may impact the performance of the PROD database and slow down users' productivity.

An Adhoc environment for data analysis will alleviate this problem and allow the analysts to do their job without interfering with the resources of the production environment.

To be effective, the data in the Adhoc environment needs to match the data in PROD as much as possible. Usually, a nightly refresh should do because the Adhoc system is usually used for analysis versus operational reports. Most of the solutions you develop will have operational reports in them as part of the design specifications.

Support

The Support (SUP) environment provides you or the support analyst with an environment to try to reproduce errors reported by the users, and it does so without requiring changes to the production data.

When users encounter errors or issues as they perform their daily tasks, they report it to the helpdesk. After you or the helpdesk log the help ticket, someone from the support team needs to investigate. When you re-create the steps the user performed when the error occurred, you may need to edit data in the process. The SUP environment provides you with a safe dataset to perform the support.

Proof of Concept

The Proof or Concept (POC) environment allows you to test different concepts to evaluate if they're worth pursuing. It differs from DEV because not everything you experiment with in the POC environment will make it out of the conceptual stage. It's a good place to test different “what-if” scenarios.

You can test proposals from users to improve the system before starting the analysis phase of the development cycle. Having an environment to experiment in can be beneficial as it can save time from further analysis or projects that may not mature properly.

Back-end Data Refresh Cycles

After the various environments are created and named, you will identify and implement the refresh cycle that best meets the business needs for each of the environments. The settings can vary from a shadow server with near real-time refresh rate to the other side of the spectrum, an environment set to be refreshed on demand every so often.

The guidelines that follow are just that, guidelines. It is up to you to work with the business and the IT department, if there is one, to find the balance of resources to refresh cycles.

One of the popular settings for refresh cycles, usually for the Adhoc and SUP environments, is to make a full copy of PROD, overwriting any changes to the environment nightly. This method allows decision support analysts and system support analysts to use production-like data to perform their respective tasks.

The DEV environment refresh cycle can vary to meet the development needs. You may decide that a regularly updated copy of PROD is best to support your development, or maybe you prefer a snapshot of the data before you start the design phase of the changes that will allow you to see how the changes you make behave over time.

The QA environment is usually refreshed before handing over the system to the UAT testers. This allows the users to start the test using a production-like data. It also enables you and the testers to see the differences between PROD and QA as the new features are tested.

The POC environment can be updated as needed. Not all suggested changes can be tested in one day. Some involve processes that may take a few days or even longer to fully cycle in the system. It is better to not update too frequently to allow the developers and analysts to evaluate the new concepts over the needed time period.

Front-end Environments

When you have multiple back-end environments, it also means you have multiple front-end versions at your disposal. Each version of the FE is linked to the related BE database. You can also, in some of the cases, have a single FE file with an option to switch the linked tables from different BE files as needed.

First on the list is the master copy of the production version. The one that is safely stored in the version control tool available to you. This copy is not changed until any revisions go through the proper design cycle phases and are approved to be pushed to production by the business owner.

The development copy is the one where you and your fellow developers work to implement any changes and enhancements requested by the users. It, too, should be stored in a version control tool and checked in at least at the end of each day. This will ensure that any changes made are saved and available to the team the next day. Using a version control tool also allows you to roll back changes made in case of an error.

Leading the “top three” environments is the QA copy of the FE. You distribute this file to the UAT users who are assigned to test the changes to the database solution. Much like the previous two versions, it should be stored in a version control tool to allow consistency and safe keeping.

The Adhoc, SUP, and POC versions are usually copies of the current PROD version. Because those versions are used by a selected few, the FE may be open as an .accdb format, allowing the analysts more access to the different database objects and options, such as creating UDF for the adhoc queries for analysis. If the analysts are only creating queries, you can keep the FE as an .accde file.

Switching Between Back-end Environments

As you work with different FE environments, you need to connect them to the matching back-end environments. Additionally, as mentioned earlier, you may utilize one front end to connect to different back ends. You can switch between linked back ends by using the Linked Table Manager in Access — you will find it on the External Data ribbon. Choose the tables you want to refresh and select the Always prompt for new location checkbox, and the process will ask you for the location of the linked tables you want to update.

If you select linked tables from different sources, you will be prompted for a new location for each table, not each source. You can handle that situation by refreshing all linked tables from one source at a time, even if you end up connecting to a single BE environment when you are done with the relink process.

You can save yourself the manual linking steps if you create a module with a code to enable you to switch environments at the click of a mouse. Additionally, there are tools already developed by others that you can use to relink the FE file to a different BE file.

Review the following tools to see which one works for you:

• Dev Ashish — Relink Access tables from code at http://access.mvps.org/access/tables/tbl0009.htm
• J Street Technology — J Street Access Relinker at http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
• Peter Vukovic — Reconnect attached tables on startup http://allenbrowne.com/ser-13.html
• Utter Angel — Boilerplate database at http://www.utterangel.com/UtterAngel/utterangel.aspx?cat=acc (scroll down)
• UtterAccess Code archive — ReLinker with multi-BE, Label Table description to show connect info at http://www.utteraccess.com/forum/ReLinker-multi-BE-Label-t1680238.html
• UtterAccess Code archive — Relink/swap between Live, Test, and Local data at http://www.utteraccess.com/forum/Relink-Swap-Live-Test-t1328573.html

It's important that you know the environment you are working in. Entering test data into the production environment is, to put is nicely, less than desirable.

Visual cues in the FE files provide an effective flag for you and other users. For example, you can change the caption and the background color of a label on UI forms when the FE file is connected to a non-production environment. The visual cues can be a single color scheme (Red = Non-PROD), or you can use a color system (Red = DEV; Yellow = QA; and so on).

One method is to read the value of “database name” from a configuration table in the database and display it in the caption of every form. Or, using the value, you can code for the background color of a textbox or a label. The following code is an example of changing the backcolor of a textbox based on the value of the textbox bound to the configuration table (code file: Deploy_Maintain.txt Change Textbox BackColor based on Textbox value):

Select Case Me.txtBE
    Case "Prod"
        Me.txtBE.BackColor = vbGreen
    Case "Test"
        Me.txtBE.BackColor = vbYellow

Case "Dev"
        Me.txtBE.BackColor = vbRed
    Case Else
        Me.txtBE.BackColor = vbBlue
End Select

Another way to determine the environment you're working in is for you is to find the location of a linked table. Either one of the three code samples that follow will work, depending on the information you want to use.

CODE FILE: DEPLOY_MAINTAIN.TXT PF_FINDPATH

Option Compare Database
Option Explicit

Public Function pf_FindPath(ByVal strTableName As String) As String

'------------------------------------------------------------------------------
' Function  : pf_FindPath
' Author    : DougY
' Date      : 4/1/2013
' Purpose   : Return the full location of the BE for a linked table,
'             including the name of the BE file
' Arguments : strTableName = Name of linked table
'------------------------------------------------------------------------------

pf_FindPath = _
    Mid$(DBEngine.Workspaces(0).Databases(0).TableDefs(strTableName).Connect, 11)

End Function

CODE FILE: DEPLOY_MAINTAIN.TXT PF_FINDFOLDER

Public Function pf_FindFolder(ByVal strTableName As String) As String

'------------------------------------------------------------------------------
' Function  : pf_FindFolder
' Author    : DougY
' Date      : 4/1/2013
' Purpose   : Return the folder of the BE for a linked table
' Arguments : strTableName = Name of linked table
'------------------------------------------------------------------------------

    Dim strPath As String
    Dim lngCNT As Long

strPath = _
        Mid$( _

DBEngine.Workspaces(0).Databases(0).TableDefs(strTableName).Connect, 11)

    For lngCNT = Len(strPath) To 1 Step -1

        If Mid(strPath, lngCNT, 1) = "" Then
            pf_FindFolder = Left$(strPath, lngCNT)
            Exit For
        End If 'Mid(strPath, lngCNT, 1) = ""

    Next lngCNT

End Function

CODE FILE: DEPLOY_MAINTAIN.TXT PF_FINDNAME

Public Function pf_FindName(ByVal strTableName As String) As String

'------------------------------------------------------------------------------
' Function  : pf_FindName
' Author    : DougY
' Date      : 4/1/2013
' Purpose   : Return the name of the file for a linked table
' Arguments : strTableName = Name of linked table
'------------------------------------------------------------------------------

    Dim strPath As String
    Dim lngCNT As Long

    strPath = _
      Mid$( _
       DBEngine.Workspaces(0).Databases(0).TableDefs(strTableName).Connect, 11)
    pf_FindName = Mid(strPath, InStrRev(strPath, "") + 1)

End Function

You can change the backcolor of a label or even the form based on the returned value from any of the preceding functions.

Either of the two methods to determine the environment you are working in, using configuration table or using the code, will help you and other developers and analysts to know which BE environment is linked to the FE file you open.

Promoting Files from Test to Production

The final stage of the project is promoting the database solution to the production environment. Depending on the magnitude of the changes, if it's the initial release or a release of a major upgrade, it can be referred to as a “go-live” event.

Regardless of the magnitude of the release, before you move a new version to the production environment, you must have your client's (the business owner's) approval.

You want to ensure you have the approval for a few reasons. The first is that the approval of the business owner also means the business agrees that all changes you made meet the specifications approved in the requirement and analysis stages of the project.

Additionally, depending on the nature of the business, your client may be subjected to an annual audit by external auditors to meet regulatory requirements. Auditors look closely at the implementation of checks and balances to protect the integrity of financial data of the business. The fact that developers can make changes to the system is balanced by the fact that there must be testing and explicit approval by the business to move the changes to production.

As the developer, you should document the requests for the changes and the source for the requests. You should also document the testing phases both by you and by the UAT testers. Finally, you should have the approval to promote the revised files to production well documented for each project.

With smaller clients, you benefit from the same procedures even if the company is not subjected to regulatory audits. It will help you maintain a good relationship with your client and allow the client to be in control of the promotion schedule.

Version Control

Version control, also known as revision control, is a way to manage changes to your solutions. Although always an important concept, it is downright necessary when you work with a team of developers on the same project.

The version control tool allows you to track the changes made to the solution or to the database, and to prevent a conflict between developers who try to work on the same file at the same time.

The version control software will allow only one person to check the file out at a time to make changes. Some version control systems work on the object level. Developers can check an object from the file and work on the same file but different objects. The file — be it an object, the database, or the front-end solution — will need to be checked in before someone else can check it out. You can have one project broken down to a few files to allow different developers to work on different features of the project concurrently.

Many types and styles of version control applications are available. The tools are split among three main categories:

1. Local data — The developers need to use the same computer system. This type of software often manages single files individually.
2. Client-server — The developers use a single, shared file repository.
3. Distributed — Each developer works with her local repository. Sharing the changes between the repositories is a separate step.

Each category has open source or proprietary tools you can choose from to meet your needs and your budget. Some are free and some require you to purchase a license, either for the company or for each user.

Here are a few tools for you to review. Although Microsoft Visual SourceSafe (VSS) is listed, you should be aware that it is not supported any longer. Its out-of-mainstream support and extended support is scheduled to end July 11, 2017. If you choose to use VSS, you may need to download it from a source other than Microsoft, such as CNET.

• Microsoft Team Foundation Server — http://msdn.microsoft.com/en-us/vstudio/ff637362
• OASIS-SVN — http://dev2dev.de/changelang-eng.html
• TortoiseHG — http://tortoisehg.bitbucket.org/
• Vault — http://www.sourcegear.com/vault/
• Microsoft Visual SourceSafe — http://msdn.microsoft.com/en-us/library/ms181038%28v=VS.80%29.aspx

DATA MAINTENANCE

After the database solution is deployed, it needs to be maintained. Maintenance of a database solution includes regular backup for both the database and the FE files, and periodic execution of the Compact and Repair (C&R) tool for Microsoft Access database to reclaim any space no longer needed.

You can perform both tasks, backup, and Compact and Repair manually. It is preferable that no user is in the database while the backup process is executed. Users must be out of the database when you perform the C&R process.

It is better, and easier, to have both tasks automated. They can be scheduled to run during the non-business hours of the business so they don't interfere with the users during normal business hours.

In the following sections, you will read about different options to automate the backup and the database maintenance processes.

Automating Backups

In large companies, the IT department is responsible for the backup process of the whole network. That means that if you place the database BE file and the master FE file on the network, it will be backed up without your having to do anything else.

You want to confirm with the IT department that the location you selected for the files is one that is included in the backup process. You will also want to work with them to identify the recovery process as it can vary from one company to another.

Even without an IT department, smaller companies may already have a backup process in place. You should work with the client to identify the process, and how you can include the database files in it.

If there is no process in place, it is up to you to help the business to create a backup process. The process may be limited to the database solution, or you can take the opportunity to help the business establish a process to back up all files important to the business.

Local Backup Versus Cloud Services

The backup files can be stored locally, on another network drive, on an external hard drive, or on a PC dedicated to the backup process and storage, or they can be stored in the cloud.

There are many backup applications suited for local backup processes. Windows computers come with a backup application from the get-go or you can download and use one of the many tools available online. All of the tools share some common features, including the ability to schedule the process, set the folder and files to back up, and set the location of the backup files.

You may want to add the security of an off-site backup location to the process. This way, if a disaster were to strike, and the physical location of the business were also impacted, a copy of the backup files are available to start the business recovery process.

Nowadays, a few cloud-based options are available to consumers and businesses alike. There are different types of services with different price points, starting at no cost at all for a small storage space. When you evaluate the products, consider also the security of the data on the cloud servers. The data should be encrypted and protected from prying eyes.

The following are cloud-based tools to get you started. Entering the search term, “cloud based backup solutions” into your favorite search engine will yield more results for you to review:

• Carbonite — http://www.carbonite.com
• Mozy — http://mozy.com/

Although not exactly intended as cloud backup solutions, both Microsoft SkyDrive and Google Drive allow you to save files to the cloud. You can sync files from a selected folder on your PC or laptop to the cloud and have the files available to you when needed, including in case of a catastrophic failure of your local machines.

• Microsoft SkyDrive — http://windows.microsoft.com/en-US/skydrive
• Google Drive — https://www.google.com/intl/en_US/drive/start/features.html

Batch File on Windows Task Scheduler

One way to automate a backup process for the database solution is to use a batch file along with the built-in Windows Task Scheduler.

The VB Script that follows copies the specified database file to a specified location, in this case the same folder, and compacts the database. You will read more about Compact and Repair in the following section (code file: Deploy_Maintain.txt Copy Compact Repair):

Option Explicit

Dim WshShell
Dim objFile
Dim objFSO
Dim objAccess
Dim strPathToDB
Dim strMsg

Dim strTempDB
Dim strFile
Dim strPath
Dim strDate

strPathToDB = "\COFFEEUsersShakshokaDesktopTestCnR_Test.accdb"
strTempDB = "\COFFEEUsersShakshokaDesktopTestCompTemp.accdb"
strPath = "\COFFEEUsersShakshokaDesktopTest"
strFile = "CnR_Test"
strDate = Year(Date) & MonthName(Month(Date)) & Day(Date)

Set WshShell = WScript.CreateObject("WScript.Shell")
Set objFSO= CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(strPathToDB)

' For Access 2013, use Application.15
Set objAccess = CreateObject("Access.Application.14")
' Perform the DB Compact into the temp accdb file
' (If there is a problem, then the original accdb is preserved)
objAccess.DbEngine.CompactDatabase strPathToDB ,strTempDB

If Err.Number > 0 Then
    ' There was an error. Inform the user and halt execution
    strMsg = "The following error was encountered while compacting database:"
    strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else

    ' Back up the original file as FilenameDATE.accdb. In case of undetermined
    ' error, it can be recovered by simply renaming the file
    objFSO.CopyFile strPathToDB , strPath & strFile & "_" & strDate & ".accdb",
True
    ' Copy the compacted accdb by into the original file name
    objFSO.CopyFile strTempDB, strPathToDB, True

    objFSO.DeleteFile strTempDB

End If

' Clean up
Set objAccess = Nothing
Set objFSO = Nothing

You create a task with the Windows Task Scheduler to execute the VBScript file at a time when it works for the business. This method requires the machine with the task file to remain powered on. Although for most Scheduler tasks the machine does not need to be logged in, you should test it to make sure when you create a new task.

Using a Zip File

Another method for you to explore is to zip the files into a compressed folder, using the default Windows Zip program, 7-Zip, or WinZip. You can call either of the programs via VBA-code to send files to a compressed folder or to retrieve files from the compressed folder.

With this method, you can have multiple backup copies of the database file. If you need to restore the file, you can select which file to use. Additionally, because the file is saved in a Zip folder, even if users can see the folders, chances are high they will not accidently use the backup file instead of the production copy.

Ron de Bruin, an Excel MVP, provides good sample code for all three programs on his site: http://www.rondebruin.nl/win/section7.htm. It is worth reading through the samples to choose the one that works for you best if you decide to use Zip files.

Although the default Windows Zip program and 7-Zip are free to use, remember that to use WinZip you need to have a registered copy of the program.

Automating Maintenance

Unlike SQL Server, ACE database does not self-compact to release unused disk space. To reclaim the space, you run the Compact and Repair (C&R) process in Access.

The C&R process is actually two processes. In older versions of Access, you could run them separately. Beginning with Access 2000, both processes run together when you use Access to start the Compact process. You can still run each process separately if you use the command line to execute the tasks. You can read about the command line switches in this Microsoft KB article: http://support.microsoft.com/kb/209207.

As time goes by, both the FE and the BE may gain size, especially if you included temporary tables in the solution that you load and truncate on a regular basis. The C&R process will shrink the size of the Access file. Because Access has a file size limitation, the process is important for proper maintenance of the file and for improving the performance of the solution.

The C&R process does the following to the database file:

• Recovers space used by deleted or renamed objects and records.
• Updates both table and index statistics. This helps the query optimizer to choose the most efficient query plan when executed.
• Physically re-orders the records in the tables in accordance with the clustered index set for the table.
• If the process finds structural inconsistencies in the database, it repairs it.

A regularly scheduled C&R will help minimize database corruption, and will provide you and your users with a better experience when you use or support the database solution.

Compact on Close

Access offers you the ability to Compact on Close. Every time you close the database file, the C&R process will kick in. Although it may sound like a great solution, it is better if you avoid this feature.

When the database is set to Compact on Close, it can add significant time to the closing process of the database. It can also use a large chunk of the workstation resources. The added time and the load on the system resources will slow users from moving on to the next task and will impact productivity; something businesses frown upon.

Additionally, Compact on Close only works on the FE file. It does not work on the linked BE database file. You need to manage the maintenance of the BE independently of the maintenance of the FE file.

Remote Compact and Repair

Another method you can use is to compact and repair the database remotely. This section discusses two possible ways based on code developed by fellow Access developers.

You can find the first tool in UtterAccess forums Code Archive: http://www.utteraccess.com/forum/Auto-compact-backup-t678947.html. This tool allows you to list the database files you want to compact and repair and set different options, including creating sequential copies of the compacted database.

Because it's a code sample, you may need to clean it after you download the file to make it work for you. But with a little effort, you can have a tool to manage and maintain your database solutions.

The second option is to use an Access add-in to help you perform the tasks. The .mda file, Compactor.mda is available to you at http://access.mvps.org/access/modules/mdl0030.htm. The original .mda was written by Dev Ashish. This version works only in Access versions up to Access 97. The second, newer .mda file, available on the same web page, was modified by Peter Doering, Access MVP, and can be used with newer Access versions.

Both use the same concept. You save the .mda file in a folder — for example \CoffeeMyMDA — and you execute the following code in VBA (code file: Deploy_Maintain.txt Call MDA file):

Call Application.Run("\CoffeeMyMDACompactor2003.DoCompact", True)

To use it in a distributed database solution, add the preceding code somewhere in your code. Make sure that it is the last line of code in the procedure because anything after may not be executed. What the add-in does is close your current database file, run the Compact and Repair on it, and open it again. You may have to wait for a few seconds before it starts the job, depending on the size of your database. Make sure you've saved all Dirty objects before the line is executed to avoid data loss.

Descriptive. Edited. DougWindow Scheduler

Much like the backup process, you can use the same VBScript from the previous section to compact only the selected database file.

This section of the code is the one that compacts the database into a temp database:

' Perform the DB Compact into the temp accdb file
' (If there is a problem, then the original accdb is preserved)
objAccess.DbEngine.CompactDatabase strPathToDB ,strTempDB

When the C&R is done, this section of the code will copy the temp database file over the original database file with the same name:

' Copy the compacted accdb by into the original file name'
objFSO.CopyFile strTempDB, strPathToDB, True

When you execute this script, you can take care of both tasks, backup and C&R, in one task. If the backup is handled by another process or tool, you can modify the VBScript to keep only the C&R steps sans the backup.

Third-Party Solutions

You can also use third-party solutions to help you maintain the database files. Much like other tools mentioned in this book, you should do your own research to identify the tool that fits best with your or your client's business.

Review the following tools to see if they fit your business needs:

• Access Autopilot — http://www.databasedev.co.uk/compact_and_repair.html
• FMS Total Visual Agent — http://www.fmsinc.com/microsoftaccess/DatabaseCompact.html

How to Kick Users Out of the Application

Sometimes you may need to have all users log out of the database solution in the middle of the workday to perform maintenance. In a small office environment, it is pretty easy to ask everyone to log off until further notice. However, in a larger environment, perhaps one that spans over multiple locations, that may not be feasible.

The knowledge base article at http://support.microsoft.com/kb/304408 walks you through the steps of shutting down an Access database solution gracefully. After everyone is out, you can continue with the tasks you need, like the C&R process, or updating the BE file.

You can use the same approach when you want to close a database solution that was left open overnight.

OWNERSHIP OF CODE

This section provides you with high-level information about what's involved when you develop a database solution as an employee or as an independent contractor, and the possible implications on intellectual property rights. This information is meant only as talking points, not as legal advice. It is always wise to consult a lawyer in your area for legal advice about your specific situation.

You will also read about how to deploy a trial solution to allow potential clients to evaluate the product you designed.

Intellectual Property Rights

As a developer, you might work in various employment statuses. You may be an employee for a company, or you might be an independent contractor, either self-employed or through a placement company.

As you develop database solutions, the question that often needs to be addressed is who owns the intellectual property rights (IPR) to the database solution. Each employment status will trigger different discussion points and can lead to a different owner of the IPR.

Employee

In most cases, when you work for a company as an hourly or salaried employed, the code you develop during work hours belongs to the company you work for. Some companies will have you sign an agreement stating that fact when they hire you; some may not. However, even without a signed agreement, the company still maintains rights to the code. Any exceptions will need to be addressed explicitly by both the company and the employee and preferably up-front before there is a need to address a potential problem. For example, no one on the Access Development Team working for Microsoft owns the IPR for Access. Because they are employed by the company, everything they develop on company time is owned by Microsoft. The EULA you sign when you install Access is with Microsoft, not the Access Development Team.

Independent Contractor

Things can get interesting when you are contracted to develop a database solution for a client. As a contractor, you are your own company (putting the actual legal structure of your business aside) so you may own the products you develop. However, when a client hires you to build a database solution, the client has a reasonable expectation of ownership of the product.

Here are a few questions to ponder as you embark on negotiating a contract with a client:

• Can the client modify the code after you deploy the solution?
• Can the client hire another developer to modify the solution?
• Can the client sell or give a copy of the database solution to someone else?
• Does the client need to pay an annual licensing fee?

The answers to those questions will help you define the terms of the contract and set a clear expectation of ownership up-front. Whatever you and the business client agree on should be part of the written contract.

It can be beneficial to consult a lawyer before you start talking with prospective clients to help you create a standard contract for you and your company.

Trial Version

Not all the database solutions you design will be a custom application. Sometimes you develop a database solution as a product that you can license or sell to many potential clients.

If that's the case, you may want to offer potential clients the option to try the product before they commit to the purchase. There are two ways you can limit the usage of the database solution: time-based and record-based.

Whichever way you choose to use, remember that in most jurisdictions, the data in the trial version belongs to the users and you cannot prevent them from keeping or retrieving it.

Record Limited Preview

You can limit the demo product to allow only a certain number of records in a table or tables. For example, if you develop a Contact database, you can set it so that the potential client can enter no more than 10 records in the main table.

You can control that by checking for the number of records when the form is loaded. If it reached the maximum number you set for it, the solution will close or be set as read-only. You can use a DCount() function on a startup event to test for the number of records (code file: Deploy_Maintain.txt Trial - Number of records):

 'Place the variable in a standard module

'Set this number as appropriate for each product type or customer
Public Const lngMaxRecordsAllowed as long = 5

'Place this code in the startup code

If dcount("ContactID", "tblContacts")> lngMaxRecordsAllowed Then
    MsgBox "The preview period has ended.", vbCritical
    <code to quit, open solution as read only, or take them to "buy me" page>
End If

In this way, you can allow the user to edit current records but prevent him from using the database solution to its full capacity.

Time Limited Preview

The other method is to set a time limit on using the database solution. In its simplest way, the application compares the current computer date to a fixed start date embedded in the application. If the date is past the fixed expiration date, the code will set the application to read-only or will close it. The code snippet that follows illustrates this method (code file: Deploy_Maintain.txt Trial - Timed):

 'Place the variable in a standard module

'Set this date as appropriate for each customer
Public Const dtEndOfPreview as Date = #5/29/2013#

'Place this code in the startup code

If Date > dtEndOfPreview Then
    MsgBox "The preview period has ended.", vbCritical
    <code to quit, open solution as read only, or take them to "buy me" page>
ElseIf Date > DateAdd("d", -10, dtEndOfPreview) Then
    MsgBox "Warning: Preview is about to expire.", vbIconExclamation
End If

Releasing the Full Version

If the client decides to purchase the database solution product, the simplest way to unlock the trial version is to send the client a full-version FE file without the use-limitation code. Depending on your setup, the client may be able to download the FE file from your website.

There are other, more elaborate, ways to unlock a trial solution, including having the database file connect to your server to verify the status of the application: Trial, Expired, or Full. Either way you choose to limit the trial version, make sure to communicate to the user that it is a trial version and that is will expire when the limits are reached.

SUMMARY

In this chapter, you read about how to evaluate and create a security module in your database solution. The module enables different users to have different experiences with the same solution to meet and accommodate business needs and rules. The design and implementation of an audit trail can also be part of the security feature of your solution. Not all databases require audit trails. The ones that do may require different levels of granularity, depending on the industry, regulations, and business rules.

You also read about various deployment techniques and methods to update a revised FE file on users' workstations to ensure a unified version in production and an easier support for the solution.

You reviewed what different environment you should establish to support the development cycle. We discussed the benefits of multiple environments and how to go promote the files from one environment to the other at the end of each development stage. Optional environments such as SUP, Adhoc, and POC offer you additional options to provide support and to have analysts create adhoc queries and reports to support the business.

The discussion about database maintenance presented ways for you to provide ongoing support to your product. Responsibility to the maintenance plan can be an integral part of your role in the company. Automation of tasks allows you to focus on enhancements and user support while ensuring the health of the database solution.

You read through a high-level review of the intellectual property rights issue, and you now have the basic information you need to begin a discussion about how you want to provide services to a client. As mentioned, it is wise to consult a lawyer in your area to make sure you understand and secure your rights.

Next, you will read about how to work with SQL Server. As we mentioned a few times, Access and SQL work well together and you will find that by working with both, you can develop robust and powerful database solutions that can support a large number of users. Chapter 21 discusses the upsizing process and techniques to help you when you work with SQL Server.