Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Question Unanswered: Multiple file copy

    I have an access database which references files stored on a server. Occaisionally I need to access some of these files based on a certain criterium. I then need to copy these files from their source location to a multiple location and update the database to tell it they have moved (there is a status column in the table). The number of files can vary from 0 to 100.

    I've though about using the FileCopy function but don't know how to feed a variable list into it. I don't want to change the file name just copy it from, for example, F:\file to c:\files

    Any help will be appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Multiple file copy

    Originally posted by dfawatts
    I have an access database which references files stored on a server. Occaisionally I need to access some of these files based on a certain criterium. I then need to copy these files from their source location to a multiple location and update the database to tell it they have moved (there is a status column in the table). The number of files can vary from 0 to 100.

    I've though about using the FileCopy function but don't know how to feed a variable list into it. I don't want to change the file name just copy it from, for example, F:\file to c:\files

    Any help will be appreciated.

    Thanks
    Build a function that reads the directory and loads them in a table. Build a form with a list box in it. On form open fire the function to update the table. The list box control source is the a query to the table. Make sure the list box is a multi select and then process from the multi select list box.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Oct 2003
    Posts
    5

    Re: Multiple file copy

    Originally posted by jimpen
    Build a function that reads the directory and loads them in a table. Build a form with a list box in it. On form open fire the function to update the table. The list box control source is the a query to the table. Make sure the list box is a multi select and then process from the multi select list box.
    Thanks Jim, the directory contains many files it is only the ones returned in a query that I want to copy. I have a table which has columns [job_id, status, location] I only want to copy certain files with a particular status

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Multiple file copy

    That is where you can put parameters on the query either one way or another such as on load set listbox.controlsource = query where [jobid] = 1
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Oct 2003
    Posts
    5

    Re: Multiple file copy

    Originally posted by jimpen
    That is where you can put parameters on the query either one way or another such as on load set listbox.controlsource = query where [jobid] = 1
    Once again thanks Jim - I've done this and have a great looking list box which returns the files. How do I then copy these from location A to location B? I assume I need to pass each file through to some function but this is not something I am familiar with.

  6. #6
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Multiple file copy

    Try something like this:


    'User input/output
    Dim Msg As String
    Dim Title As String
    Dim BoxType As Integer
    Dim Answer As Integer

    'For holding the data and recordsets that need to be opened _
    to extract the data
    Dim Quote As String
    Dim SQLState As String
    Dim Filter As String
    Dim DB As database
    Dim RS As Recordset
    Dim OutRS As Recordset
    Dim PartyConvert As String

    Dim FileBox As Control

    'For holding the export file strings
    Dim HomePath As String
    Dim DestPath As String
    Dim FileName As String
    Dim WaitTime As Single
    Dim HeartBeat As Integer

    'These variables are for the availble notes and an array _
    to hold the selected notes
    Dim AvailCount As Integer
    Dim ArrayCount As Integer
    Dim DownloadFile As Integer
    Dim IndexNum As Long
    Dim I As Integer

    ArrayCount = 0
    AvailCount = Forms!Download_Select_Form!Available_File_List.Lis tCount
    I = 0


    HomePath = "R:\MSNaut\" '\" '"T:\Private\IS\MServFTP\'DL.E.MORTPROD.DLINFIL.PA YTR834(+1)'"

    'Counting the file selected to be exported.
    With Me!Available_File_List
    Do Until I >= AvailCount
    'Debug.Print .ItemData(I) & " - " & .Selected(I)
    If .Selected(I) = True Then
    ArrayCount = ArrayCount + 1
    End If
    I = I + 1
    Loop
    End With

    'If they didn't select any return for a selection
    If ArrayCount = 0 Then
    Msg = " You must select " & Chr(13) & _
    " one file to move. " & Chr(13) & _
    Chr(13) & _
    "Please make a selection."
    Title = "No Selection"
    MsgBox Msg, vbExclamation, Title
    Exit Sub
    End If

    'Read through and get the notes that are selected.
    'ReDim ProductCode(ArrayCount)
    'ArrayCount = 1
    I = 0
    Set DB = CurrentDb()

    DoCmd.Hourglass True
    With Me!Available_File_List
    Do Until I >= AvailCount
    ' Debug.Print .ItemData(I) & " - " & .Selected(I)
    If .Selected(I) = True Then
    SQLState = "Select File_Name, Uploaded " & _
    "FROM FTP_Files " & _
    "WHERE Index = " & .ItemData(I)
    IndexNum = .ItemData(I)
    Set OutRS = DB.OpenRecordset(SQLState)
    OutRS.MoveFirst

    Debug.Print HomePath & OutRS!File_Name


    Call Reprocess_Accting_Data(HomePath & OutRS!File_Name)
    With OutRS
    .Edit
    !Uploaded = True
    .Update
    End With
    End If
    I = I + 1
    Loop

    OutRS.Close

    DoCmd.Requery Forms!Download_Select_Form!Available_File_List.nam e
    End With
    DoCmd.Hourglass False

    Msg = "Do you want to delete this file?"
    Title = "Delete file?"
    BoxType = 4 + 32
    Answer = MsgBox(Msg, BoxType, Title)
    If Answer = 6 Then
    SQLState = "Select File_Name, Uploaded " & _
    "FROM FTP_Files " & _
    "WHERE Index = " & IndexNum
    Set OutRS = DB.OpenRecordset(SQLState)
    OutRS.MoveFirst
    If Dir(HomePath & OutRS!File_Name) = OutRS!File_Name Then
    Kill (HomePath & OutRS!File_Name)
    End If

    Msg = "File " & (HomePath & OutRS!File_Name) & " has been deleted."
    OutRS.Delete
    OutRS.Close
    Title = "Deletion Complete"
    BoxType = 64
    MsgBox Msg, BoxType, Title
    DoCmd.Requery Forms!Download_Select_Form!Available_File_List.nam e
    End If
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  7. #7
    Join Date
    Feb 2003
    Posts
    8
    not even close.

  8. #8
    Join Date
    Oct 2003
    Posts
    5

    Re: Multiple file copy

    [QUOTE][SIZE=1]Originally posted by jimpen
    Try something like this:


    'User input/output
    Dim Msg As String
    Dim Title As String
    Dim BoxType As Integer
    Dim Answer As Integer
    Etc.....

    Thanks Jim - I think I can work out what this is meant to do - particularly the bit about deleting the files. My DB works slightly differently. I have the list Multi Select list box with the file names listed eg:

    g:\docs\sound1.wav
    g:\docs\sound2.wav

    What I want to do is copy these from their home location to a new source location. Therefore the code needs to somehow pick the text in the column - pass this into a variable and do a file copy. This is the bit I am having difficulty with.

    Looking at your code, it seems to work in a different way in that it shows the files in the list box and when selected passes these into a SQL statement which then passes the file name into the command - am I right?

    Dom

  9. #9
    Join Date
    Oct 2003
    Posts
    5
    Originally posted by deb
    not even close.
    Deb,
    Thank you for your reply, not sure what you're getting at though. Do you have any ideas that can help with this problem.

  10. #10
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Multiple file copy

    Mine is designed to copy a file to a specific filename and location on demand for uploading. But the capability is there to take what is selected in the multi-select box and do a filecopy, one file at a time. That is where the loop comes in.

    My suggested method is that you store the directory name separate from the actual filename. The method to that madness is that way you can do a dir() to get the file name back and/or to check if the file exists in the destination already. If you do dir("g:\docs\sound1.wav") you get back "sound1.wav" not "g:\docs\sound1.wav". The other reason is that if you are copying from g:\docs to c:\temp\docs, you don't have to try and paser the filename from the pathname.

    I have an index number on the table and that is what is stored in the return from the ittemselected data. (The index number is hidden from the user.) The index number makes it easy to select the specific record in the table and use that to do the the filecopy. I close and re-open the recordset for each pass, but you could do an openrecset and then use a seek. That is up to you.

    The comment from Deb was on another posting where she is looking at trying to limit down data to be selected by users on the earlier select on a form. I was trying to save typing by hoping some of these ideas would translate. I referred her to this post.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •