Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting file names into a datatable (Access 2k, win 2k)

    (Edited by HansV to make provide link to post - see <!help=19>Help 19<!/help>)

    Hello Again.

    I'm trying to get Access to copy around 220 file names from a specific network folder (I can move them to my local drive if necessary) into a datatable. I found <post#=440579>post 440579</post#>, which appears to be overkill, it also doesn't help me to understand what's going on so I can trim it down to my requireemnts.

    So, how do I get access to take 220 file names, including the extension, from a folder and copy them into 220 records? I don't need any other data as the exercise is mainly aimed at cutting out typos and saving time, it'll only be used the once.

    Thanks in advance.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting file names into a datatable (Access 2k, win 2k)

    The code from that post also loops through subfolders, you don't need that. Try the following, substituting the correct name where needed.

    Sub ImportFilenames()
    ' Modify as needed but keep the trailing backslash; you can use a drive letter if you wish
    Const strPath = "serversharefolder"

    Dim strFile As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    Set cnn = CurrentProject.Connection
    ' Substitute table name
    rst.Open "tblList", cnn, adOpenKeyset

    strFile = Dir(strPath & "*.*")
    Do While Not strFile = ""
    rst.AddNew
    ' Substittute field name
    rst![FileName] = strFile
    rst.Update
    strFile = Dir
    Loop

    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k, win 2k)

    Hans

    Thanks, I can follow most of this, although the ADODB is a bit odd to a DAO luddite like me...

    I've got the error message 3251, Object or Provider is not capable of performing requested operation.

    I've gone through the references, DAO 3.6 is added, but nothing else, I can't see any specific ADODB refences I need to load, are there?

    Thanks for the help.

    Ian

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k, win 2k)

    Right, been doing some digging on the MS site and found this knowledge base article, http://support.microsoft.com/kb/248144 which says the error message is by design. So, what do I need to do to get the data the code finds written into my datatable?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Getting file names into a datatable (Access 2k, win 2k)

    If you are not comfortable with ADO, then just use DAO; it is generally easier anyway.

    And just to illuminate Hans' solution. The first use of Dir(str) returns the first filename that meets the criteria in str. Then calling Dir without arguements returns the next filename meeting that same criteria.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting file names into a datatable (Access 2k, win 2k)

    I used ADO because you mention Access 2000, ADO is the default there. But as noted by Mark, you canuse DAO just as well. Change

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    Set cnn = CurrentProject.Connection
    ' Substitute table name
    rst.Open "tblList", cnn, adOpenKeyset

    to

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    ' Substitute table name
    Set rst = dbs.OpenRecordset("tblList", dbOpenDynaset)

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k, win 2k)

    Hans

    Thanks, the DAO stuff worked, I'd tried to convert it myself yesterday, but didn't get past the error message, just got many other varieties of error message, all with the same sort of meaning "You can't do this, we don't want you to do this". I now know why, I'd not changed the rst.Open line correctly, changed yes, correctly No.....

    I was unaware that 2000 used ADO by default, I take it this is why I can't find any thing to add under the references...... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Mark

    I'm not 'comfortable' with either DAO or ADO, but over the last couple of years people like Hans and yourself have got me to point where I can just about work out what's happening with DAO, although in the example Hans gave me it was fairly easy to work out most of the things I had to change to something else, my general incompetence lead to missing an important change of method. At some point, when I have time, I'll try and work out why the ADODB code gave me the errors, I like to understand my mistakes!!

    Thanks to both of you for your time, it is always appreciated and has saved me a lot of innacurate typing.

    Ian

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k

    This works very well for returning the file names. Can it be extended so that the whole path is returned i.e. serversharefilename

    nebbia

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting file names into a datatable (Access 2k

    Yes, change the line
    <code>
    rst![FileName] = strFile
    </code>
    to
    <code>
    rst![FileName] = strPath & strFile</code>

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k

    Perfect Thanks

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k

    Actually I spoke too soon. I have multiple photo file locations referenced in a table . The photo files themselves are in the same directory. My question has to do with handling new photos. Their location needs to added to the address table and the files themselves placed in the directory What I think I need is either
    1-retrieve all the file names in that directory and append them to the table. Unfortunately that would create duplicates each and every time. I would then have to remove all duplicates or
    2- the other idea would be to put the new photos in a separate directory, generate a list of file names (no path included) Before placing the reference in the table I would need to add the path to the photo directory plus the generated list of file names. The photos themselves would then be moved to the 'true photo directory'

    I'm not sure that I have been clear enough or if these are the best solutions. Any help would be appreciated

    nebbia

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting file names into a datatable (Access 2k

    I'm not sure I understand.

    If you want to prevent duplicate path/filenames in the table, you can set a unique index on the field that contains them, and use

    ...
    On Error Resume Next
    rst.Update
    On Error GoTo 0

    If the record cannot be added because the path/filename already occurs, the code will simply skip it and continue with the next file.

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting file names into a datatable (Access 2k

    Sorry for the delay and sorry that its not clearer.
    If I add filenames to directory c:dir when I transfer them to tblTransfer I do not want any duplicates to be added. I am using your code but I 'm not sure how to add the on error clause.

    ' Modify as needed but keep the trailing backslash; you can use a drive letter if you wish
    Const strPath = "c:dir"

    Dim strFile As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    ' Substitute table name
    Set rst = dbs.OpenRecordset("tbltransfer", dbOpenDynaset)

    strFile = Dir(strPath & "*.*")
    Do While Not strFile = ""
    rst.AddNew
    ' Substittute field name
    rst![Picture] = strPath & strFile
    rst.Update
    strFile = Dir
    Loop


    rst.close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub

    You have suggested that I set a unique index to the 'picture' field and then added the following code

    On Error Resume Next
    rst.Update
    On Error GoTo 0

    nebbia

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting file names into a datatable (Access 2k

    You should set a unique key on the Picture field. This is done interactively, by opening the tblTransfer table in design view. Either select the Picture field, then click the Primary Key button on the toolbar, or - if you don't want to make Picture the primary key - activate the Indexes window, enter Picture in both the Index Name and Field Name columns (in a blank row), and set the Unique property of the new index to Yes. Then save the table.

    In the code, simply replace the line

    rst.Update

    with

    On Error Resume Next
    rst.Update
    On Error GoTo 0

    In other words, insert On Error Resume Next above the existing line, and On Error GoTo 0 below 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
  •