Re: creating Access table from directory with files

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).

On Sun, 7 May 2006 11:05:23 +0200, "SonyMan"
<surya.hardjosusono@xxxxxxxxxx> wrote:

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.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.