By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

add filenames

100+
P: 347
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
Mar 2 '09 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,619
@colinod
  1. How to you want these File Names to appear in the Field?
    Expand|Select|Wrap|Line Numbers
    1. file1.mp3, file2.mp3, file2.mp3
    2. file1.mp3 file2.mp3 file2.mp3
    3. 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...
Mar 2 '09 #2

100+
P: 675
If the question is "How do I read a Directory", the answer is simple:
Expand|Select|Wrap|Line Numbers
  1.     strFileName = Dir(strCompleteDrivePath)
  2. Do Until strFileName = ""
  3.     '<< Code to handle file name strFile >>
  4.     strFileName = Dir()  'Get next name - May not be in alphabetic order
  5. Loop
Mar 2 '09 #3

100+
P: 347
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
Mar 2 '09 #4

ADezii
Expert 5K+
P: 8,619
@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.
Expand|Select|Wrap|Line Numbers
  1. Dim strFileName As String
  2. Dim MyDB As DAO.Database
  3. Dim rstMP3 As DAO.Recordset
  4. Dim strMsg As String
  5.  
  6. Const conFOLDER_PATH As String = "C:\Misc Files\mp3s\"
  7. Const conFILE_SPEC As String = "\*.mp3"
  8.  
  9. strMsg = "No Files were found in " & conFOLDER_PATH & " with a File Specification " & _
  10.          "of (" & Right$(conFILE_SPEC, 5) & ")"
  11.  
  12. If Dir(conFOLDER_PATH & conFILE_SPEC) = "" Then
  13.   MsgBox strMsg, vbExclamation, "No Files Found"
  14.     Exit Sub
  15. End If
  16.  
  17. Set MyDB = CurrentDb()
  18. Set rstMP3 = MyDB.OpenRecordset("mp3", dbOpenDynaset, dbAppendOnly)
  19.  
  20. strFileName = Dir(conFOLDER_PATH & conFILE_SPEC)
  21.  
  22. Do While strFileName <> ""
  23.   With rstMP3
  24.     .AddNew
  25.       ![File] = conFOLDER_PATH & strFileName & "#" & _
  26.                         conFOLDER_PATH & strFileName
  27.       ![Location] = Left$(conFOLDER_PATH, Len(conFOLDER_PATH) - 1) & _
  28.                         "#" & Left$(conFOLDER_PATH, Len(conFOLDER_PATH) - 1)
  29.       ![Filename] = strFileName
  30.     .Update
  31.   End With
  32.     strFileName = Dir()
  33. Loop
  34.  
  35. rstMP3.Close
  36. Set rstMP3 = Nothing
P.S. - Change the value of the conFOLDER_PATH and conFILE_SPEC Constants to suit your own specific needs.
Mar 3 '09 #5

100+
P: 347
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
Mar 26 '09 #6

ADezii
Expert 5K+
P: 8,619
@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
Mar 26 '09 #7

P: 1
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
Apr 7 '09 #8

ADezii
Expert 5K+
P: 8,619
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    2.                 Optional bIncludeSubfolders As Boolean)
    3. On Error GoTo Err_Handler
    4.  
    5. Dim colDirList As New Collection
    6. Dim varItem As Variant
    7.  
    8. 'colDirList - Files are rturned in this Collection
    9. Call FillDir(colDirList, strPath, strFileSpec, True)
    10.  
    11. For Each varItem In colDirList
    12.   Debug.Print varItem
    13. Next
    14.  
    15. Exit_Handler:
    16.   Exit Function
    17.  
    18. Err_Handler:
    19.   MsgBox "Error " & Err.Number & ": " & Err.Description
    20.   Resume Exit_Handler
    21. End Function
  2. Copy and Paste the following 2 Functions to the 'same' Standard Code Module used in Step 1.
    Expand|Select|Wrap|Line Numbers
    1. Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
    2.                          bIncludeSubfolders As Boolean)
    3. 'Build up a list of files, and then add add to this list, any additional folders
    4. Dim strTemp As String
    5. Dim colFolders As New Collection        'holds Directory Names
    6. Dim vFolderName As Variant
    7.  
    8. 'Add the files to the folder.
    9. strFolder = TrailingSlash(strFolder)    'ensures Folder Names end with a Slash "\"
    10. strTemp = Dir(strFolder & strFileSpec)  'adds FileSpec to the Folder Name
    11.  
    12. Do While strTemp <> vbNullString
    13.   colDirList.Add strFolder & strTemp    'Folder\ & FileSpec
    14.   strTemp = Dir                         'recursively calls the Dir() Function
    15. Loop
    16.  
    17. If bIncludeSubfolders Then      'Include Sub-Directories?
    18.   'Build collection of additional subfolders.
    19.   strTemp = Dir(strFolder, vbDirectory)     'passed Folder Argument
    20.  
    21.   Do While strTemp <> vbNullString
    22.     If (strTemp <> ".") And (strTemp <> "..") Then      'Ignore . and ..
    23.       If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then      'Is it a Directory?
    24.         colFolders.Add strTemp      'Adds Directory/Folder to colFolders Collection
    25.       End If
    26.     End If
    27.       strTemp = Dir                     'recursively calls the Dir() Function
    28.   Loop
    29.  
    30.   'Call function recursively for each subfolder.
    31.   For Each vFolderName In colFolders
    32.     Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
    33.   Next vFolderName
    34. End If
    35. End Function
    Expand|Select|Wrap|Line Numbers
    1. Private Function TrailingSlash(varIn As Variant) As String
    2. If Len(varIn) > 0& Then
    3.   If Right(varIn, 1&) = "\" Then
    4.     TrailingSlash = varIn
    5.   Else
    6.     TrailingSlash = varIn & "\"
    7.   End If
    8. End If
    9. 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:
    Expand|Select|Wrap|Line Numbers
    1. Call ListFiles("C:\Windows", "*.mp3", True)
  4. Any questions, please feel free to ask.
Apr 7 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.