add filenames

[colinod]

does anyone know if its possible to add all the filenames in a folder to an access database i.e.

a folder contains

file1.mp3
file2.mp3
file3.mp3

i want a code that will search a folder on our local server and add all these filenames to a particular field in the DB

[ADezii]

@colinod

1. How to you want these File Names to appear in the Field?
   

   file1.mp3, file2.mp3, file2.mp3
   file1.mp3 file2.mp3 file2.mp3
   file1.mp3:file2.mp3:file2.mp3

2. What are you basing the Filespec on?
3. Where does the actual Filespec exist? (Text Box, Combo Box, etc.)
4. What is the Field Name?
5. What is the Name of the Table in which the Field exists?
6. Are there other Fields in this Table, and if so, what are their Names and Data Types?
7. etc...

[OldBirdman]

If the question is "How do I read a Directory", the answer is simple:

    strFileName = Dir(strCompleteDrivePath)
Do Until strFileName = ""
    '<< Code to handle file name strFile >>
    strFileName = Dir()  'Get next name - May not be in alphabetic order
Loop

[colinod]

ADezii

i want one file name per field, they are in a table called mp3 in a field called File, its a hyperlink type of field, the other field in this tabel are as follows
mp3id - autonumber

File - hyperlink - comlete link to the file e.g.\\Pc04\artist voice clips\GILBREATH\GILBREATH - MASTER CD\AlexandraGilbreath.mp3

location - hyperlink - comlete link to the folder e.g.\\Pc04\artist voice clips\GILBREATH\GILBREATH - MASTER CD

Artist - text - just the name of the artist Artist
Alexandra Gilbreath

Information - memo - just info text if needed

filename - text just the filename - AlexandraGilbreath.mp3

do you need any more info, i can attach the db if you want

[ADezii]

@colinod
The following code will search for all *.mp3 Files in the C:\Misc Files\mp3s Folder. For every File it finds, it will write the Absolute Path of the File to the [File] Field (Type HYPERLINK) in the Table mp3. It will also write the Folder Name to the [Location] Field (Type HYPERLINK) in the mp3 Table. Both of these Fields will be functional Hyperlinks as long as they are defined as such. The Name of the retrieved Files only will also be written to the [Filename] Field as Text. The code has been thoroughly tested and is fully operational. Any questions, please feel free to ask.

Dim strFileName As String
Dim MyDB As DAO.Database
Dim rstMP3 As DAO.Recordset
Dim strMsg As String
 
Const conFOLDER_PATH As String = "C:\Misc Files\mp3s\"
Const conFILE_SPEC As String = "\*.mp3"
 
strMsg = "No Files were found in " & conFOLDER_PATH & " with a File Specification " & _
         "of (" & Right$(conFILE_SPEC, 5) & ")"
 
If Dir(conFOLDER_PATH & conFILE_SPEC) = "" Then
  MsgBox strMsg, vbExclamation, "No Files Found"
    Exit Sub
End If
 
Set MyDB = CurrentDb()
Set rstMP3 = MyDB.OpenRecordset("mp3", dbOpenDynaset, dbAppendOnly)
 
strFileName = Dir(conFOLDER_PATH & conFILE_SPEC)
 
Do While strFileName <> ""
  With rstMP3
    .AddNew
      ![File] = conFOLDER_PATH & strFileName & "#" & _
                        conFOLDER_PATH & strFileName
      ![Location] = Left$(conFOLDER_PATH, Len(conFOLDER_PATH) - 1) & _
                        "#" & Left$(conFOLDER_PATH, Len(conFOLDER_PATH) - 1)
      ![Filename] = strFileName
    .Update
  End With
    strFileName = Dir()
Loop
 
rstMP3.Close
Set rstMP3 = Nothing

P.S. - Change the value of the conFOLDER_PATH and conFILE_SPEC Constants to suit your own specific needs.

[colinod]

hi thanks for that, will this search in subfolders, so if i have various folders inside the mp3 folder will it find them also??

Its also throwing back an error at line 2

[ADezii]

@colinod

does anyone know if its possible to add all the filenames in a folder to an access database

No mention was ever made concerning Sub-Folders, so the code only returns Files matching a Filespec in a specific Folder. It can, however, be adapted to search Sub-Folders as well.

Its also throwing back an error at line 2

You are probably missing a Reference to the Microsoft DAO X.X Object Library

[msaccessusr]

Anyone know how the code above would be adapted in order to include files within subfolders and/or the code I have listed below? I'm using similar code that I had found posted on this website a few years ago to list files in a specific file folder, and I am now trying to adapt it so I can list files within the subfolders. The code I've been attempting to adapt is below:

Sub GetFiles(strPath As String)
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
Set rs = Nothing
MsgBox ("Directory list is complete.")
End Sub

[ADezii]

Here is some code that uses 1 Public Function, 2 Private Functions, the Dir() Function, a Collection, and Recursion to Print to the Immediate Window all *.mp3 Files that were found in a specified Folder and all its Sub-Folders. You can easily adapt it to suit your needs. Follow these Steps closely.

1. Copy and Paste the following Function to a Standard Code Module:
   

   Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
                   Optional bIncludeSubfolders As Boolean)
   On Error GoTo Err_Handler
    
   Dim colDirList As New Collection
   Dim varItem As Variant
    
   'colDirList - Files are rturned in this Collection
   Call FillDir(colDirList, strPath, strFileSpec, True)
    
   For Each varItem In colDirList
     Debug.Print varItem
   Next
    
   Exit_Handler:
     Exit Function
    
   Err_Handler:
     MsgBox "Error " & Err.Number & ": " & Err.Description
     Resume Exit_Handler
   End Function

2. Copy and Paste the following 2 Functions to the 'same' Standard Code Module used in Step 1.
   

   Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
                            bIncludeSubfolders As Boolean)
   'Build up a list of files, and then add add to this list, any additional folders
   Dim strTemp As String
   Dim colFolders As New Collection        'holds Directory Names
   Dim vFolderName As Variant
    
   'Add the files to the folder.
   strFolder = TrailingSlash(strFolder)    'ensures Folder Names end with a Slash "\"
   strTemp = Dir(strFolder & strFileSpec)  'adds FileSpec to the Folder Name
    
   Do While strTemp <> vbNullString
     colDirList.Add strFolder & strTemp    'Folder\ & FileSpec
     strTemp = Dir                         'recursively calls the Dir() Function
   Loop
    
   If bIncludeSubfolders Then      'Include Sub-Directories?
     'Build collection of additional subfolders.
     strTemp = Dir(strFolder, vbDirectory)     'passed Folder Argument
    
     Do While strTemp <> vbNullString
       If (strTemp <> ".") And (strTemp <> "..") Then      'Ignore . and ..
         If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then      'Is it a Directory?
           colFolders.Add strTemp      'Adds Directory/Folder to colFolders Collection
         End If
       End If
         strTemp = Dir                     'recursively calls the Dir() Function
     Loop
    
     'Call function recursively for each subfolder.
     For Each vFolderName In colFolders
       Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
     Next vFolderName
   End If
   End Function

   Private Function TrailingSlash(varIn As Variant) As String
   If Len(varIn) > 0& Then
     If Right(varIn, 1&) = "\" Then
       TrailingSlash = varIn
     Else
       TrailingSlash = varIn & "\"
     End If
   End If
   End Function

3. Call the Entry Level Function and pass to it the Path, File-spec, and either True or False to include Sub-Directories. The following code will list all *.mp3 Files in the Windows Folder as well as any *.mp3 Files that may exist in Sub-Folders under the Windows Folder:
   

   Call ListFiles("C:\Windows", "*.mp3", True)

4. Any questions, please feel free to ask.