Can Access read a folder & enter the filenames into a database?

[Guest]

I'm using Access 2003. I have many MP3 files in a specific folder on my hard
disk in the format of "<artist> - <title>.mp3". Is there a way Access can
"read" the filenames and enter them into a database?

 

[Bill Edwards]

From Access Help File:
Using the FoundFiles Object

Use the FoundFiles property to return the FoundFiles object. This example
steps through the list of files that are found and displays the path and
file name of each file. Use FoundFiles(index), where index is the index
number, to return the path and file name of a specific file found during the
search.

With Application.FileSearch
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next I
End WithUse the Execute method to begin the file search and update the
FoundFiles object. The following example searches the My Documents folder
for all files whose names begin with "Cmd" and displays the name and
location of each file that's found. The example also sorts the returned
files in ascending alphabetic order by file name.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "cmd*"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If

 

[Douglas J. Steele]

Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\My Documents\My Music\"
strFile = Dir(strFolder & "*.mp3")
Do While Len(strFile) > 0
strSQL = "INSERT INTO MyTable (FileName) " & _
"VALUES (" & Chr$(34) & strFile & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir()
Loop


If you want to split the artist and title out of the file name, try:

Dim strArtist As String
Dim strFile As String
Dim strFolder As String
Dim strSQL As String
Dim strTemp As String
Dim strTitle As String

strFolder = "C:\My Documents\My Music\"
strFile = Dir(strFolder & "*.mp3")
Do While Len(strFile) > 0
strTemp = Left$(strFile, Len(strFile) - 3)
strArtist = Trim(Split(strTemp, "-")(0))
strTitle = Trim(Split(strTemp, "-")(1))
strSQL = "INSERT INTO MyTable (ArtistName, SongTitle) " & _
"VALUES (" & Chr$(34) & strArtist & Chr$(34) & ", " & _
Chr$(34) & strTitle & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir()
Loop


Replace the table and field names in the SQL statement as appropriate.

 

[Guest]

Hi Bill,

Thank you very much for the prompt reply. The problem is that I'm a fairly
basic Access user and have no idea where to enter the information you've
given me. Is it in a Query, Form, Macro, or what? Please excuse my
inexperience, but if you could be a little more specific, I would greatly
appreciate it. It sounds like you've figured out exactly what I want, I just
need a little more help getting there.

Thanks again,
martkaufman

 

[Guest]

Hi Douglas,

Thank you very much for the prompt reply. The problem is that I'm a fairly
basic Access user and have no idea where to enter the information you've
given me. Is it in a Query, Form, Macro, or what? Please excuse my
inexperience, but if you could be a little more specific, I would greatly
appreciate it. It sounds like you've figured out exactly what I want, I just
need a little more help getting there.

Thanks again,
martkaufman

 

[Douglas J. Steele]

That's VBA code that would need to go in a module.

To use it, create a new module, and type a name to use for the code I gave.
For example, when the new module opens in the VB Editor, type

Sub PopulateTable()

then hit Enter. That should cause an End Sub to appear below what you just
typed. Copy all of my code between those two lines. Make sure you set the
value for strFolder appropriately, and that the SQL statement corresponds to
your table and field names.

If you're going to want to be able to run the code more than once, save the
module, but don't name the module the same as the routine (call it
mdlPopulateTable, for instance).

Then, in the Immediate Window (Ctrl-G), simply type PopulateTable and hit
Enter. The code will run and populate your table.

 

[Guest]

Thanks again for the help, Doug. This is great and I'm learning a lot.

I've made a table in the database called "My Table" with two fields
"ArtistName" and "SongTitle". I've made the module "mdlPopulateTable" using
the 2nd code you supplied, entering the correct location of the music
directory on my hard disk. However, when I run the module I get the
following error message "Run-time error '9': subscript out of range". After
clicking on Debug, it highlights the line " strTitle = Trim(Split(strTemp,
"-")(1))". What do I need to adjust?

Martkaufman

 

[Douglas J. Steele]

That implies that the specific file you're reading at the time doesn't
actually have a dash between the ArtistName and the SongTitle. I'm assuming
from your original post that your files have names like:

The Beatles - Love Me Do.mp3
The Beatles - From Me to You.mp3

If you want to be able to trap for that sort of thing, try:

Dim strArtist As String
Dim strFile As String
Dim strFolder As String
Dim strSQL As String
Dim strTemp As String
Dim strTitle As String
Dim varDetails As Variant

strFolder = "C:\My Documents\My Music\"
strFile = Dir(strFolder & "*.mp3")
Do While Len(strFile) > 0
strTemp = Left$(strFile, Len(strFile) - 3)
varDetails = Split(strTemp, "-")
If IsNull(varDetails) Then
strArtist = "Unknown"
strTitle = "Unknown"
Else
strArtist = Trim(varDetails(0))
If UBound(varDetails) > 0 Then
strTitle = Trim(varDetails(1))
Else
strTitle = "Unknown"
End If
End If
strSQL = "INSERT INTO MyTable (ArtistName, SongTitle) " & _
"VALUES (" & Chr$(34) & strArtist & Chr$(34) & ", " & _
Chr$(34) & strTitle & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir()
Loop

If I misinterpretted what your file names look like, let me know.

 

[Guest]

BINGO!

That did the trick! Sorry I wasn't more clear about my file naming, but you
got it right. Thanks again, Douglas, you've been a great help!

Best regards and have a Happy New Year!
Martin Kaufman