Importing data from txt file on weekly basis!

[Sameer]

I have a form on intranet which sends the data in csv text file.The form is used by many users to feed in the info.I have to generate various reports from the data recd in csv file and I wish to do reporting MS Access.Is there any way to do the following:
1. MS Access should automatically import data from text file weekly.
2.Kill that text file from where the data is imported so that it is empty to receive next week data.
3.Every week is imported in different tables.

I hope I am clear with my question.
Any help....greatly appreciated.

 

[BukHix]

1. MS Access should automatically import data from text file weekly.

This can be done rather simply with this:

DoCmd.TransferText acImportDelim, , "ImportTableName", "c:\FileName", False

You can make it a little more complicated by using an Import spec. Of course this code is not automatic in that it needs an event to trigger it. It will be up to you to determine the event though.

2.Kill that text file from where the data is imported so that it is empty to receive next week data.

The short answer to your question is this:

Dim strFileName As String
strFileName = "c:\myfile.txt"
Kill strFileName
MsgBox (strFileName & " has been deleted")

Check my reply to this recent thread. I think you will find the answer for this one there plus some more helpful information for your project.

Moving Documents and Reading for Existance of Server Folders

3.Every week is imported in different tables.

I am not sure what kind of criteria you want to use for different names but say for example you wanted to use the current date as part of the name you could do something like this:

' Declare the variable names for the export path

Dim dtName As Date
Dim strFName As String
 
' get the current date
dtName = Date

  
strFName=strFolderName & "\pr" & Format(dtName, "mmdd") & ".txt"
 
' Exports time to listed directory

DoCmd.TransferText acExportDelim, "Bidtek", "MyReport", _
  strFName, False

The file name will end up looking like, "pr0113.txt" if it were run today. In that example I am actualy exporting the file but you can use the same idea to import or Save As to get unique names. Hope that helps. Post back if you have more questions.

 

[Sameer]

Thank you very much...I will give it a try...I am sure these will solve lots of my question...If I have any further to ask...for sure get back here...Thanks again

 

[BukHix]

Sameer, I amended my post slightly to make it a little more informative. There is now an example of the Kill command in the post along with some more information about how the dynamic name will come out.

 

[Sameer]

Great...Thanks once again...much appreciated