Being able to create HTML and ASP files is only the first step; step two involves getting your files onto a web server. Many people use third-party FTP clients to upload the files to the server. In addition to the third-party software it is also possible to actually FTP files from within Microsoft Access. There are several ways to do this.
Those of you with the Office 2002 Developers edition can use an ActiveX Control called the Internet Transfer Control. This control allows you to perform simple file transfer using FTP or HTTP protocols.
However, a pure Microsoft Access approach is also available. Dev Anish and Terry Craft have made a free addin available which will permit you to transfer files to the Internet without the use of ActiveX controls. This feature has the advantage that, when distributing your database, you do not need to worry about ActiveX controls on the user PC. You simply include the FTP library with your database and set a reference to it. This is discussed in Chapter 13. We have included both the database and the library files on the book CD ( FTP2002 is the MDB file, and InetTransferLib the library file containing the required modules. Further information is available from The Access Web site at http://www.mvps.org/access/ . A ReadMe file has also been included which provides some examples of usage. The library exposes two objects: HTTP which is used to download from the server, and FTP which is used to both upload and download files. Note that some default FTP and HTTP clients have been added to the code in the example database. You will need to change these in order to get the tool to function.
Using the library is a straightforward process, but make sure you view the ReadMe file for more information and instructions on providing this functionality in your own database. Make sure you set a reference to the MDA library file before actually trying it out.
Before anything else let's install the files.
Create a new folder on your PC.
Copy the files FTP2002 and InetTransferLib.mda from the chapter section of the CD-ROM to the new folder.
Open the FTP2002 database file.
Within the FTP2002.mdb file, open a new module.
Click Tools References.
Click the Browse button and navigate to InetTransferLib.mda.
Select InetTransferLib.mda and click OK .
That's the first few steps done. We have now installed the database and library files and set a reference to the library. The next step is to actually move some files.
This form offers you three choices:
FTP Upload calls TestFTPUpload() .This opens a file dialog and you can then select a file to transfer to the web server. Note that a dummy ftp location has been entered into the procedure. You will need to replace this with a valid server address. Until you do this the function will return an error.
FTP Download calls TestFTP() . This button will navigate to a specific FTP site and download a named file. For this example, I have set the path and file names within the procedure to ftp://ftp.microsoft.com/softlib/index.txt which is the Index file from the Microsoft FTP service.
HTTP Download calls TestHTTP().
Click the FTP Download button to open the standard windows Save dialog.
Enter a name for the downloaded file.
Click OK to save the file locally.
If you've looked at the Company table, then you've already seen a hyperlink field:
As you can see this field is designed to hold the web address of the companies and, because the field is a hyperlink, clicking on the field will launch your web browser to point at this web site.
Open the tblCompany table in design mode.
Change the Data Type property for the Email column from Text to Hyperlink . Make sure the Allow Zero Length property is set to Yes .
Save the changes and switch the table into datasheet view.
Find Dave's Dairy Produce and notice the Email and Web fields.
Although they look similar, we want one to behave like an e-mail address, launching our e-mail program when clicked, and one to behave like a web address, launching the web browser when it's clicked. Since there is nothing in the properties of the hyperlink field to identify this, we have to use the hyperlink itself, and that's where the protocols come in. The default protocol is http , so at the moment Access thinks the e-mail address is a web address. That means you can't just click in the column to edit the field, as this tries to jump to the hyperlink.
To get around this, click in the Email column for either of the empty records either side of the Dave's Dairy record, and use the cursor keys to move to the Email column for Dave's Dairy .
Enter the following into the Email column:
Click on the new mail hyperlink, and you'll see your mail program load with the e-mail address already in the To: field.
You can see that the Hyperlink column type is extremely useful, as it gives you, and your users, quick access to web pages and mail addresses. If you want to hide even more complexity from your users you can use the display part of the hyperlink to show different text. Rather than delete and retype the hyperlink, let's edit it in a different way.
With your mouse over the e-mail address column, click the right mouse button and select Hyperlink , and then Edit Hyperlink from the next menu. This brings up the Edit Hyperlink window:
If you now change the Text to display field and press the OK button, you'll see that what's displayed in the column is now the text you typed in, rather than the e-mail address.
You've seen how easy it is to use hyperlinks in tables, but you might be wondering how you can use them in your code. The Hyperlink data type is more like an object, with several properties and methods :
The main hyperlink address.
The Subject line if the hyperlink is an e-mail.
The Screen Tip or Tool Tip text to display for the hyperlink. This requires IE4 or later to work.
The sub-address of the hyperlink.
The visible text to show on the screen.
Add the hyperlink to the favorites folder.
Creates a new document associated with the hyperlink.
Follows the hyperlink, opening the program (browser, mail, and so on) associated with the hyperlink.
You've seen the Edit Hyperlink dialog, which allows all of the values for a hyperlink to be edited, but you may not want your users using this form. After all, it looks fairly confusing and has more information than the user will require. This process, creating a new form, simplifies the process for the end user. Let's make a more user-friendly form for editing hyperlinks.
Open frmCompany in design view, and add two small buttons to the right of the Email and Web fields. You might need to widen the form a bit to get the buttons on:
Name these buttons cmdEmail and cmdWeb . If you make these as small as the diagram, then you might need to reduce the font size to make the dots show up 7-point seems to work well.
In the Click event for cmdEmail add the following code (make sure you get the number of commas correct there are five of them).
Private Sub cmdEmail_Click() DoCmd.OpenForm "frmHyperlink", , , , , acDialog, "Email" End Sub
In the Click event for cmdWeb , add the following code:
Private Sub cmdWeb_Click() DoCmd.OpenForm "frmHyperlink", , , , , acDialog, "Web" End Sub
Save and close this form. Now create a new form and add three textboxes and four text buttons:
The textboxes should be named txtDisplay , txtAddress , and txtSubAddress , and the command buttons cmdClear , cmdTest , cmdCancel , and cmdOK .
Set the Record Selectors and Navigation Buttons properties for the form to No . Now save the form as frmHyperlink .
Create a code module for the form by pressing the code button, and add the following global variable:
Option Compare Database Option Explicit Dim m_ctlHyperlink As Control
Now we'll place code for each of our command buttons in their respective event procedures. First, in the Click event for cmdCancel , add the following:
Private Sub cmdCancel_Click() DoCmd.Close End Sub
Next, in the Click event for cmdOK , add the following:
Private Sub cmdOK_Click() m_ctlHyperlink = txtDisplay & "#" & txtAddress & "#" & txtSubAddress DoCmd.Close End Sub
Now add the following line to the Click event of the cmdTest button:
Private Sub cmdTest_Click() m_ctlHyperlink.Hyperlink.Follow End Sub
Next, add this code to the Click event of the cmdClear button:
Private Sub cmdClear_Click() txtDisplay = "" txtAddress = "" txtSubAddress = "" End Sub
To wrap up the procedure, add this code to the Load event for the Form :
Private Sub Form_Load() Set m_ctlHyperlink = Forms!frmCompany.Controls(OpenArgs) With m_ctlHyperlink.Hyperlink txtDisplay = .TextToDisplay txtAddress = .Address txtSubAddress = .SubAddress End With End Sub
Save the module, switch back to Access, and save and close the form.
Now open frmCompany in form view, and view the record for Dave's Dairy .
Press the button alongside the Mail field, which will bring up the Edit Hyperlink form below
Enter the information into the textboxes as shown below:
Just press the Test button to launch the mail program.
So with just a few lines of code, you've made a simpler form for editing hyperlinks and made the link a lot more readable.
How it Works
We've done two things here. The first has been to modify the company form, because that's where the hyperlinks are shown. The second has been to create a new form to allow editing of those hyperlinks. Let's first look at the new code in the company form, behind the two buttons that open the hyperlink form:
DoCmd.OpenForm "frmHyperlink", , , , , acDialog, "Email" DoCmd.OpenForm "frmHyperlink", , , , , acDialog, "Web"
You've seen forms being opened before, but this format does require a little explanation. The last two arguments are the ones we're interested in. The arguments specified by the commas tell Access to simply use the default argument. DoCmd.OpenForm takes the following arguments: FormName , View , FilterName , WhereCondition , DataMode , WindowMode , OpenArgs . acDialog tells Access that the form is to be opened modally, as though it were a dialog form that means that you have to close the form before you can continue. The last argument is a string that gets passed into the hyperlink form, and is used to identify which control we are editing the hyperlink for.
Let's now look at the code for the hyperlink form. Firstly there's a global variable, m_ctlHyperlink , which is a Control . This will point to the actual control on the calling form this will be either the Email or Web address control.
Now for the Form_Load procedure. Firstly we set this global variable to point to the control on the previous form. We're using a special variable called OpenArgs here this contains the value that was passed in as the last argument of the OpenForm command shown earlier. In this case it's the name of a control, so we can use this to index into the forms Controls collection:
Set m_ctlHyperlink = Forms!frmCompany.Controls(OpenArgs)
Now we want to display the various parts of the hyperlink on our form. For this we refer to the Hyperlink property of the control, which allows us to access the various hyperlink elements.
With m_ctlHyperlink.Hyperlink txtDisplay = .TextToDisplay txtAddress = .Address txtSubAddress = .SubAddress End With
When the user presses the OK button we want to be able to update the hyperlink control. We can't use the Hyperlink property and its elements as we did when the form was opened because the individual elements are read-only, but we can combine the elements together, using the hash symbol to separate them.
m_ctlHyperlink = txtDisplay & "#" & txtAddress & "#" & txtSubAddress DoCmd.Close
The Clear button simply clears the values from the form fields. This saves the user having to clear them individually.
The last piece of code is for the Test button, when we want to launch the application associated with the hyperlink. For this we use the Follow method of the Hyperlink property.
That's all there is to it! An easier form, with only a few lines of code. Although we've only shown this code working for the e-mail address, it works the same way for Web addresses. Why not try adding some real Web URLs to see it in action?