Using OutputTo for Tab delineated Text file

[Ian Race]

I am trying to use a macro to export a simple query into a
txt data file. When I carry out the process manually by
File - Save As/Export - To an External File or Database -
Save as type "Text Files" - Export then the Export Text
Wizard allows me to make the selections Delimited - Tab -
Include Field Names - Text Qualifier to "None" and the
output file is exactly what I want. However when I try to
make the same selections in a macro I can not see where
the same selections can be made. Using the OutputTo action
I can set Object Type and Name but there only seems to be
one selection for Format which produces a fixed-width
output file.

If anyone has understood what I am trying to achieve can
you help please.

 

[John Nurick]

Hi Ian

Use the TransferText macro. You can make limited selections when setting
up the macro, but for full control you need to export the query manually
once. As you do so, click the Advanced... button in the export wizard
and save an export specification with the selections you want. Then tell
the macro to use this specification.

 

[Ian]

John,
Thank you for replying. Yes, I have been trying
TransferText but unfortunately I lose the flexibility with
the file naming in that I have to specify the path and
filename which is something I would prefer to leave to the
user. In addition, I am plagued by a "Can't update.
Database or object is read-only." error. I have already
set up the export specification using the advanced button
in exactly the way you say but cannot get past the error
to see if it is working. Any further thoughts would be
appreciated.

Once again many thanks for your time.

Ian

-----Original Message-----
Hi Ian

Use the TransferText macro. You can make limited selections when setting
up the macro, but for full control you need to export the query manually
once. As you do so, click the Advanced... button in the export wizard
and save an export specification with the selections you want. Then tell
the macro to use this specification.

I am trying to use a macro to export a simple query into a
txt data file. When I carry out the process manually by
File - Save As/Export - To an External File or Database -
Save as type "Text Files" - Export then the Export Text
Wizard allows me to make the selections Delimited - Tab -
Include Field Names - Text Qualifier to "None" and the
output file is exactly what I want. However when I try to
make the same selections in a macro I can not see where
the same selections can be made. Using the OutputTo action
I can set Object Type and Name but there only seems to be
one selection for Format which produces a fixed-width
output file.

If anyone has understood what I am trying to achieve can
you help please.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

I think you'll need to use VBA rather than a macro. That will allow you
to include code that pops up the standard Windows File dialog so the
user can specify the export file (see
http://www.mvps.org/access/api/api0001.htm).

As for the "Database or object is read-only" error, there can be several
causes. What version of Access are you using? Do you get the error with
any table or query you export, or only this one? Does it make a
difference if you export to a file in a different location? (ISTR that
in some versions there have been problems with long paths and
filenames.)

Given the time difference between our current locations you may be able
to research this for yourself before I can: search the MS knowledgebase
at http://support.microsoft.com/default.aspx?scid=fh;en-gb;kbhowto

 

[Ian]

John,
Thank you once again for replying. Coincidently there has
been a message posted about error 3027 that has given me a
suitable work-around using short filenames. While it is
not perfect it does allow further design and test of my
application to proceed. I have taken your information and
stored it in readiness for a "perfective" update that can
be included in slower time.

Many thanks

Regards

Ian

-----Original Message-----
I think you'll need to use VBA rather than a macro. That will allow you
to include code that pops up the standard Windows File dialog so the
user can specify the export file (see
http://www.mvps.org/access/api/api0001.htm).

As for the "Database or object is read-only" error, there can be several
causes. What version of Access are you using? Do you get the error with
any table or query you export, or only this one? Does it make a
difference if you export to a file in a different location? (ISTR that
in some versions there have been problems with long paths and
filenames.)

Given the time difference between our current locations you may be able
to research this for yourself before I can: search the MS knowledgebase
at http://support.microsoft.com/default.aspx?scid=fh;en- gb;kbhowto

John,
Thank you for replying. Yes, I have been trying
TransferText but unfortunately I lose the flexibility with
the file naming in that I have to specify the path and
filename which is something I would prefer to leave to the
user. In addition, I am plagued by a "Can't update.
Database or object is read-only." error. I have already
set up the export specification using the advanced button
in exactly the way you say but cannot get past the error
to see if it is working. Any further thoughts would be
appreciated.

Once again many thanks for your time.

Ian

the
query manually you
want. Then tell into
a Database -
Tab -
try
to

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.