Use the file system object to enumerate the files. For each file found,
INSERT it into a "temporary" table (let's call it NewInventory, and it has
the same structure as the "main" Inventory table) in the database. Once all
the data is gathered, you can use SQL statements to identify those that have
been added:
SELECT ... FROM NewInventory as N
WHERE Not Exists (SELECT 1 FROM Inventory as I
WHERE I.FileName = N.FileName)
removed:
SELECT ... FROM Inventory as I
WHERE Not Exists (SELECT 1 FROM NewInventory as N
WHERE N.FileName = I.FileName)
Or changed:
SELECT ... FROM Inventory as I INNER JOIN NewInventory as N
ON N.FileName = I.FileName
AND N.<some other attribute> <> I.<same attribute>
You can create INSERT/UPDATE/DELETE statements based on the above logic to
move the data/updates to the main Inventory table, i.e.:
INSERT INTO Inventory (<Column List>)
SELECT ... FROM NewInventory as N
WHERE Not Exists (SELECT 1 FROM Inventory as I
WHERE I.FileName = N.FileName)
DELETE FROM Inventory
WHERE Not Exists (SELECT 1 FROM NewInventory as N
WHERE N.FileName = Inventory.FileName)
UPDATE Inventory
SET <Column> = N.<Column>, ...
FROM NewInventory as N
WHERE N.FileName = Inventory.FileName
AND N.<some other attribute> <> Inventory.<same attribute>
[AND ... ?]
Or you can use any number of other approaches to matching records up during
processing. However, with a reasonably limited number of records, using the
above described approach will be very simple and perform acceptably (the
number of files on an average system would be considered "reasonably
limited" in a database sense).
HTH,
Tore.
-----Original Message-----
From: Roy Murdock [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 24, 2002 2:32 PM
To: ActiveServerPages
Subject: Importing files names into an access DB
I have a folder that contains the names of mp3 files used in a project. I
want a table in my DB to contain the name of each file and reflect additions
or removals. Does access have functions for this? If not does anyone have
suggestions about how to code a module in VBA?
Thanks for any help
Roy
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
