Help to Refine Code for better performance

[JohnLee]

Hi Folks,

With the help of experts here I've been able to produce the following code:

[FONT=Times New Roman][SIZE=3][/SIZE][COLOR=blue]
[SIZE=3][COLOR=#000000] [/COLOR][COLOR=blue][COLOR=#000000][COLOR=blue]If[/COLOR] FS.FileExists("B:\aeg.fof\aeg.txt") = [COLOR=blue]True Then[/COLOR]
        FS.CopyFile "B:\aeg.fof\aeg.txt", "G:\Scan - Verify\eFlow\BackupProdRegOnprdfs01\aeg.fof\aeg " & "_" & Format(Date, "ddmmyy") & ".txt"
        
        filenum = FreeFile 
        
        [COLOR=blue]Open[/COLOR] "B:\aeg.fof\aeg.txt" For Input As filenum
        
        [COLOR=blue]Do While[/COLOR] Not EOF(filenum)
            Line Input #filenum, tmp$
            count = count + 1 
        [COLOR=blue]Loop[/COLOR]
        
        [COLOR=blue]Close[/COLOR] #filenum 
        
        [COLOR=blue]Set[/COLOR] rst = DB.OpenRecordset("tblFileExists") [/COLOR][/COLOR][/SIZE]
[SIZE=3][COLOR=blue][COLOR=#000000]        rst.AddNew 
        rst!strFileExists = "aeg.txt"
        rst!lngRecordCount = count 
        rst.Update 
        rst.Close 
        [COLOR=blue]Set[/COLOR] rst = [COLOR=blue]Nothing[/COLOR]  
        
        count = 0 
        
        DoEvents 
        DoCmd.Echo [COLOR=blue]True[/COLOR], "Copying/Adding AEG Text File: " & "aeg.txt"
    [/COLOR][COLOR=blue]End If[/COLOR][/COLOR][/SIZE]
[/COLOR][/FONT]

I have to produce this code for each instance of a text file and it's associated folder(s). which is in excess of 45 text files and folders and is likely to grow.

I understand that the code can be modified so that there is no need to have the above code mulitiple times. I need help in doing just that, as I don't know how to go about making the changes for that purpose.

A bit of background information may help you understand.

Text files are exported from a scanning system in fixed width format, but that scanning system doesn't keep a history for more than 48 hours. the nature of our work requires us to have the ability to revisit or indeed re-submit data to our main frame database should something go wrong, so hence the database solution for this.

Each day the respective manufacturer folders are checked for text files pertaining to that manufacturer, if a text files exists it is copied to our backup drive location to a folder with the exact same name, and the current date is added to the name of the text file, so that we can identify data according to the date it was exported from the scanning system.

The above code also counts the number of records in each text file if it exists and creates a record in the tblFileExists table each day.

So I need to modify the above code in a way that doesn't require it to be written mulitple times and removes the need to add more code when new manufacturers are added to the system.

Any help in this direction would be most appreciated.

John

 

[Galaxiom]

Names of different manufacturers and their filenames and paths could be kept in a table to be used in a loop.

You can retreive the names of objects in a folder using the Dir function.
http://office.microsoft.com/en-gb/access-help/dir-function-HA001228824.aspx

Once you have their names you can use that as a variable and process them in a loop

I don't really know but since you are only counting lines maybe try the ReadLine Method of the TextStreamObject instead of Line Input. ReadLine has an argument for the number of characters which could be set low to avoid actaully transferring the whole line. Might make it run trivially faster.

Just a word on good practice. In this case it is probably superceded by keeping filenames and paths in a table but normally in this situation one would set the filename base as a constant at the beginning of the code.

It is used multiple times in the code and would be much easier to edit in one place if it ever changed. I would do the same with the Paths even though the are only used once.

Neither would make the code run faster but it is a lot tidier to edit them in a constant.

 

[JohnLee]

Hi GalaxiomAtHome,

Thanks for your response, I'll look up some information accordingly.

John

 

[gemma-the-husky]

counting lines in a file, although easy, is not quick.

if they are fixed width, you could just divide the total size by the bytes per line,

if you only need to check new files, its easier.

but if you need to recheck every file, i cant see a quick way - its just a slow (relatively) process

(obviously you could loop round a dataset or the folder to avoid manually keying in every file name)

 

[JohnLee]

Hi Gemma,

Thanks for your response, but I think I've been misunderstood in what it is I'm trying to do.

The counting of lines in a text file is fine, I have no problems there. What I'm trying to work out is how to loop through 40 or more folders checking to see if the associated text file is present, if it is then to make a copy of that text file and place it in another location and then to count the contents of that text file and update my tblFileExists table with that information.

The copying part I am fine with and I am with the counting of lines in the text file.

The part I'm not fine with is how to loop through all the folders checking to see if the text file exists and if it does then to copy that file to another location and then count the records in the text file.


I currently have 40 folders all of which on a daily basis may or may not have a text file in it. Also the number of folders and text files pertaining to the folders is likely to grow.

So currently I have written code as per my first post for each and every folder/text file, which is a lot of code doing the same thing, but for different folders and text files.

What I am hoping to acheive is to reduce that code down so that I have code that loops through each folder in turn checks to see if a text file exists, if it does then to count the number of records in the text file update my tblFileExists table and then copy that text file to my backup location [in this case my G:\Scan - Verify\eFlow\BackupProdRegonprdfs01\ folder.

So ultimately once the code is written I won't have to keep adding code for any new folders/text files that are created, because the code that I hope to write will cover that.

GalaxiomAtHome, suggested having a table in my database that lists all the folder and text file names, the problem with that is, that it will still need to be updated whenever new folders and text files are created. What GalaxiomAtHome was not aware of is that the scanning system creates folders and text files when new data is processed and I will not know when that happens until it actually arrives in the destination drive, which is why I am hoping to write code as outlined above.

I've been googleing stuff on the internet, but haven't yet come across anything that shows me how this is done, most of the stuff I've come across deals with looping through text files, so any help would be most appreciated.

John

 

[Dairy Farmer]

Robocopy is a much better solution for copying files.

 

[gemma-the-husky]

unfortunately, you cannot easily use dir, because it isnt recursive you could use it, but you would have to set up an array of the folders you need to visit

ie - you cannot navigate down and then back up directly, just using dir ...

otherwise

filename = dir("somefile") will return a non zls, if it find a (partial) match on somefile

-------------
i am not familiar with treeview controls - maybe one of those would help.

 

[boblarson]

Don't know if this might help - you might check it out.

 

[JohnLee]

Hi Gemma/Boblarson,

Thanks for your pointers, I'll have a look and see what I can kleen from them.

John