Import File Names into Access?

[Wayne]

Applies to Microsoft Access 2003

I have second hard drive installed on my PC which I have designated as a
backup drive for my CD collection, I've managed to copy my CDs using Media
Player and save the files as .wmv files, but now what I would like to do is
import those file names (without the extension) into a Microsoft Access
database - hopefully to save me the time of manually entering the Artist
Name and Track Title data, so I have an easy to use music database which I
can update as I add more CDs to my collection.

I've read on the Internet that it is possible to import file names into
Access using VBA code and although the code was provided, this is not
something I have ever done before and I'm a little naive about adding code
to a database etc.

Does anyone know if this is really possible and what I need to do (in simple
straightforward terms) to achieve this.

Any help, advice, pointers or line by line list of what to do would be
enormously appreciated. Other than creating very simple tables, I'm not by
any stretch of the imagination an expert on Microsoft Access.

Many thanks in advance
Wayne

 

[KARL DEWEY]

Use a DOS command by clicking on Windows START - Run and enter CMD then OK.

Type Help DIR and enter to view your choices of data to extract.

Type the drive letter followed by a colon where the CD is at - press enter.

Type DIR then enter to see the file data. Use the switches such as /s that
lets you include subdirectory information - You learned all about the
switches when you entered Help DIR.

To save that informatio to file end you command line (DIR/s /b ) with

C:\MyCDFile.txt

This saves the information to that file on your c:\drive.


Open the *.text file in Excel, convert text to columns, make sure it has
properly parsed the text, add the column names you want, then save it as an
excel spreadsheet, then import the excel file into access

 

[Albert D.Kallal]

You can do what you want, but you will need some coding skills.

(or, as the other poster mentioned, some command line experience).

With zero coding experience, I not sure posting some code will really help
you (it would be like a mechanic friend who comes over and takes apart your
car....and then leaves......what do you do now???).


Here is a code snippet that can read in a dir into a collection for you:

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub

So, what you would do is to modify the code to write out the data to a table

If you "know" coding, the above is a trival suggstion, and would simply
menas that you modify the above coce exxple to write out the results to a
table

Furhter, if you "know" coding, then removing the file extension would also
be easy.


Here is our modifed version of the code that would write out the data to a
table called MyMusic

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer
Dim rstRecords as DAO.RecordSet


startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

' lets write data to a table

set rstRecords = currentdb.OpenRecordSet("MyMusic")

For i = 1 To dlist.Count
rstRecords.AddNew
rstRecords!SongTitle = dlist(i)
rstRecords.Update
Next i
rstRecords.Close
set rstRecords = nothing

End Sub

So, the above code will pull in the file names from a given directory into a
table....