Create a batch file and run it

Question

I need to move closed files to a closed file folder every night.  So I have a query that finds files indicated as closed, then creates this text per record;

move m:\12345 m:\closed\12345

move m:\12346 m:\closed\12346

and so on, for all records that meet the criteria.

How do I then save this as a batch file so that I can make my windows scheduled tasks run the resulting batch file?

I imagine its some kind of transfertext command...

Although I bet there is a way to run the command directly from the Sub until all records in the resulting query are processed...

I'm not very good with that do until recordset is complete business...

Any help would be greatly appreciated.

Answer 1

I rephrased the question and posted it as this title and got the answer. Thank you Scott Gem!

Run a DOS MOVE command on every record in a select query

Answer 2

Yes Bill, the source path will be different for each record.

The requirements have changed a little...I am creating a new drive letter for the closed files, drive "K".  So the command will be:

move m:\foldername1 k:

move m:\foldername2 k:

move m:\foldername3 k:

and so forth...

The source foldername for each record is contained in the [Location] field of the query.

The current sql statement of the query is:

SELECT "Move " & [location] & " K:" AS DOSCommand

FROM t_Matters

WHERE (((t_Matters.DateClosed) Is Not Null) AND ((t_Matters.Location) Not Like "K:\*"));

...so if there is a date closed, it is a candidate, but if the location field already contains "K:" then it has already been moved, so it is not a candidate

Answer 3

Here is a simple example for moving a file using a recordset from a query. In it I used a couple constants for the folder names. If you need to make that dynamic we will have to manipulate the source file path so we can insert the "closed" folder name, but that's not tough.

Public Function MoveFiles()

    Const conSourcePath As String = "C:\MyFolder"

    Const conTarget As String = "C:\MyFolder\Closed"

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL As String

    Dim strFile As String

    Set db = CurrentDb

    strSQL = "SELECT FileName FROM myTable WHERE Closed=-1"

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    With rs

        Do While Not .EOF

            strFile = !FileName

            Name strsource & strFile As strTarget & strFile

            .MoveNext

        Loop

    End With

    Set rs = Nothing

    Set db = Nothing

End Function

Answer 4

Hi. Yes, you can "move" the files from Access if you don't need to schedule it. Try the NAME AS command. For example:

NAME m:\12345 AS m:\closed\12345

Is that a file or a directory? I'm not sure NAME AS will work in moving the entire folder.

Just my 2 cents...