Uploading Data

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.

Try it outUsing the FTP Database to Download a File

Before anything else let's install the files.

1. Create a new folder on your PC.

2. Copy the files FTP2002 and InetTransferLib.mda from the chapter section of the CD-ROM to the new folder.

3. Open the FTP2002 database file.

4. Within the FTP2002.mdb file, open a new module.

5. Click Tools References.

6. Click the Browse button and navigate to InetTransferLib.mda.

7. Select InetTransferLib.mda and click OK .

8. 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.

9. Open frmTestCases

10. 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().

    1. Click the FTP Download button to open the standard windows Save dialog.

    2. Enter a name for the downloaded file.

    3. Click OK to save the file locally.

How to Use Hyperlinks

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.

Try It OutUsing Hyperlink Fields

1. Open the tblCompany table in design mode.

2. Change the Data Type property for the Email column from Text to Hyperlink . Make sure the Allow Zero Length property is set to Yes .

   

3. Save the changes and switch the table into datasheet view.

4. 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.

5. 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 .

6. Enter the following into the Email column:

    mailto:[e-mail] 

7. 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.

Hyperlinks in VBA

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 :

Item	Type	Description
Address	Property	The main hyperlink address.
EmailSubject	Property	The Subject line if the hyperlink is an e-mail.
ScreenTip	Property	The Screen Tip or Tool Tip text to display for the hyperlink. This requires IE4 or later to work.
SubAddress	Property	The sub-address of the hyperlink.
TextToDisplay	Property	The visible text to show on the screen.
AddToFavorites	Method	Add the hyperlink to the favorites folder.
CreateNewDocument	Method	Creates a new document associated with the hyperlink.
Follow	Method	Follows the hyperlink, opening the program (browser, mail, and so on) associated with the hyperlink.

Custom Hyperlink Form

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.

Try It OutEditing Hyperlinks

1. 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:

   

2. 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.

3. 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 

4. In the Click event for cmdWeb , add the following code:

    Private Sub cmdWeb_Click()   DoCmd.OpenForm "frmHyperlink", , , , , acDialog, "Web"   End Sub 

5. Save and close this form. Now create a new form and add three textboxes and four text buttons:

   

6. The textboxes should be named txtDisplay , txtAddress , and txtSubAddress , and the command buttons cmdClear , cmdTest , cmdCancel , and cmdOK .

7. Set the Record Selectors and Navigation Buttons properties for the form to No . Now save the form as frmHyperlink .

8. 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   

9. 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 

10. Next, in the Click event for cmdOK , add the following:

     Private Sub cmdOK_Click()   m_ctlHyperlink = txtDisplay & "#" & txtAddress & "#" & txtSubAddress     DoCmd.Close   End Sub 

11. Now add the following line to the Click event of the cmdTest button:

     Private Sub cmdTest_Click()   m_ctlHyperlink.Hyperlink.Follow   End Sub 

12. Next, add this code to the Click event of the cmdClear button:

     Private Sub cmdClear_Click()   txtDisplay = ""     txtAddress = ""     txtSubAddress = ""   End Sub 

13. 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 

14. Save the module, switch back to Access, and save and close the form.

15. Now open frmCompany in form view, and view the record for Dave's Dairy .

16. Press the button alongside the Mail field, which will bring up the Edit Hyperlink form below

17. Enter the information into the textboxes as shown below:

    

18. 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.

 m_ctlHyperlink.Hyperlink.Follow 

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?