Access Export vs Output

[buckwheat]

I created a macro to output a query as "txt" format, the results differ from using the "right'click export" method in that the macro txt is not delimited and the "export" method is . . . is there something special that needst be done to get the same result as the "export" method?

[JoeM]

Is it fixed width or delimited?
Which Macro Action are you choosing?
What arguments are you using with that action?

[buckwheat]

Hi Joe,

Where do I look for fixed width or delimited?
The Macro Action is "Output"
The arguements are "Object Type = Query; Obj Name = Export Survey File; Output Format = Text files(*.txt) No selection for Encoding

(Access 2003)

[JoeM]

Try using the "TransferText" Action instead of "Output". That is the one that mirrors the manual export action of a text file.
Note that you will probably need to save an Export Specification to use with the TransferText action.
Creating one is easy.
If you manually go through the process of exporting, you will work through the Export Wizard. When you get to the last step of the Export, before clicking "Finish", click on Advanced, and then "Save As" as give the Export Specification a name. This is the name you will want to use in the Export Specification argument of the TransferText action.

[buckwheat]

Ok, I went through and created everything for the TransferText. I copied and pasted a text file and removed the records so I could use this as a template. The macro runs, but returns an error that it cannot update. Database or object is read-only . . which it is not and then it deletes the txt template.

[JoeM]

I copied and pasted a text file and removed the records so I could use this as a template.

I am not sure I understand. The process should export your data to a new text file.
Where is this "template" coming in to play?

What are your arguments for your TransfersText action (that is, what are you filling in for these values)?

[buckwheat]

Transfer Type = Export Delimited
Specification = Export as delimited text
Table Name = qryFinalPatientEmailList7
File Name = full path of where file is located. The action argument states to "Enter the full path of the text file to import from, export to, or link to. Required argument.

I thought this process was similar to the TransferSpreadsheet function that relates to an existing spreadsheet. I wanted to create a new text file and is why I selected the Output method.

[JoeM]

Your arguments look OK.
TransferText transfers to a new text file, not an existing one. If you choose the name of an existing file in the "File Name" argument, I think it will simply overwrite the old one.

It sounds to me like maybe you got that error message because you were trying to overwrite an existing file, but maybe you had that file open at the time (or someone else did). You cannot overwrite a file that is currently open/in use.

[buckwheat]

A little confusing.. . this requires a File Name . . . won't execute without it.

[JoeM]

Of course it does. Every file needs a filename. It needs to know where to save it.
If you choose an unused file name, it will save it as that. If you choose the same name each time, it will keep overwriting the file.

If you want different file names each time, you can convert the Macro to Visual Basic and make a dynamic file name (i.e. maybe something that incorporates a date/time stamp).

[buckwheat]

I tried all approaches. . . a name that did exist was deleted.. . and converting to VBA and a new name of a file . . . returned the error message I mentioned above, yet Access was not opened as a "read-only" and if it is a new excel workbook, how can a read-only error message happen?

[JoeM]

If you are exporting from Access to a Text file, where does Excel come in?
Can you open the original query you are exporting from without any issues?
Can you manually export the query without any issues?

The only other thing I can think of is that your Access database is corrupt, in which case you might need to repair or import all the objects to a new database.

[buckwheat]

Ok thanks for your help.

[JoeM]

If you still cannot figure it out, you can always upload your database and we can take a look at it.
Just let us know the name of the Macro you are trying to run when you upload it.

[buckwheat]

Sorry Joe, I neglected to answer your questions: 1) yes qry exports fine manually; 2) didn't mean to say excel workbook, mean to say txt file. I think you're correct regarding the db being corrupted, I've had a lot of other issues that are very odd. Thanks again.