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