Renaming files from database records

[mrkipling]

Hi,


I am just about to start a small file renaming project with the following requirements:

Provide a front end for users to select a specific directory from a dir structure.

Rename the files in that dir by looking up the existing filename in a ms sql db table, and renaming the file with a string from a different column in the corresponding db record.

This is my very basic train of thought on the project:

start loop

loop through each file

create a recordset using the filename as criteria to return the required string.

rename file with new name.

Does this seem correct ?

Do I place the recordset inside the loop so it returns the correct string for each file name ?

As you can guess coding is not a strong point, so any advice is greatly appreciated.

Thanks

 

[Sheco]

How many rows in the database table?

If it is small then pull it all at once and set the .Filter property of the recordset each time through the files loop.

If the table is large then query it each time around.

 

[mrkipling]

Thanks for the reply Sheco.

The table at any one time has around 10000 records in it, so I guess I will query it each time round.

 

[damber]

10000 is a relatively small number of records.

let's say each filename is 30 char's in length, you will have 60bytes of data per record - which would be about 585kb , even + a 30% overhead would be less than 1 MB of memory used to store the recordset object for a 1 time pass (I'm assuming you wont be doing this very often and it will be single threaded - e.g. maybe once a day or even a one off process)

For simplicity, follow Sheco's idea and pull all the records to a disconnected recordset and use the Filter method to find the relevant name to use. This will be more performant than creating 10000 database requests.

If you want to speed it up a bit more, sort the source file names alphabetically and step through the array (created with GetRows) until you find a match in the file list, then increment the counter to the next one etc and continue your looping. This is likely to be more efficient than searching for the right record every time, as it requires only one pass of each array of values.


As a bit of an example here's some semi-pseudo code:
e.g.
Row Ordinal in Use = (ROU) = Current Row in the Array
Named Documents = (ND) = Array of Files in filesystem
Table = (TABLE) = Table of reference data

ROU = 0 
ND = {get files from FS here}
TABLE = {SELECT sorted list from db and return using .GetRows}

for i=lbound(ND) to ubound(ND)
   for j=ROU to ubound(TABLE)
      if ND(i) = TABLE(ROU,0) then
          'Do some renaming stuff for the file here
           ROU=j+1
           exit for
      end if
   next
next

A smile is worth a thousand kind words. So smile, it's easy!