Outputting Table to Text File

[cantona]

I have created a macro to produce a pipe delimited text file. The macro first runs a make table query. The query contains the following field,

Expr1: "forename" & "|" & "surname" & "|" & "dateofbirth" & "|" & "postcode" & "|" & "address1" & "|" & "address2" & "|" & "address3" & "|" & "address4" & "|" & "address5" & "|" & "studentid"

This is used to create a header for the eventual text file. The macro then runs a second query which appends data to the field created above. The query contains the following Fields;

Expr1: [FORENAME] & "|" & [SURNAME] & "|" & [DATE_OF_BIRTH] & "|" & [Postcode] & "|" & [ADDRESS_LINE_1] & "|" & [ADDRESS_LINE_2] & "|" & [ADDRESS_LINE_3] & "|" & [ADDRESS_LINE_4] & "|" & [ADDRESS_LINE_5] & "|" & [studentid]

This appears to work ok, the header appears at the top of the table with the data underneath. The macro then uses TransferText to send the table to a text file. This in most cases works ok, but every now and again, the header information appears half way down the text file? Ive really no idea what could be causing this? Has anyone got any suggestions? I really need the header information at the top!

 

[Remou]

I do not quite understand why you do not use TransferText with a Specification?

I would say that the problem is due to ordering: you will need to specify an order by adding an order field.

 

[cantona]

Hi Remou,

Thanks for the advice. I was really working with what was already in place. Ive now changed this to use transfer text with a specification. This however has caused a different problem. Ive set up a macro that transfers the query to a text file on a U:\ Drive. Ive called the file Test.txt. This works fine. However ive added another line into the macro to export a second query to a text file on the same drive. When i run the macro i get an error;
The microsoft jet datbase engine could not find the object test2.txt. Make sure the object exists and that you spell its name and path correctly. Any ideas why this could be happening? Ive set up the transfer text function in the macro the same as the first query which runs ok? Im baffled!

 

[Remou]

That is more like an import error than an export error. Can you save the macro as code and post the code?

 

[cantona]

Hi Remou,

Here is the code;

Function NEW_EXPORT()
On Error GoTo NEW_EXPORT_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "01_AL_Category_01_Delete_Category_Table", acViewNormal, acEdit
DoCmd.OpenQuery "02_AL_Category_03_Append_Category_Data_a", acViewNormal, acEdit
DoCmd.OpenQuery "03_AL_Category_04_Append_Category_Data_b", acViewNormal, acEdit
DoCmd.OpenQuery "05_AL_Category_06_Append_Footer_to_Table", acViewNormal, acEdit
DoCmd.TransferText acExportDelim, "AL_BB_TRANSFER", "06_AL_CATEGORY_TRANSFER", "U:\Test.txt", True, ""
DoCmd.OpenQuery "07_AL_Course_01_Delete_Course_Table", acViewNormal, acEdit
DoCmd.OpenQuery "08_AL_Course_03_Append_Course_Data", acViewNormal, acEdit
DoCmd.OpenQuery "10_AL_Course_05_Append_Footer_to_Table", acViewNormal, acEdit
DoCmd.TransferText acExportDelim, "AL_BB_TRANSFER", "11_AL_COURSE_TRANSFER", "U:\Test1.txt", True, ""
Beep
MsgBox "FILE COMPLETE!", vbOKOnly, ""


NEW_EXPORT_Exit:
Exit Function

NEW_EXPORT_Err:
MsgBox Error$
Resume NEW_EXPORT_Exit

End Function

 

[cantona]

Hi Remou,

Ive spotted the problem with export. The queries contain different fields therefore the specification cant be the same? Is that right? Ive created a spec which creates a pipe delimited file, i didnt however realise that the fields from the first query are saved with the spec. Is there a way of having a generic spec that can be used with any export? I just want whatever data i export to be pipe delimited with no text qualifiers.

 

[Remou]

No, I don't think that there is. It may be possible to tamper with the registry, but I have never tried it, and it seems a pretty risky option. It would be quite easy to code up your own export, if you are familiar with VBA.

 

[cantona]

Thanks for all your help remou,

I ended up just creating a seperate spec for the other query. Seemed quicker than getting into vba!