Tricks And Tips With Exporting Tbl/qry To Text

This is not a question.

I have recently been playing with exporting a query to a delimited text file and have learned some things along the way. Some of this is bound to be old-hat to some of you. However, I thought that it might be useful to post a summary of what I've learned for the benefit of others, like me, who may face some of the frustrations I've been dealing with.

Firstly, in order to export a query or table to a text format other than the default csv, you need to create what is called an 'export specification'. As has been mentioned elsewhere (http://www.access-programmers.co.uk/forums/showthread.php?t=59828&highlight=export+wizard)on this forum, you do this by clicking on the advanced button on the 'Export Wizard'. Pick the delimiter you prefer, click save as, and save the specification with a name that you can remember.

To use the specification in an export, you use the specification's name as the second argument in the DoCmd.TransferText method. (See access help for more detail)

However, things can go wrong!

Firstly, on some occasions, the export wizard fails to open thereby preventing you from accessing the specifications you have created or making new ones. This happened to me and it turns out that the reason was that some code I was running got interupted at one stage and DoCmd.Setwarnings was set to false when it happened. This prevents the export wizard opening for whatever reason.

The solution is to create a sub that sets DoCmd.Setwarnings to True and run it. (Credit to BobLarson here (http://www.access-programmers.co.uk/forums/showthread.php?t=37696&highlight=export+wizard))

Secondly, you may get an error message saying 'too few parameters...expected #' where the # represents a number.

This problem seems to be caused by using a control in a form as a criteria in a query that you're trying to export.

One solution is to simply get rid of the form references in the criteria of the query (Credit Jon K (http://www.access-programmers.co.uk/forums/showthread.php?t=54237&highlight=export+wizard))

But if you need to keep the criteria reference like I did, the best workaround I came up with was to enclose each reference to a form control in the criteria of the query with an Eval("").

Thus, if I were wanting to use a control called 'mycontrol' on a form called 'myform' as a criteria, I would use Eval("[Forms]![myform]![mycontrol]") instead of just [Forms]![myform]![mycontrol] in the criteria line of the query.

Thirdly, you may encounter an error that helpfully says 'Invalid Argument'.

This is due to the presence of a numerical field in the table/query that has its fieldsize property set to 'Decimal'. This is a known bug with access 2000 and solutions can be found at Microsoft's knowledge base (http://support.microsoft.com/kb/263946).

In my case, I resorted to changing the fieldsize properties to Double instead of decimal, and deleted/re-created my export specification after the change and this solved the problem for me.

I hope this post is helpful for others.
Cheers! :D