CODEWITHSUNDEEP
ms-accessms-access-2007vba
tcrite
tcrite

Reputation: 553

Writing a single column from a table in MS Access VBA to .txt file

this is my first time posting a question here, but I almost always get good answers from searching this site. I'm trying to find out if there is a better way to speed up this process I have of writing a table column in Access to a .txt file. Here is the code I have, which works, but it's on the slow side. It takes about 45s to write around 7000 items.

lsFileName2 = "E:\DOI_Inventory2\SyncData\EquipUser.txt"
LiFileNumb = FreeFile

SysCmd acSysCmdSetStatus, "Updating User Equipment List"

Open lsFileName2 For Output As LiFileNumb
    With rst
        Do While Not .EOF
            Write #LiFileNumb, ![EqUserFile]
            .MoveNext
        Loop
    End With

dbs.Close
Close LiFileNumb

I'm fairly new to the IT field so any help would be greatly appreciated. Thanks

Just to add a note, the actual query is fine. I already checked that and it's pretty fast.

Upvotes: 3

Views: 1279

Answers (1)

HansUp
HansUp

Reputation: 97101

Create a query, save it with a name, and then use TransferText to export the query's data to your text file.

So assuming you have a SELECT query named qryExportMe like this which returns your table's column data correctly ...

SELECT EqUserFile
FROM YourTable;

... refer to this TransferText example and adapt it to fit your needs.

DoCmd.TransferText TransferType:=acExportDelim, _
    Tablename:="qryExportMe", _
    FileName:="E:\DOI_Inventory2\SyncData\EquipUser.txt", _
    HasFieldNames:=True

Check the TransferText options at that linked page or from Access' built in help system.

Note you are not required to include the option names. I added them to help you keep track of which is which.

If this approach speeds up your export operation adequately, I think it will be because Access handles the task as a single set-based file write. Your recordset approach required Access to process one row at a time ... and one reason such approaches are called RBAR (row by agonizing row) is because they are often painfully slow.

Upvotes: 1

Related Questions