Integrating, Tagging, Printing and Expanding

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Dec-2002 >

Integrating, Tagging, Printing and Expanding


Peter Vogel


In this month’s Access Answers column, Peter Vogel looks at

replacing perfectly good Access functions, having multiple

utilities share a control’s tag property, printing PDF files from

Access reports, and the issues around creating an expanding/

contracting form. He also disappoints at least one reader.


My Access 2.0 application used the TransferSpreadsheet

command for many long and successful years. I even

upgraded the command from a macro to a VBA command

(using the DoCmd object). The TransferSpreadsheet

command survived successive upgrades through Access

95/97/2000. We’ve just moved the code to an Access Data

Project and I can no longer use TransferSpreadsheet. Am

I doomed?


You’re not doomed, though you’ll have to abandon the

TransferSpreadsheet command. But you’re on the verge of

taking advantage of one of the most powerful commands

in Access 2002 (though this new command has been

available since Access 97, at least). Don’t panic, though;

the change in your code is minimal.


By the way, you can keep using TransferSpreadsheet

but not the way that you’re doing it now. As long as

you stay away from Access Data Projects, you can

use TransferSpreadsheet with tables or queries. Once

you move to ADPs, queries disappear and are replaced

with views, functions, and stored procedures.


TransferSpreadsheet won’t work with these objects.

Instead, you get a mem (misleading error message) that

Access can’t find the specified view, function, or stored

procedure. It’s not that Access can’t find them; the

problem is that TransferSpreadsheet won’t work with

those objects.


I’ve seen a number of different solutions for this

problem, all of them too complicated. One solution is to

use SQL Server’s Data Transformation Services (DTS),

while another solution exports the data to a table in

another database and then exports the data from there.

There’s a much simpler method in Access 2002: OutputTo.

This powerful and flexible method gives you all the

capabilities of the TransferText and TransferSpreadsheet

methods of the DoCmd object, and much more.


The format of the OutputTo method looks like this:


DoCmd.OutputTo objectType, objectName, outputFormat, _

outputFile, autoStart, templateFile, encoding


To output your ADP view, you’ll need to set the

objectType parameter to acFormatServerView.

The real power in this command is in the

outputFormat parameter. There’s no IntelliSense support

for this parameter, so you’ll have to look it up in the

Access Help system (or guess at the options from the

dropdown list in the Access Macro Editor, which does list

all the options). There are eight options for this parameter:


• acFormatHTML: HTML

• acFormatText: Text files

• acFormatASP: Active Server Pages

• acFormatXLS: Microsoft Excel

• acFormatIIS: Microsoft IIS

• acFormatSNP: Access Report Snapshots

• acFormatRTF: Rich Text Format

• acFormatDAP: Data Access Page


The dropdown list in the Access Macro Editor

lists three options for Excel—Excel, Excel 5-7, and Excel

97-2002—but, in Access 2002, the output seems to be

identical for all three versions. The list in the Macro Editor

also varies depending on the type of object you select (the

Snapshot format only appears if you’re outputting a

report, for instance), which provides some guidance on

which options can be used together. I also found that the

method was more reliable from code than from a macro.

For instance, I wasn’t able to create an RTF file when I

used OutputTo from a macro, but I had no problem with

doing that from code.


The acFormatXLS option is obvious: It produces an

Excel spreadsheet. Two of the other options are interesting

but would only be used during design time rather than

being used at runtime:


• The acFormatASP option produces an ASP page

with ADO code that reads the table and displays the

data in a table. The code isn’t complete though, as

it’s missing the connection string to connect to

the database.

• The acFormatIIS produces the skeletons of the IDC

and HTX files that you’d need to integrate the output

with Microsoft’s search engine, Index Server.

Presumably, you’d use these two versions of the  ...


Read more here:

Integrating, Tagging, Printing, and Expanding