Run query to export to sequential text file

[magwa1971]

Hi All - who can help me

Not sure if this is a query issue or VBA

I would like to know if it is possible to run a query in access 2003 to export into a text file - but when the query is run again to then export the data into a sequential file

So for instance run the query which then exports the data to a file c:\test\queryrun1.txt and the next time I run the query the export file is c:\test\queryrun2.txt so each time the file increases by one.

Can this be done? If so any chance of the code

Thanks

Magwa

 

[honda882000]

First, create a table which will hold a counter value and set the initial value to 1. This procedure will do that for you:

Sub create_counter_table()

    With CurrentDb
        .Execute ("CREATE TABLE tbl_file_counter (cnt  Number);")
        .Execute ("INSERT INTO tbl_file_counter VALUES (1);")
    End With

End Sub

Then, you can use a recordset to read this value and use it in your file name, and increment this value every time your data is exported:

Sub export_file()
'The table 'tbl_file_counter' holds a counter for the number of times the
'file has been exported.  This value is incremented by one everytime the
'file is exported.

    Dim file_name As String 'This will be the name of the file exported
    Dim rs_file_counter As DAO.Recordset 'This is a recordset to read the current counter value
    
    Set rs_file_counter = CurrentDb.OpenRecordset("tbl_file_counter") 'Open counter table
    
    'Set file name
    file_name = "C:\My File Name-" & rs_file_counter!cnt & ".txt"
    
    'Export file:
    DoCmd.TransferText acExportDelim, , "Table1", file_name, True
    
    'Clean variables:
    rs_file_counter.Close
    Set rs_file_counter = Nothing
    
    'Increment counter:
    CurrentDb.Execute ("UPDATE tbl_file_counter SET cnt = cnt + 1;")
    
    MsgBox "File exported!", vbInformation

End Sub

Hope this works for you.

 

[magwa1971]

Many Thanks

This worked a treat

Mick

 

[magwa1971]

First, create a table which will hold a counter value and set the initial value to 1. This procedure will do that for you:

Sub create_counter_table()
 
    With CurrentDb
        .Execute ("CREATE TABLE tbl_file_counter (cnt  Number);")
        .Execute ("INSERT INTO tbl_file_counter VALUES (1);")
    End With
 
End Sub

Then, you can use a recordset to read this value and use it in your file name, and increment this value every time your data is exported:

Sub export_file()
'The table 'tbl_file_counter' holds a counter for the number of times the
'file has been exported.  This value is incremented by one everytime the
'file is exported.
 
    Dim file_name As String 'This will be the name of the file exported
    Dim rs_file_counter As DAO.Recordset 'This is a recordset to read the current counter value
 
    Set rs_file_counter = CurrentDb.OpenRecordset("tbl_file_counter") 'Open counter table
 
    'Set file name
    file_name = "C:\My File Name-" & rs_file_counter!cnt & ".txt"
 
    'Export file:
    DoCmd.TransferText acExportDelim, , "Table1", file_name, True
 
    'Clean variables:
    rs_file_counter.Close
    Set rs_file_counter = Nothing
 
    'Increment counter:
    CurrentDb.Execute ("UPDATE tbl_file_counter SET cnt = cnt + 1;")
 
    MsgBox "File exported!", vbInformation
 
End Sub

Hope this works for you.

Worked a treat