Opening Databases with Shortcut Files



If our world were simple, there would be only one database to open and protect. In this world, we could install Access on an end user's computer and use the work- group administrator to join to a workgroup file. We could then train users to open Access and select the first database on the most recently used list, and DBAs would have a relatively simple time of it. Of course, the world isn't that simple, as most Access sites have many Access databases and sometimes more than one workgroup security file for each user . This section of the chapter shows you how to use special commands in shortcut files to circumvent these issues. It also shows a more secure way of using Access VBA code to open databases that are protected by workgroups, or even those that aren't.

Irrespective of your current development and users' workgroup arrangements, it is a good idea to understand how to use shortcut files to start Access because they will provide you with a quick way to open your database with a workgroup file. Unfortunately, if you don't get on top of the quick and simple ways to switch workgroups and databases, you will forever be gnashing your teeth when you or your users open a database while connected to the wrong workgroup file.

Using shortcut files is good because people only join the workgroup for the session. If you combine this measure with menu-specific startup options, protected menus , and toolbars (see Chapter 7), users will not as easily be able to open the back-end database by using the workgroup file. The shortcut file helps protect the back-end database because users have to close Access and, hence, lose their association with the workgroup file. In the Access help guide, shortcut commands are referred to as startup command-line options. Access has quite a number of these options, but for the purposes of protecting the database, the ones that I will cover follow:

  • /wrkgrp . Starts Access by using the specified workgroup information file.

  • /user . Starts Access by using the specified user name .

  • /pwd . Starts Access by using the specified password.

Creating a Shortcut File

If you want to create a shortcut file, you must first include the path to the Access executable on your computer. The way that I like to do this is to find the Access executable ( MSAccess.exe ), which is generally located in the Program Files folder on your computer. Now right-click the MSAccess.exe executable and choose Send To ˜ Desktop (Create Shortcut). (The sample demonstrated in Figure 10-14 is from Windows XP.) Now switch to your Desktop, which you can do quickly by pressing the Windows key and the D key together.

click to expand
Figure 10-14: Right-clicking the Access executable to send a shortcut to the desktop.

To customize the shortcut on the Desktop, right-click it and choose Properties, as shown in Figure 10-15. The first item that you want to add to the target line is the full path to the database, which should occur directly after the path to the executable.

click to expand
Figure 10-15: Modifying the target line of the shortcut by right-clicking the shortcut file.

Once the shortcut to the database works, you can start adding the commandline options. To demonstrate , I will show you a command line that will open a copy of the Northwind back-end database with a workgroup file and the Editor user (described in the section "Trusting Your Users by Adopting the Same Workgroup"). You should organize these commands together in the shortcut's target field. As you can see, after each argument is entered, you need to leave a single space before putting the required entry in the line. These shortcuts can be a bit cumbersome to build and test, so you should add each item one at a time.

 C:\MSOfficeXP\Office10\MSACCESS.EXE c:\data\Northwind_be.mdb /wrkgrp c:\developer.mdw /user editor 
Note  

If a workgroup file or database is in a folder that has spaces in the name, you will need to enclose the path to the file in double quotes.

Another thing that I like to do at sites where multiple users use the same computer is to enter the name of the user account that has the least permissions as part of the path. That way, even if the user may have found out the password of a higher-level account, the interface may persuade them to use the lower-level account anyway.

Shortcut Files Identify the Location of Files

The problem with shortcut files is that they expose both the location of the database and the location of the workgroup file. This visibility means that any user who has even a small amount of computer knowledge will be able to read the target line of a shortcut file. Therefore, if you don't take steps to protect the database folder (discussed in Chapter 12), these locations can be useful to someone who's trying to copy the database or workgroup file.

Secure (Personal) Shortcut Files

In Windows 2000 or Windows XP, it is possible to store your Desktop shortcuts in either a shared folder or in a personal folder. If you store the shortcut in a personal folder, those shortcuts will not be available to any other limited users of your computer or any other users of the local area network.

In essence, you can keep your shortcuts safe in a personal folder, such as

 C:\Documents and Settings\Your Name\Desktop 

where the folder \Your Name\ is the name of the account that you log on to. If your shortcut is kept in the folder

 C:\Documents and Settings\All Users\Desktop 

then your shortcut will be shared among all the users of your computer.

Therefore, I recommend that if you want your personal folders to be very safe, you use the personal folders and protect your computer accounts with passwords and time-outs.

Unfortunately, storing shortcut files in personal folders still does not provide much protection from the actual user of the shortcut file, because that user can still gain access to the target text string in the shortcut file. Windows XP security and how it relates to Access is described in more detail later in this chapter.

Creating a Secure Shortcut File

Though starting Access by using shortcut files is quick and easy and offers a temporary connection to the workgroup file, shortcut files are not encrypted. Any person who has any computer awareness can therefore view the properties of the shortcut file to reveal the location of both the workgroup file and the database itself. As I explained in the previous chapter, the location of your workgroup file can be a security issue if your users are not fully trustworthy.

Tip  

If you really want good security, combine the shortcut file method with the protected folders operating system instructions in Chapter 12, and you can make it very difficult for users to copy your databases.

To help you get around the problems caused by the shortcut files' target being visible, I have written some VBA code that uses the startup command-line directly from a form in another Access database. This approach, when used in a compiled MDE database (explained in the next chapter), will hide the location of the files.

In the first VBA code example from the form frmCmdLine ( shown in Figure 10-16), I demonstrate how to open the Northwind database directly from another database. To make this work on your computer, you need to change the FILENAME constant that points to the location of your database. The other part of the command-line string that you need is the location of the executable so that you can start Access, which you can is retrieve by using the SysCmd method. To launch Access, I use the VBA Shell method to open a new version of Access with a command line. This new version looks exactly like the target line property of the shortcut file when you look at the strShell string in the Immediate window.

click to expand
Figure 10-16: The frmCmdLine form, which allows you to mimic shortcut files securely.
 Private Sub cmdDBOpen_Click() ' Open a database. Const ACCESSEXE = "msaccess.exe" Const FILENAME = "C:\data\Northwind_be.mdb" Dim strFilePath As String, varAppID As Variant, strShell As String On Error GoTo err_cmdDBOpen strFilePath = SysCmd(acSysCmdAccessDir) If Len(Dir(FILENAME)) > 0 Then    ' Create the text target path and use Chr(34) to add inverted commas.    strShell = strFilePath & ACCESSEXE & " " & Chr(34) & FILENAME & Chr(34)    varAppID = Shell(strShell, vbNormalFocus) Else   MsgBox "Problem Opening Your Application. Contact your DBA", vbCritical, _          "Database Is Out of Action" End If ' Reinstate the following line once you have finished testing. ' DoCmd.Quit acQuitSaveAll exit_cmdDBOpen:    Exit Sub err_cmdDBOpen:    Select Case Err.Number       Case Else          MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical    End Select    Resume exit_cmdDBOpen End Sub 

In the second, slightly more complicated, example (see button 2 on the form frmCmdLine ), I demonstrate how you can open a database with a workgroup file and a workgroup user and password. Because this information may be sensitive, I recommend that you compile the database into MDE format. This example is an extension of that used in example one, in which the workgroup file, User account, and User password are also included in the strShell command line string.

 Private Sub cmdWrkOpen_Click() ' Open a workgroup-secured database. ' Open a database. Const ACCESSEXE = "msaccess.exe" Const FILENAME = "C:\data\Northwind_be.mdb" Const SECUREWRK = "C:\developer.mdw" Const SECUREUSER = "Developer" Const SECUREPWD = "Developer" Dim strFilePath As String, varAppID As Variant, strShell As String On Error GoTo err_cmdWrkOpen strFilePath = SysCmd(acSysCmdAccessDir) If Len(Dir(FILENAME)) > 0 And Len(Dir(SECUREWRK)) > 0 Then    ' Open the database while using Chr(34) to add inverted commas.    strShell = strFilePath & ACCESSEXE & " " & FILENAME & _               "/WRKGRP " & Chr(34) & SECUREWRK & Chr(34) & _               "/USER " & Chr(34) & SECUREUSER & Chr(34) & _               "/PWD " & Chr(34) & SECUREPWD & Chr(34)    varAppID = Shell(strShell, vbNormalFocus) Else    MsgBox "Problem Opening Your Application. Contact Your DBA", vbCritical, _           "Database Is Out of Action" End If ' Reinstate the following line once you have finished testing. ' DoCmd.Quit acQuitSaveAll exit_cmdWrkOpen:   Exit Sub err_cmdWrkOpen:    Select Case Err.Number       Case Else          MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical    End Select    Resume exit_cmdWrkOpen End Sub 

Now I will describe the other shortcut command-line switches that you can use to start Access plus offer some other advice on shortcuts.

Another Way to Switch Workgroups and User Accounts

When you work on multiple databases in different versions of Access like I do, even shortcuts can become a bit unwieldy. To counter this, I wrote a program called the Access Workbench to handle these circumstances in a more versatile way. If you look in Appendix B, you can find out how to download and register for a free version of this program.

Other Issues to Consider with Shortcut Files

You can use other helpful command-line switches in a shortcut file to open an Access database. All these switches, which will work with workgroup user name and password commands for secured databases, include:

  • /ro . Opens the specified database in read-only mode.

  • /compact . Works out to be the easiest way to compact a database if you are not actually in the database.

  • /repair . In Access 97, repairs the database. In later versions of Access, repairs and compact are combined, so this option is superfluous.

  • /x . Starts Access and runs a macro that is specified after the switch. This switch provides an alternative to using the AutoExec macro to start the database.

  • /cmd . Allows you to specify additional parameters that are used when you open the database.

Finally, here is some advice to the person who installs Access on your company networks: Always install Access and/or Office in the same directory on every computer in your company if you can, because it will make your shortcut files easier to maintain. It is also better to have a different directory name for each version of Office, irrespective of whether there is only one version of Office on the computer. The location of each of these versions of Access and Office should be consistent across your company computers.

Now I will introduce some background material for you to consider before we launch headlong into more complex data protection and security measures.




Real World Microsoft Access Database Protection and Security
Real World Microsoft Access Database Protection and Security
ISBN: 1590591267
EAN: 2147483647
Year: 2003
Pages: 176

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net