Saving a table to another file

[Tony Williams]

I have a database that has daily imports from a text file. I import them into
a temporary table to do some cleaning up then use the remaining data to
update and append records to my main table. However I want to save the
temporary table each day and have used this code behind a command button

Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click
DoCmd.TransferText acExportDelim, "VDATA Export Specification", "VDATA",
"C:/TempData/tempdata.txt"
Exit_cmdsave_Click:
Exit Sub

Err_cmdsave_Click:
MsgBox Err.Description
Resume Exit_cmdsave_Click

However this will mean that each time I save the data it will overwrite the
current file bevcause the file location is coded. Is there a way of using
this code to prompt for a file name each time I save the file so that I can
add say the date?

Thanks
Tony

 

[Stefan Hoffmann]

hi Tony,

However this will mean that each time I save the data it will overwrite the
current file bevcause the file location is coded. Is there a way of using
this code to prompt for a file name each time I save the file so that I can
add say the date?

Private Sub cmdsave_Click()

On Local Error GoTo LocalError

Dim Filename As String

Filename = "C:\TempData\" & _
Format(Now(), "yyyymmdd") & _
".txt"
Filename = InputBox("Enter file name:", "Save", Filename)

DoCmd.TransferText _
acExportDelim, _
"VDATA Export Specification", _
"VDATA", _
Filename

Exit Sub

LocalError:
MsgBox Err.Description

End Sub



mfG
--> stefan <--

 

[Tony Williams]

Thanks Stefan that worked just fine.

Is there any way I can change the code to save a table in my database to
another Access database as a backup table? This looks such a good routine
I've thought of other things I can do.

Thanks again

 

[OLIVIA]

423416-2046