Need to import filenames into a table

[Guest_imported]

Hello,
First off, I am a newbie to Access and VB...Right now, I am making a database of filenames in a directory on one of my computers. My goal is to be able to press a button on my form that will update the filenames in my access table. The table consists of two fields, Filename and Description.
Filename is the pkey and description is optional.

The directory is a mapped drive on this old server I have setup called 2k1. So the map is \\2k1\graphics. How do I make the table update Filename with new files that are saved into the \\2k1\graphics directory? Any help is greatly appreciated!!

thanks!
Danny

 

[lionelbrown]

Here's a quick and dirty way to do it. Probably not the best way (since I'm relying on an error to prevent duplicate records), but it works.

This is using ADO in Access 2000. You could also use DAO to do it with a little code tweaking.

It looks like this post will wrap the text, so you'll have to of course clean up if you cut and paste.


Private Sub cmd_GetFiles_Click()
On Error GoTo Err_cmd_GetFiles
Dim strFile As String
Dim strPath As String
Dim strName As String
Dim strMsg As String
Dim lngNewFileCount As Long
Dim lngExaminedFileCount As Long
Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset

'set new file counter and examined file counter to 0
lngNewFileCount = 0
lngExaminedFileCount = 0

'specify path here (could provide way to specify path from interface)
strPath = "\\2k1\graphics\"

'open connection to current db
Set cnn = CurrentProject.Connection
'open recordset on tblFiles
rs.Open "tblFiles", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

strFile = Dir(strPath, vbNormal) 'get a file
Do While strFile <> &quot;&quot; 'loop through all the files
'Add new record to tblFiles
With rs
.AddNew 'create new record
.Fields(&quot;FileName&quot = strFile 'add filename
.Update 'save new record
lngNewFileCount = lngNewFileCount + 1
lngExaminedFileCount = lngExaminedFileCount + 1
End With
strFile = Dir 'can call without arguments to get next file
Loop

'Clean up objects
If rs.State = adStateOpen Then
rs.Close
Set rs = Nothing
End If

If cnn.State = adStateOpen Then
cnn.Close
Set cnn = Nothing
End If

Exit_cmd_GetFiles:
'give a confirmation message
strMsg = &quot;Examined &quot; & lngExaminedFileCount & &quot; File(s) in &quot; & strPath _
& vbCrLf & &quot;Added &quot; & lngNewFileCount & &quot; New File(s) to the Database&quot;
MsgBox strMsg, vbInformation, &quot;Finished&quot;

Exit Sub

Err_cmd_GetFiles:
If Err.Number = -2147217887 Then 'file already exists in table
rs.CancelUpdate 'skip file
lngNewFileCount = lngNewFileCount - 1 'fix count
Resume Next 'continue
Else 'all other errors
MsgBox Err.Number & &quot; : &quot; & Err.Description
Resume Exit_cmd_GetFiles
End If

End Sub