Access 2007 export to text file

I have a project where I need to pull in data from an sql table, then have the user be able to change some of the data. Then I need to have the user be able to export the data in a format that is required by another application. That format is very specific. It needs to be pipe delimited without the quote marks. I am new to access, but I thought this would be fairly simple. I created my database and the table using the sql data. That went fine. I created a query to build a single field with the fields in the correct format with the pipe delimiter. My problem has been the export. I tried exporting the query. I left Export data with formatting and layout unchecked. There are 2 options, delimited and fixed width. Delimited is almost good, except it has " marks at the beginning and the end. So I tried fixed width. The problem there is that for some reason it only shows the 1st 5 characters of each record. I don't know why the truncation. If this could be figured out, I think this might work. Any help would be appreciated. I am not a VB programmer.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Just send us the exported file if possible and we will be able to give you a better opinion on that.
Rey Obrero (Capricorn1)Commented:
<I created a query to build a single field with the fields in the correct format with the pipe delimiter.> DON"T do this

if you want to export certain fields from the table, create a simple select query.
save the query and export.
in the export process, choose the options you want
qbjgqbjgConsultantAuthor Commented:
I want all of the fields in the table, but I want them exported as pipe delimited with no quote marks in a .txt file. How do I get this without building the data as I want it?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Rey Obrero (Capricorn1)Commented:
export the table,
in the export wizard, choose the options you want
Rey Obrero (Capricorn1)Commented:
is this a one time process or repetitive ?
if repetitive, you need to use VBA codes to this

docmd.transfertext acexportdelim, "exportSpecification","tableName","c:\mytext.txt", true
qbjgqbjgConsultantAuthor Commented:
It looks like it is working. I chose delimited, then next and it let me remove the quotes. This will work, but it would be nice if there was a better way so it would be easier for the user.Query1.txt
Rey Obrero (Capricorn1)Commented:

see my post at http:#a37833032
qbjgqbjgConsultantAuthor Commented:
It will be done on a regular basis. I have never done any VBA code. How do I setup that Code:
docmd.transfertext acexportdelim, "exportSpecification","tableName","c:\mytext.txt", true
qbjgqbjgConsultantAuthor Commented:
That link did not work for me.
Rey Obrero (Capricorn1)Commented:
first you need to create an export specification

1.right click on the table export > Text file
   click on Browse and locate the destination folder
3. (you can accept the proposed name or change it)
click Save, then click OK
4. In the export text wizard select the type (Delim Fixed) width
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myText.txt", True

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qbjgqbjgConsultantAuthor Commented:
OK, I did all of that except the VB command. Where do I enter it? (OK, I know I am a Dummy)
Rey Obrero (Capricorn1)Commented:
create a form, add a command button (name it cmdExport) and use the click event of the button

private sub cmdExport_click()

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myText.txt", True

end sub
qbjgqbjgConsultantAuthor Commented:
OK, thanks. Creating a form was going to be my next task.
qbjgqbjgConsultantAuthor Commented:
It will take awhile to get the form done. I have a book I am following. You have been a tremendous help. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.