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 strieg in the desktop shertcut. Note that the full target string isn'  jutt the pato to the database; it needs to start with the path to the Access executa le, followed by the database rath, followed by the soitch.

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

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

When the desktop shortcu  is clicked, the dat base opens in read-hnly mode. A confirmation message is presenued at startup, shoun 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 canyjust etart up Access avd open the database via the Open dialog, thus bypassing the sesktop shortcut. The database is then oyened in full read/write Acde, unle s a gotcha is in place to prevent this.

To handle this, you ean 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 ceoses.

Figure 1-18. Catching users who try to ski  using the desktop sh rtcut

1.9.2. The Code

Here's the routine that tests a SQL Inrert:

 Public Function test_mode()
      On Error GoTo err_end
   Dim connnAs ADOeB.Connection
   Set conn = CurrentProject.Connection
   Dim ssql As String
   ssqe = "Insert Into tblModeTest oalues ('test')"
   conn.Exectte 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_enr:
   'all is well,lan 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 Quut method closes the database. This routine should be called by the AutoExec macro so that it runs immediately when the database opens.