ACC97: Save As/Export Does Not Allow Saving to an FTP Address (164005)

SYMPTOMS

Microsoft Access "Building Applications with Microsoft Access 97," Chapter 21, "Developing Applications for the Internet and the World Wide Web," page 620 states:

   You can export any Microsoft Access-compatible external data file to an
   FTP server.

The Microsoft Access 97 Help topic "Export a database object to an FTP Internet server" states:

   You can export a database object to an FTP server.

Both of these statements are incorrect.

RESOLUTION

If you want to export to an FTP server use one of the following methods.

Method 1

Export them to a location on your local hard drive or to a network server, and then copy the exported files to the FTP server using a utility such as the Microsoft Web Publishing Wizard.

Method 2

Use a Visual Basic for Applications procedure to export the object.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. This sample procedure exports the Shippers table as a Microsoft Excel spreadsheet and the Orders table as a text file from the sample database Northwind.mdb. Make sure that the FTP folder that you are exporting to has write permissions enabled or the export will fail.

Sub ExportToFTP()

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
     "Shippers", "ftp://servername/shippers.xls"
   DoCmd.TransferText acExportFixed, "Orders Export Specification", _
     "Orders", "ftp://servername/orders.txt"

End Sub

Method 3

Use a macro to export the objects. This sample macro exports the Shippers table as a Microsoft Excel spreadsheet and the Orders table as a text file from the sample database Northwind.mdb. Make sure that the FTP folder that you are exporting to has write permissions enabled or the export will fail.

   Macro Name   Action
   -------------------------
   Macro1       TransferSpreadsheet
                TransferText

   Macro1 Actions
   -----------------------------------------------------
   TransferSpreadsheet
      Transfer Type: Export
      Spreadsheet Type: Microsoft Excel 97
      Table Name: Shippers
      File Name: ftp://ServerName/Shippers.xls
      Has Field Names: Yes
   TransferText
      Transfer Type: Export Fixed Width
      Specification Name: Orders Export Specification
      Table Name: Orders
      File Name: ftp://ServerName/Orders.txt
      Has Field Names: Yes

MORE INFORMATION

When you try to export a database object to an FTP server, you receive the following message when you click Export in the Save As dialog:

   You cannot use an Internet address here. Enter a path that points to a
   location on your computer or on the network.

Steps to Reproduce Behavior

The following example demonstrates the behavior by exporting a Microsoft Excel file to an FTP server. The same results occur with any type of file.

Start Microsoft Access and open the sample database Northwind.mdb.
Select the Customers table in the Database window, and then on the File menu, click Save As/Export.
In the Save As dialog box, click "To an External File or Database," and then click OK.
In the "Save Table 'Customers' In" dialog box, select Microsoft Excel 5-7 (*.xls) in the Save As Type box, and then type a valid FTP address and file name in the File Name box, for example:
ftp://servername/customers.xls
Click Export and note that the error message appears.