Importing, Linking, and Exporting Data on the Internet

With Microsoft Access, you can import or link data from HTML tables or other data sources on an Internet server. You can also export data in your database to an Internet server.

Importing and Linking Data from HTML Tables

You can import or link data formatted as an HTML table to a Microsoft Access database. Before Microsoft Access imports or links the data, it copies the data into the local cache. Whenever you open a linked table, Microsoft Access makes a local copy from the original on the Internet or an intranet before opening it. For this reason, the data in the table is read-only. Similarly, if you export the linked HTML table to an HTML file, Microsoft Access exports it to a local file, not the original file on the Internet.

Û To import or link data from HTML tables

  1. On the File menu, point to Get External Data, and then click either Import or Link Tables.
  2. In the Files Of Type list, click HTML Documents (*.htm; *.html).
  3. Specify the file to import or link from by using any of the following methods:
    • Use the Look In box and the list of files below it to browse through the file system on your local hard drive or LAN.
    • In the File Name box, type a valid http:// or ftp:// URL.
    • In the Look In box, click Internet Locations (FTP) and select a previously defined FTP site.
    • In the Look In box, click Add/Modify FTP Locations, then specify a new FTP site and browse its files.
  4. Click Import or Link.

    See Also   For information on linking HTML files by using Visual Basic, see “Working with HTML Files” in Chapter 18, “Accessing External Data.”

When you import or link data from an HTML table, Microsoft Access parses the information contained within the HTML tags. The primary HTML tags that define tables are:

HTML tag pair Description
<TABLE>...</TABLE> Specifies the beginning and end of the table.
<TH...>...</TH> Specifies table header cells.
<TR...>...</TR> Specifies a row in a table.
<TD...>...</TD> Specifies table data cells.
<CAPTION...>...</CAPTION> Specifies the table’s caption, usually at the beginning or end of the table.

Microsoft Access applies the following rules when it interprets the progression of tags and tag pairs within the HTML table it imports or links:

  • The <TD> tag pair may be closed with either a </TD> tag or a </TH> tag.
  • The <TH> tag pair may be closed with either a </TH> tag or a </TD> tag.
  • The <TR> tag is not required to start a new row. If a <TD> tag follows a </TR> tag, Microsoft Access assumes this is the beginning of a new row.
  • If a </TABLE> tag is not preceded by a </TR> tag, then Microsoft Access assumes this is the end of the row.

In many cases, a table cell can display something other than text. If a table cell contains an embedded graphic file, there is a <IMG SRC...> tag associated with it. This tag may or may not have additional text that would be displayed. If additional text is present, Microsoft Access imports it, but Microsoft Access doesn’t import the embedded graphic and the tag that defines it. However, this is not true of embedded anchor <A HREF> tags; Microsoft Access imports anchor tags as Hyperlink fields.

HTML tables can contain lists that are embedded within a table cell. Lists in an HTML table cell are formatted with the <LI> and </LI> tags. Microsoft Access inserts a carriage return and line feed (<CR><LF>) after each list item and imports each item in the list as a separate field for that record.

HTML tables can also contain tables that are embedded within a table cell. You can import these as separate tables.

To achieve the most predictable results, import simple HTML tables with a fixed number of fields per record, without embedded lists or tables.

Exporting, Importing, and Linking Data fromOther Data Sources on Internet Servers

You can export any Microsoft Access-compatible external data file to an FTP server. You can’t export files to HTTP servers through the Internet, because you can’t write to them using the HTTP protocol.

Û To export data from external data sources on Internet servers

  1. On the File menu, click Save As/Export.
  2. In the Save As dialog box, click To An External File Or Database, and then click OK.
  3. In the Save As Type box, select the type of file you want to export.
  4. Specify an FTP server by using any of the following methods:
    • In the File Name box, type a valid ftp:// URL.
    • In the Save In box, click Internet Locations (FTP) and select a previously defined FTP site.
    • In the Save In box, click Add/Modify FTP Locations, then specify a new FTP site and browse its files.
  5. Click Export.

You can also import and link any Microsoft Access-compatible external data file, except Microsoft Access databases and ODBC data sources, by using an FTP or HTTP connection. An FTP or HTTP connection is only maintained long enough to perform a single transaction. Therefore, when you import or link a file located on an FTP or HTTP server, Microsoft Access copies the data file to the user’s local cache. Microsoft Access then imports or links to the data file in the local cache. For this reason, files linked with an FTP or HTTP connection are read-only.

Û To import or link data from external data sources on Internet servers

  1. On the File menu, point to Get External Data, and then click either Import or Link Tables.
  2. In the Files Of Type list, select the type of file you want to import or link.
  3. To specify an FTP server, use any of the following methods:
    • In the File Name box, type a valid ftp:// URL.
    • In the Look In box, click Internet Locations (FTP) and select a previously defined FTP site.
    • In the Look In box, click Add/Modify FTP Locations, then specify a new FTP site and browse its files.

    To specify an HTTP server:

    • In the File Name box, type a valid http:// URL.
  4. Click Import or Link.