Hack 8. Protect Valuable Information

Protect your data using the read-only command-line switch so that users can't edit the data.

Creating a desktop shortcut to a database provides a behind-the-scenes benefit. Specifically, you can use command-line switches that are unseen by all but the technically curious.

In this manner, it is easy to set up a shortcut to open a database in read-only mode and, thus protect your data and design elements. To do this, add the /ro switch at the end of the target string in the desktop shortcut. Note that the full target string isn't just the path to the database; it needs to start with the path to the Access executable, followed by the database path, followed by the switch.

Using Access 2003, which by default is in the standard Program Files/Microsoft Office/Office 11directory, the full target string looks like this:

	"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
	"C:\Sales Summaries\Sales2005.mdb" /ro

When the desktop shortcut is clicked, the database opens in read-only mode. A confirmation message is presented at startup, shown in Figure 1-17. Data can't be added, deleted, or edited.

This is a great way to disseminate information without concern for data integrity issues. Distributing the database application in such a way that a desktop shortcut is created or updated guarantees that the database opens in just the way you intended.

Figure 1-17. A reminder about the read-only status

1.9.1. But Just in Case

Of course, a half-savvy user can just start up Access and open the database via the Open dialog, thus bypassing the desktop shortcut. The database is then opened in full read/write mode, unless a gotcha is in place to prevent this.

To handle this, you can place a simple SQL Insert operation in the database's opening routine, and you can include an extra table in the database for just this purpose. If the operation succeeds, the user is warned to use the desktop shortcut (as shown in Figure 1-18), and the database closes.

Figure 1-18. Catching users who try to skip using the desktop shortcut

1.9.2. The Code

Here's the routine that tests a SQL Insert:

	Public Function test_mode()
      On Error GoTo err_end
	  Dim conn As ADODB.Connection
	  Set conn = CurrentProject.Connection
	  Dim ssql As String
	  ssql = "Insert Into tblModeTest Values ('test')"
	  conn.Execute ssql
	  conn.Close
	  Set conn = Nothing 
	  'if got this far then database is not in read only mode
	  'tell user, then quit
	  MsgBox "Database must be opened through desktop shortcut"
	  DoCmd.Quit
	  Exit Function
	err_end:
	  'all is well, an error was expected
	End Function

There is a twist to this: the database is supposed to be in read-only mode, so the optimal outcome is that the operation will fail instead of succeed. An error trap is implemented in the routine, so if the error is triggered, all is well, and no action is taken. If the insert succeeds, the warning is displayed, and the Quit method closes the database. This routine should be called by the AutoExec macro so that it runs immediately when the database opens.