Microsoft Access VBA Programming for the Absolute Beginner

Exports a database object in a variety of formats.

Syntax

DoCmd.OutputTo ObjectType[, ObjectName][, OutputFormat][, OutputFile][, AutoStart][, TemplateFile][, Encoding]

with the following parameters:

ObjectType

An AcOutputObjectType constant indicating the type of object to export. Possible values are acOutputForm (a form object), acOutputFunction (a SQL Server user-defined function), acOutputModule (a code module), acOutputQuery (a query object), acOutputReport (a report object), acOutputServerView (a SQL Server view), acOutputStoredProcedure (a SQL Server stored procedure), and acOutputTable (a table object).

ObjectName

The name of the object to export. If omitted, Access exports the active object of type ObjectType.

OutputFormat

An AcFormat constant indicating the export format. Possible values are acFormatASP (Active Server Page), acFormatHTML (HTML file), acFormatIIS (IIS .htx or .idc file), acFormatRTF (RTF file for Word or other word processors), acFormatTXT (plain text file), and acFormatXLS (Excel workbook file).

OutputFile

The path and filename of the output file.

AutoStart

A Boolean that determines whether the application that handles OutputFile should open and load the file. Its default value is False.

TemplateFile

The path and filename of a file to serve as a template for exported ASP, HTML, and IIS .htx files.

Encoding

A Boolean indicating whether the export file is to be encoded.

Example

The following code exports the customer table in each of the supported formats except XML:

Public Sub ExportTable() DoCmd.OutputTo acOutputTable, "tblCustomer", acFormatASP, _ "C:\BegVBA\Customer.asp" DoCmd.OutputTo acOutputTable, "tblCustomer", acFormatHTML, _ "C:\BegVBA\Customer.html" DoCmd.OutputTo acOutputTable, "tblCustomer", acFormatIIS, _ "C:\BegVBA\Customer.htx" DoCmd.OutputTo acOutputTable, "tblCustomer", acFormatRTF, _ "C:\BegVBA\Customer.rtf" DoCmd.OutputTo acOutputTable, "tblCustomer", acFormatTXT, _ "C:\BegVBA\Customer.txt" DoCmd.OutputTo acOutputTable, "tblCustomer", acFormatXLS, _ "C:\BegVBA\Customer.xls" End Sub

Comments

• The acOutputDataAccessPage constant is documented but not supported as a value of the OutputFormat argument.

• The acFormatDAP and acFormatSNP constants are documented but not supported by the DoCmd method.

• You can generate an XML file (possibly along with an XSD file) by omitting the OutputFormat argument and selecting XML from the Output To dialog box. (XML does not have an intrinsic constant in Access 2003.)

• If the OutputFormat and OutputFile arguments are omitted, Access prompts for the export file type and export filename, respectively.

• In the case of ASP and IIS files, OutputFile seems to require a file extension; otherwise, Access adds an appropriate extension if it isn’t already present.

• If AutoStart is True, the application launched to handle the exported file is defined by the system registry.

• The AutoStart argument is ignored for IIS and ASP files.

• Generally, the exported files appear to be much more robust than in some previous versions of Access. I was unable to get IIS .htx/.idc files to work at all, and ASP pages required minor tweaking (an ADO connection string had to be added to the call to the Connection object’s Open method), but all remaining exported files accurately reflected the Access data and did not require modification.

• Except for an ASP page, which uses ADO to retrieve live data from the database, all exported files contain static data; they provide a snapshot of the data at a given point in time.