Freedom Computers

ACCESS

Export data from Access to a text (csv) file


DoC
md.TransferText acExportDelim, "tblMyTable", _, "MyTextFile.txt"

This will create a delimited text file from the table tblMyTable. The second, empty, argument would be a previously defined transfer specification name.

Being able to do this opens up the possibilities of multiple, dynamically named files containing paremetrised data! Unfortunately it is not possible to simply replace 'tblMyTable' with 'qryMyQuery' as transferText will not run on queries. We need to first make the query a make table query which we will run using parameters before we export the data:


Public Sub exportIndividualFiles()
Dim db As Database
Dim myEmployeeTdf As TableDef
Dim myEmployeeSalesQdf As QueryDef
Dim myEmployeeRS As DAO.Recordset
Setdb = CurrentDb()
Set myEmployeeTdf = db.TableDefs ("tblEmployees")
Set myEmployeeRS = myEmployeeTdf.OpenRecordset
Do While Not myEmployeeRS.EOF DeleteIt "tblEmployeeSales"
Set EmployeeSalesQdf = db.QueryDefs("qryEmployeeSales")
EmployeeSalesQdf![EmployeeID] = myEmployeeRS.Fields(0) EmployeeSalesQdf.Execute
employeeFilename = "C:\" & _ EmployeeSalesQdf![EmployeeID] & ".txt" DoCmd.TransferText acExportDelim, , _ "tblEmployeeSales", employeeFilename myEmployeeRS.MoveNext
Loop DeleteIt "tblEmployeeSales"
Set myEmployeeTdf = Nothing
Set myEmployeeSalesQdf = Nothing
Set myEmployeeRS = Nothing
Set db = NothingEnd Sub



The above example:

Opens a list of employees - tblEmployees
Uses the EmployeeID as the parameter for the qryEmployeeSales query which will make the table 'tblEmployeeSales'.
This table is then exported to a textfile named for the EmployeeID.
This exercise will result in separate files for each employee containing their sales records

Also see the DeleteIt Knowledgebase article