Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import File Folder? (97)

    Hello Everyone,

    I would like to know if there is a way to import the contents (the files) of a file folder on, say the "c:" drive to a table in Access. And if so, is the process fairly simple...I thought this could be done by making an API call to the drive, but I'm not sure of the procedure.

    Thanks for any suggestions.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import File Folder? (97)

    You could create a field of type "Ole Object" or "hyperlink. The Ole type is not recommended as it dramatically increases the size of the database. I use the hyperlink field quite a bit.

    Create a directory to store your files in and then use a hyperlink field to list them (drivepathfilename.ext).
    Then double clicking on the hyperlink field will open the files.
    I leave the drive name off so that if I move the database to another drive the relative links will work (pathfilename.ext).

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import File Folder? (97)

    If you want to capture the filenames of files in a specific folder, then if tblFilenames is your table with Filenme as the field in this table (substitute your own names for tblFilenames and Filenme) then the following code should do the trick:

    Dim dbs as DAO.Database, rs as DAO.Recordset
    Dim strFilenme as String
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset("tblFilenames")
    strFilenme = Dir("cath...")
    Do While strFilenme <> ""
    rs.AddNew
    rs!Filenme = strFilenme
    rs.Update
    strFilenme = Dir
    Loop
    Set rs = Nothing
    Set dbs = Nothing

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import File Folder? (97)

    OK....so I'm a dummy...I've tried your code .... I've placed it in the OnClick event of a button in a form....nothing happens when I click the button.

    I will explain a little further what I'm trying to do. I have a directory ("c with a folder (fmgEm) that has files in it that I would like to retrieve on a regular basis. I don't want to execute these files...I just want to use the file names and store them in a table in Access where they will be used as an underlying table to a combo box.

    Better said....I would like to be able to click a button on a form....the code would go to the specified directory and retrieve the file names in the specified folder and update the table (tblFileNames). Just as your code suggests it should...however when I click the button, nothing happens! any suggestions?

    Thanks for your help, it's greatly appreciated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import File Folder? (97)

    No I'm the dummy <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>, I didn't fully expand on the first Dir command.
    It should read:
    strFilenme = Dir("c:fmgEm*.*")
    The wildcard tells it that you want all files in that folder.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import File Folder? (97)

    Pat,

    3rd times a charm! I hope....LOL....I tried it again....and still nothing...any more suggestions....still the table isn't updated with the file names that are in the directory.

    Thanks again for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import File Folder? (97)

    Would you post the code here.
    In the meantime could you set a breakpoint on the first command and step thru it and try to work out the problem.
    What does the first Dir return?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import File Folder? (97)

    Pat,

    Here's the code

    Private Sub cmdFileNames_Click()
    Dim dbs As DAO.Database, rs As DAO.Recordset
    Dim strFileName As String
    Set dbs = CurrentDb()
    Set rs = dbs.OpenRecordset("tblFilename")
    strFileName = Dir("C:fmgEM*.*")
    Do While strFilenme <> ""
    rs.AddNew
    rs!FileName = strFileName
    rs.Update
    strFileName = Dir
    Loop
    Set rs = Nothing
    Set dbs = Nothing
    End Sub

    As for setting the breakpoint and stepping through it...I've tried it from the first line and it fails on the first line of code.
    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import File Folder? (97)

    Pat,

    I've got it....there was a spelling error in the code..I corrected the spelling and it works...thanks for all your help. Really it is appreciated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import File Folder? (97)

    I'm glad you got it working.
    What was the spelling error?
    Do you have Option Explicit at the start of the code?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import File Folder? (97)

    Pat,

    Yes I have Option Explicit and the Spelling Error was in strFileName...I made the change the first time I copied the code and missed it in one location after I pasted it in......it was in the Do While line.

    I have now put all my other codes in with it and all is working well. I can now select all the records, delete them just before I update the table, update the table just before I refresh it, parse the extension of the file name, close the table, and close the form. It was the missing peice of the puzzle....thanks for all your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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