The SUBST DOS command is all you need to copy the network drive to your development machine. On your development machine, you can use SUBST to create a map that matches the one users need.

First, create a directory on your computer that matches the folder on the share where the back-end database will go. If the network path includes subdirectories, create a path that matches that path structure on your development machine.

The syntax for using SUBST requires the new drive letter and the path it is set to, like this:

	SUBST <New Virtual Drive Letter:> <Path to map to that letter>

For example, if you have a subfolder named XYZ_Corp in your C:Clients folder, and you want to map that folder to an S: drive, click Start/Run; type command (Windows 98, Me) or CMD (Windows NT, 2000, XP); click OK; and enter this at the command line:

	SUBST S: C:ClientsXYZ_Corp

Figure 6-1 shows how you do this in the Command Prompt box.

Figure 6-1. Using SUBST

If users are accessing a subfolder under the S: drive, create matching folder names under the folder that was substituted to the S: drive. Place the back-end database in the appropriate folder.

Now, when you are in the front-end database file and are linking the back-end tables, browse to your new S: drive to find the back-end database, thereby keeping the links the same as what the users need. When you send your users an update, they should not have to relink anything. This new drive letter will even show up when you open My Computer. The new virtual drive letter will last until the next time you restart. If you decide you no longer need a virtual drive, you can get rid of it with the /d switch:

	SUBST S: /d

It’s important to note that this removes the virtual mapping; it doesn’t delete the folder or its files. Also, you can’t use SUBST if you already have a drive using that letter, so if your keychain flash drive is using G:, you have to safely remove it before SUBSTing a folder to use the G: drive.

Of course, this technique works only in extremely stable environments, where all users have the same drive letter mapped to the given share. Although S: might work for your primary contact, other users of the application might have a different letter mapped to that location, or they might not have any letter mapped. They could be accessing the folder through its Universal Naming Convention (UNC) name (\ServerNameShareName). If this is the case, you can emulate this on your PC as well as long as you are willing to rename your PC to match the server’s name (you can always change it back later).

If you want to use UNC instead, you need to rename your computer. First, you need to know the server name at the client site and the full path of folders and subfolders to the share that will hold your back-end datafile. To rename your computer to match the server, bring up your PC’s System Properties by right-clicking My Computer and clicking Properties, or by double-clicking the System icon in the Control Panel to open the System Properties dialog box. Select the Computer Name tab, and then click the Change button. When you see the Computer Name Changes dialog box, shown in Figure 6-2, type the desired name for the computer. It will require a reboot to take effect. Of course, this assumes you will be creating a name conflict on the network. The assumption is that your development machine isn’t on the production network. If it is, you can disconnect your computer during this process.

Figure 6-2. Changing the name of the computer

So, if XYZ Corp.’s server, called ServerName, has a DeptShare folder and a subfolder called DataFolder that will hold your datafile, change your computer’s name to ServerName. Then, create a folder named DeptShare off the root of your C: drive, and create a subfolder called DataFolder inside the DeptShare folder.

Once the folder structure is in place, browse to the DeptShare folder, rightclick in an empty area of the folder, and then click Properties. Select the Sharing tab, and make selections to share the folder, as shown in Figure 6-3.

Figure 6-3. Sharing a folder

Now go to My Network Places, and click Add a Network Place. Click Next on the wizard’s first screen, and the wizard will ask you where to create the new network place; select “Choose another network location.” Click Next, and in the Internet or Network Address box, type \ServerNameDeptShare. Click Next; Windows will ask what to call the share. If the name isn’t already in the box, type DeptShare for ServerName. Click OK, and then click Finish.

Finally, back in your Access application file, delete all the linked tables. This time, when relinking the tables, make sure to go through My Network Places/Entire Network in the link dialog to browse to the datafile, or type \servernamedeptshare into the dialog to browse to the datafile. This causes Access to create the links to use the UNC naming convention. If you use the My Computer shortcut to your share, Access recognizes that it is local and uses the C: drive path to create the link. To ensure that your link is using UNC, type this in the debug window:

	?Currentdb.TableDefs("<your table name>").Connect

Make sure to put the name of one of the linked tables in the code line where you see the <your table name> prompt. The response should look like this:

	;DATABASE=\ServernameDeptShareDataFolderProject_dat.mdb

Note that you will see the name of your database; you won’t see Project_ dat.mdb. If you get the following response, you need to try again, making sure you go through the entire network, workgroup, computer name, and share name when browsing to your datafile:

	;DATABASE=C:DeptShareDataFolderProject_dat.mdb

Once this is correct, end users at the client site shouldn’t need to relink, regardless of which drive letter (if any) they have mapped to the network location.

—Steve Conklin

Forms have an intrinsic timer control and Timer event. If you’re familiar with Visual Basic, you know how to actually place a timer control on a form. In Access, the control is effectively already on the form, although you don’t see it. Instead, you use the property sheet to set the Interval property and to indicate what occurs in the On Timer event.

To display the property sheet, open the form in Design mode, and press F4 on the keyboard. If necessary, make sure the property sheet is displaying properties about the form itself, not about one of the controls or sections. Select Form from the drop-down box at the top of the property sheet.

Figure 6-5 shows the property sheet set to display the properties for the form. The On Timer and Timer Interval properties are found on both the Event tab and the All tab.

The Interval property accepts values between 0 and 2,147,483,647 milliseconds. A setting of 1000 equals one second. The 10000 setting shown in Figure 6-5 is the equivalent of 10 seconds. By the way, the largest setting of 2,147,483,647 equals almost 25 days. Yes, you can schedule an Access event every 25 days!

The On Timer event property links to either a macro or a code procedure. In this example, a code procedure was written. I’ll explain the code soon, but first, let’s examine the form’s design.

Figure 6-6 illustrates the form design, field list, and property sheet. Notice the text box control in the form header that isn’t bound to a field. The property sheet is set to display the properties of the unbound box, txtTime, and its Visible property is set to No. In other words, when the form is in View mode, the txtTime text box won’t be seen.

Figure 6-5. Setting the Timer Interval and On Timer event

Figure 6-6. An unbound text box to hold a time reference

The txtTime text box isn’t meant to be used for entry. Instead, it holds a snapshot of the computer’s clock time, at the moment the form is activated. To make this happen, you need to enter a little code in the form’s Activate event.

To get to the event code stub, select [Event Procedure] from the drop-down menu to the right of On Activate in the property sheet and then click the ellipses (…) button, as shown in Figure 6-7. This brings you to the form’s code module, right at the start of the Activate event. How’s that for convenience?

Figure 6-7. Getting to an event code stub from the property sheet

Here is the code to enter in the Activate event:

	Private Sub Form_Activate( )
       Me.txtTime = Now
	End Sub

The On Timer event contains the DateDiff function, set to test for the difference in seconds between the established form activation time and the current time. From the property sheet, select [Event Procedure] in the drop-down menu to the right of On Timer. Click the ellipses button and enter this code:

	Private Sub Form_Timer( )
	If DateDiff("s", Me.txtTime, Now) > 5 Then
	   DoCmd.Close
	End If
	End Sub

The first parameter of the DateDiff function indicates which interval to test; in this case, s is for seconds. The function tests if more than five seconds have lapsed between the time stored in the txtTime text box and now. Bear in mind that there are two values to coordinate here: the timer interval and how many seconds to test for.

This example is set up to test every 10 seconds if there is a difference of five seconds, but you can change these numbers. For example, it might be easier on the user if the timer interval is 30 seconds. There is a balance of what makes sense here. If users are likely to edit the same records often, make the interval shorter.

If we stopped here, the form would close 10 seconds after being opened. That is, upon the first run of the On Timer event (which occurs 10 seconds after the form is opened) a difference greater than five seconds is found, and the DoCmd.Close line runs, closing the form. But our goal is to close the form only when there is no activity, not just for the heck of it.

The key to making this hack work is to add code to each change event for the various text entry boxes on the form. The form in this example has text boxes for editing employee name, department, title, and so on. The Change event for each text box receives a single line of code to update the txtTime text box with the current time. In other words, every time a change is made in an entry text box the txtTime text box (remember, this one is invisible) is reset to Now, like this:

	Private Sub Department_Change( )
       Me.txtTime = Now
	End Sub

The Change event fires each time a character is entered or backspaced out of the text box. Therefore, as a user types in one of the entry text boxes, the txtTime text box is constantly updated with the current time. Then, when the timer event fires, the DateDiff function returns a difference of less than five seconds, and the form stays open. Only when the form is left idle does a difference greater than five seconds occur, thereby closing the form.

Figure 6-8 shows how the form’s code module should look after these routines have been entered. It’s OK if your event routines aren’t in the same order.

Figure 6-8. The code that handles an inactive form

You can implement this hack in many different ways. So far, all we know is how to reset the baseline time each time a character is entered with the keyboard. Also, the only action after a period of inactivity has been to close the form. Here are some other ideas.

	Private Sub Title_MouseMove(Button As Integer, _
         Shift As Integer, X As Single, Y As Single)
       Me.txtTime = Now
    End Sub

Let the user decide the timer interval.

Each user has his own way of working, not to mention his own speed of working. So, instead of hardcoding the timer’s interval value, why not let the user decide what is best? To do this, you have to build a way to let him select the interval into the form (or somewhere else, such as in a preferences area). Figure 6-9 shows how the form has been modified by adding a combo box. The combo box lets the user select from a list of possible values.

Figure 6-9. Letting the user decide how long to wait before closing the form

The code is updated as well. The combo box has been named cmbSeconds. Its Row Source Type is set to Value List, and the Row Source is set to the choices 10, 20, 30, 40, 50 , and 60 . When the user selects a value from the combo box, the combo box’s Change event fires to update the invisible text box to the current time. Also, the form’s Activate event now takes care of establishing a default time to wait—20 seconds in this case, as shown in Figure 6-10.

It’s necessary to have a default value to use until or unless the user selects an interval. Finally, the number of elapsed seconds that are tested for is now always one fewer than the interval selected in the combo box. Figure 6-10 shows the updated code module.

Figure 6-10. Setting the Interval property with the combo box Change event

Save the record but leave the form open.

Just because a record is displayed in a form doesn’t necessarily mean it is being edited. The Dirty property is true if edits have been made or false if no data has changed. You can change the code in the form’s Timer event to test the Dirty property. If it is true , the record is saved, and a message is presented, as shown in Figure 6-11. If Dirty is false, and no edit is occurring, nothing happens. Either way the form stays open.

The new code for the Timer event uses a pair of nested If statements. First, if the elapsed time is greater than the predetermined interval of the test, the second If statement comes into play. The second If tests the Dirty property. If true , the record is saved, and the message is displayed:

Figure 6-11. The saved-edits message

	Private Sub Form_Timer( )
	If DateDiff("s", Me.txtTime, Now) > Me.cmbSeconds - 1 Then
      If Me.Dirty Then
		DoCmd.RunCommand acCmdSaveRecord
		MsgBox "Edits have been saved!"
      End If
	End If
	End Sub

If you started an edit and didn’t complete it, the code completes it for you. If an edit is initiated, no harm is done.

	Private Sub Form_Timer( )
	If DateDiff("s", Me.txtTime, Now) > 10 Then
	  If Me.Dirty Then
	    DoCmd.RunCommand acCmdUndo
		DoCmd.Close
      End If
	End If
	End Sub