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

Topic

New Reply

(Edited by HansV to make provide link to post – see Help 19)

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, 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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Right, been doing some digging on the MS site and found this knowledge base article, http://support.microsoft.com/kb/248144%5B/url%5D 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?

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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……

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

Reply | Quote

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

nebbia

Reply | Quote

Yes, change the line

rst![FileName] = strFile

to

rst![FileName] = strPath & strFile

Reply | Quote

Perfect Thanks

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote