Update table with contents of a directory

[lwwhite]

I'm using Access 2003, Win XP. I want to update a table with the contents of a directory and subdirectories. I have a directory structure like this:

FolderA
Subfolder1
[multiple XML files]
Subfolder2
[multiple XML files]

I want to place each of those XML file names into two fields in a table called tbl_sourcedocs. I'd like to place the path into tbl_sourcedocs.sourcedoc_path and the file name itself into tbl_sourcedocs.sourcedoc_name.

I then want users to be able to select the appropriate file from a list box on a form. If I can get the table built, that part should be straightforward.

Assuming I can get this working, how is the table update triggered? Say a user added a file to the directory then returned to the db...would it automatically be available for selection in the list box? Would the user have to manually trigger some table update event?

I'm not a programmer, so I need very detailed instructions. Actual code would be much appreciated, if possible :)

Thanks for any help.

[NeoPa]

1. Getting the file system information within VBA.
You can use the Dir() function. It only returns one item at a time so you will need to code it to get all the relevant files and directories. I've quoted the first part of the Dir() Help page.

Dir Function


Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir[(pathname[, attributes])]

The Dir function syntax has these parts:

Part Description
pathname Optional. String expression that specifies a file name — may include directory or folder, and drive. A zero-length string ("") is returned if pathname is not found.
attributes Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.



Settings

The attributes argument settings are:

Constant Value Description
vbNormal 0 (Default) Specifies files with no attributes.
vbReadOnly 1 Specifies read-only files in addition to files with no attributes.
vbHidden 2 Specifies hidden files in addition to files with no attributes.
VbSystem 4 Specifies system files in addition to files with no attributes. Not available on the Macintosh.
vbVolume 8 Specifies volume label; if any other attributed is specified, vbVolume is ignored. Not available on the Macintosh.
vbDirectory 16 Specifies directories or folders in addition to files with no attributes.
vbAlias 64 Specified file name is an alias. Available only on the Macintosh.

2. Triggering the updates of the table in the database.
I think this may well have to be a command button selected by the user. You would need to code it to do a recordset.Requery. You could always add the same code into an event procedure but it wouldn't be able to synchronise exactly with your update.
3. Doing all the coding for you.
Sorry, not the idea to my mind. I'll happily answer questions I can but am a little suspicious of such requests so avoid acquiescing :(.
If you're not very comfortable coding then it's time to learn. This we can and will help with, as well as answering questions along the way.