By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,320 Members | 2,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,320 IT Pros & Developers. It's quick & easy.

Export Query as Fixed Length Text File

100+
P: 171
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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Apr 17 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
The second argument to DoCmd.TransferText would be the name of your specification. You should also see it listed under your Tables.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acExportFixed, "myExportSpec", "qryFFRDeFile", "C:\qryFFRDeFile.txt", True
Apr 17 '09 #2

NeoPa
Expert Mod 15k+
P: 31,560
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.
Apr 17 '09 #3

100+
P: 171
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
Apr 19 '09 #4

NeoPa
Expert Mod 15k+
P: 31,560
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.
Apr 20 '09 #5

100+
P: 171
Ok, I may not be following the steps probably, I will try again, following your steps.
Apr 21 '09 #6

NeoPa
Expert Mod 15k+
P: 31,560
Let me know if that doesn't work for you. I remember being quite confused when I first noticed that happen.
Apr 21 '09 #7

100+
P: 171
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
May 4 '09 #8

NeoPa
Expert Mod 15k+
P: 31,560
Very pleased to hear it :)

That means I'm still sane.
May 5 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.