creating Access table from directory with files

  • Thread starter Thread starter SonyMan
  • Start date Start date
S

SonyMan

Hello,

How can I create an Access table from a directory using different file
attributes:

(1) The directory is for example c:\music.

(2) In this directory there are different files with te following name
structure: "band name" - "song title"."music format". Every once in a while
this directory will be updated.

(3)-a Based on this name structure I'd like to create an Access table with
related headers ("band name", "song title" and "music format).

(3)-b Every once in a while I'd like this table to be updated, only adding
new files/songs.

If possible, could this be done with macro's (and no Visual Basic, because I
have this spaghetti programming allergy ... :-))?

Kind regards, Surya H.
 
Hi Surya,

It can't be done with macros. It will need *either* some moderately
sophisticated VBA or a few simple manual steps:

1) Create a text file containing the file names.
a) Open a Windows command prompt.
b) Use the CD command to navigate to "C:\music"
c) Use a command like this to create the text file:
DIR *.* /S > "C:\FileList.txt"

2) Open this file in Word or your favourite text editor.
a) Use find and replace to change all the " - " and all the "." to tab
characters ("^t" in Word)
b) At the beginning of the file, type the names of your fields with tabs
between them, e.g.
BandName<tab>SongTitle<tab>Format
c)Save the file as plain text. You now have a tab-delimited file that
Access can link to.

3) In Access, create a table with the fields you need, including
BandName, SongTitle and Format (life is usually simpler if you don't use
spaces in field names). Either include all three of these fields in the
table's primary key, or (if you want a different primary key) create a
unique index that includes the three fields.

4) Use File|Get External Data|Link to link to the text file.

5) Create an Append query that appends data from the linked table to
your permanent table.

6) The first time you run the query, it will import all the records from
the text file. Subsequently, if you always use the same name and
location for the text file, the query will only append the new records
(the index will prevent existing ones from being duplicated).
 
Hi John,

Thanks for the detailed description.

Can this be automated or what can be done with VBA?

Kind regards.
 
Another alternative is to use the SHELL command to execute a DOS DIR command
that will automatically create a text file that you can import into Access.
Something like this:

htask = Shell("cmd.exe /c dir " & strStartDir & " > c:\mylist.txt")

'DIR Command Parameter explanation:
'/c tells the command interpreter to terminate after executing the command
'\ indicates start at the root of the current drive
'/a:-d indicates I want only files,not directories
'/s all subdirecties
'/b Lists each directory name one per line (including the filename
'extension).
'> c:\myfile.txt the file to store the list in.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "DirectoryList.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top