Export Query to Text File

[Guest]

Using Access 2003, is it possible to have a query create a text file with no
user intervention? Thanks

 

[Allen Browne]

Sure. Create a macro using the TransferText action.

 

[Guest]

Allen:

thanks for the quick assistance, and I checked your webiste and it looks
like a valuable resource. can you point me to a good explanation of macro
creation/transfer text?

thanks

 

[Allen Browne]

It's very simple:

1. Choose the Macros tab of the Database window.

2. Click New.
Access opens a new macro.

3. On the first row, choose the TransferText action.

4. In the lower pane, set:
Transfer Type Export Delimited
Spec {leave this blank}
Table Name {name of your table or query here}
File Name C:\MyFolder\MyFile.txt {or whatever}

5. Save the macro.

6. Set the On Click property of a command button to the name of your macro.

 

[Guest]

Allen:

thanks for the ample code - worked great. but I need to create a fixed
width text file, and when I make that checge I get an error message requiring
the Spec parameter. can you help, again? thanks

 

[Allen Browne]

Pretend you are manually exporting the file.
While running the export wizard, click the Advanced button.
This takes you to a screen where you can save the specification.

You can now use that spec name in the macro.

 

[Guest]

Allen:

I'm sorry to keep bugging you, but I don't get the export wizard when I try
to export the query manually. Searched with Google for some help, no luck so
far. thanks again.

 

[Allen Browne]

1. In the Database window, select the query.

2. Choose Export on the File menu.

3. In the file dialog, set the Save as Type drop-down to:
Text Files
Enter a file name, and click Export.

4. In the Export Text Wizard dialog, click the Advanced button.

 

[Guest]

Allen:

the problem is that the Export Text Wizard doesn't start

 

[Allen Browne]

Did you skip step 3?

 

[Guest]

Allen:

finally got it - I was running the query then trying to export it. my bad.
thanks for all of your EXTREMELY valuable help and time!

 

[Guest]

Allen:

are you available for another question associated with this topic? I have
an issue with some of the formatting of the data. Thanks.

 

[Allen Browne]

Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

 

[Guest]

Allen:

My issue involves a dollar amount field that must be a fixed length with
leading zeroes and no character for the decimal point. ("000000043274" for
$432.74). The data is formatted OK in my query, but in the text file the
leading zeroes are lopped off and the physical decimal point is inserted
("432.74")

So it seems like it's happening during the export process, and I'm unable to
figure out how to define the field in the export wizard to get what I need.

thanks again
--
Bill Gable

Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

are you available for another question associated with this topic? I have
an issue with some of the formatting of the data. Thanks.

 

[Allen Browne]

How about:
Format(100 * [Amount], "000000000000")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

My issue involves a dollar amount field that must be a fixed length with
leading zeroes and no character for the decimal point. ("000000043274" for
$432.74). The data is formatted OK in my query, but in the text file the
leading zeroes are lopped off and the physical decimal point is inserted
("432.74")

So it seems like it's happening during the export process, and I'm unable
to
figure out how to define the field in the export wizard to get what I
need.

thanks again
--
Bill Gable

Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

are you available for another question associated with this topic? I
have
an issue with some of the formatting of the data. Thanks.

 

[Guest]

will try. thanks

--
Bill Gable

How about:
Format(100 * [Amount], "000000000000")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

My issue involves a dollar amount field that must be a fixed length with
leading zeroes and no character for the decimal point. ("000000043274" for
$432.74). The data is formatted OK in my query, but in the text file the
leading zeroes are lopped off and the physical decimal point is inserted
("432.74")

So it seems like it's happening during the export process, and I'm unable
to
figure out how to define the field in the export wizard to get what I
need.

thanks again
--
Bill Gable

Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

are you available for another question associated with this topic? I
have
an issue with some of the formatting of the data. Thanks.

 

[Gary Sharp]

Have an almost identical project. Getting an error message: Cannot update. Database or object is read-only. Any help with what this is referring to, and how to fix? Cheers!

A Womand Told Me
http://www.awomantoldme.com

 

[Gary Sharp]

Have an almost identical project. Getting an error message: Cannot update. Database or object is read-only. Any help with what this is referring to, and how to fix? Cheers!

A Womand Told Me
http://www.awomantoldme.com

 

[Gina Whipp]

Gary,

Did you copy your files to a CD and then paste it back to a computer? Files
copied from a CD are pasted Read Only. Open Windows Explorer, right click on
the file, open Properties and uncheck Read Only under Attributes (at the
bottom of the General tab).

 

[Michel Walsh]

Maybe the file is read only (check with the Operating System). That may
happen if the file has been copied from a read only CD, as example. You may
have open the application in a read only mode too, either explicitly, either
it was already open in design mode when you opened it.


Vanderghast, Access MVP