Export Query as Fixed Length Text File

[iheartvba]

Hi I am using Access 2007 and am trying to export a query to a fixed length text file. I tried using the following code to export the text file:

DoCmd.TransferText acExportFixed, , "qryFFRDeFile", "C:\qryFFRDeFile.txt", True

Access asks for a specification name, now here lies the challenge, how do I create an export specification I have tried the following 2 methods:

In access 2007:
Step1: I right clicked the query and chose export to text file
Step2: I entered the location I wanted it exported to as C:\qryFFRDeFile.txt
Step3: Saved the Export Specification and Pressed ok to export
Result: It exports... and thats it, I can access the saved specification in the saved imports section under the external data tab, but can't use it in the vba code listed above (I get an error saying the specification doesn't exist), even if I could call it in the code, it's not the specification I want because I haven't determined the fixed lengths.

In Access 2003:

So now I go and try the same thing in Access 2003, when I right click export on the query, it doesn't even bring up the export wizard, it just asks me for the location and exports it.

Now if that wasn't enough, there are 3 parts to the fixed length file all three of which have different lengths and number of columns. So the solution would be if I could export the first part, then export the second part to the same file, but instead of overwriting the file it would append the query to the first part that had previously been exported, and the third part would be exported in the same manner.

Please help (Not that I needed to say that)

Thanks Guys!

[ChipR]

The second argument to DoCmd.TransferText would be the name of your specification. You should also see it listed under your Tables.

DoCmd.TransferText acExportFixed, "myExportSpec", "qryFFRDeFile", "C:\qryFFRDeFile.txt", True

[NeoPa]

To create an Import/Export Specification you should export the data via the manual process first. It may be easier to use a table properly pre-configured for this the first time.

1. Select File / Export.
2. Select Type of Text files.
3. Enter (or select) a File name.
4. For this one it helps to use the extension .txt.
5. Click on Export button.
6. Select Fixed Width.
7. Click on Advanced... button.
8. Select the options required on that screen first.
9. Next, go down to the Field Information section and look at the columns.
   1. Select columns across from the left to the right. Ensure the mouse moves beyond the right border to catch any hidden columns there.
   2. Double-click on a column separator to show all hidden (width=0) columns.
   3. Repeat for any columns at the left.
10. Now configure each field as required.
11. Click on Save As....
12. Enter your new specification name.

This specification is now available for use in your code.

[iheartvba]

Hi ChipR AND NeoPa,
Firstly ChipR please read after my transfertext code, I can't actually create the Export Specification.

NeoPa, actually I do know how to create the export specification like you showed me in access 2003 (not in access 2007 though). The problem is that this only occurs if the export wizard runs I.E. the box that pops up and allows you to do steps 6-11. It used to come up automatically but now access just exports the report after step 5. It doesn't make sense. Also if someone knows how to save and then programatically call an export specification in Access 2007 please let me know.

Thank you

[NeoPa]

Are you following all the instructions precisely? I have had a similar issue, but if I follow those steps it uses the wizard (including specifying the source as a table to start).

I have no info on, or experience with, Access 2007 though.

[iheartvba]

Ok, I may not be following the steps probably, I will try again, following your steps.

[NeoPa]

Let me know if that doesn't work for you. I remember being quite confused when I first noticed that happen.

[iheartvba]

heheh, following the steps in Post # 3 worked, I am so sure I did the same thing before and it didn't work, it even works in Access 2007.

Thanks Mate

[NeoPa]

Very pleased to hear it :)

That means I'm still sane.