Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Cool Unanswered: Tool to import and move files

    Is there a fairly no-fuss way to create a form in Access that allows a user to select a file to import, and after finishing the import, then moves the file to another Windows directory so that it doesn't accidentally get imported twice?

    1. on form user selects file from dialog
    2. file loaded from [drive]:\folder\new
    3. file imports to table(s)
    4. file moved from [drive]:\folder\new to [drive]:\folder\old

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    File import

    Seems to me that steps 1,2, and 3 would all be done with vba code in MSAccess (easily enough). Step 4 could be done via a vba shell command to a vbscript passing it the parameter used in step 1 and possibly the destination directory.

    Creating the vbscript shouldn't be too difficult. I'm sure someone could easily come up with one (I've done ones similar to it before but I don't have time to come up with one now.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To move a file from one location to another use the Name function in VBA. Also you can use the Dir function to show the files that are available to import. You will probably also find the DoCmd.TransferDatabase method useful to link to the file and create queries to do the import from the linked table.

    I use this quite often to process incoming files. I have a listbox that I populate with the filename and file statistics (records, file size, etc). You can also use wildcards when using the Dir function to search for *.txt files or *.csv.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    MoveAFolder ReportFolderStatus

    Here Some Code I wrote

    Code:
    Sub MoveAFolder(FormFolder, ToFolder)
       Dim fso
       Dim filesys, folder
       If ReportFolderStatus(FormFolder) Then
       Set fso = CreateObject("Scripting.FileSystemObject")
           fso.copyFolder FormFolder, ToFolder
           Set folder = fso.GetFolder(FormFolder)
           folder.Delete
       End If
    End Sub
    I couldn't get the Move folder to work so this was my workaround


    Code:
    Function ReportFolderStatus(fldr)
       Dim fso
       Set fso = CreateObject("Scripting.FileSystemObject")
       If (fso.FolderExists(fldr)) Then
          ReportFolderStatus = True
       Else
          ReportFolderStatus = False
       End If
    End Function
    The Above code Move about a gig of Data a day When I'm sleeping LOL
    Last edited by myle; 08-31-06 at 20:43.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Moving files

    Nice code Myle. Guess I did things the hard way even though I did something similar but in a vbscript (I should've known better that I could've done it all in vba).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    pkstromy
    that where the code started

    vbscript
    then it got move to excel then finally to msaccess.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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