Create Directory for records in MS Access 2007

Question

Is it possible to create a directory folder for individual records in Access 2007. For example

tblUser
ID
firstName
surName

When adding a record, would create a folder C:\userdatabase\Surname,firstName,ID

Could see this being useful in situations for example where a large amount of images/files would need to be associated with a record. Access would create and link to a directory for each record.

Thanks in advance for any advice

Noel

Kerin

Answer 1

You can use the VBA MkDir statement to make a directory.

For example if you want to create a folder whose name matches one of your ID values, as a sub folder under the directory where your database is stored, you could use this code:

MkDir CurrentProject.Path & "\" & ID

Effeminacy

Answer 2

While these are all interesting answers to the question asked, I have to wonder why the original poster thinks this is necessary. If you're using your database to keep track of the information, it shouldn't really matter where the files are stored.

Of course, if you need to have access to the files from the file system, this can be a way to do it, but in my experience, usually not the most useful way (it segregates the data to too high a degree).

So, I'm basically saying to go back and question whether the design is correct or not. It very well may be, but you should be certain before committing to something like this, which I consider to be unnecessarily convoluted.

Obau

Hi David. It would be that I would need to have access to the file system. Essentially the database I intend to create stores clients details regarding their homes. This will include anything up to 30 photographs. Obviously i do not want to store these in the db, as they will take up a lot of data. If a directory sytem was automatically created so that we had db/user,id/photos. I feel it would be easier for the db users to add and mantain the relevant files for the relevant records if the directorieswere generated automatically. – Kerin

I need to do my research but hopefully it will be possible within access to read the contents of a directory and display this in a record. So if i go to the record for a user/id, a list (or preview) of the images in the directory db/user,id/photos will be available. – Kerin

Sounds fairly reasonable as a justification for what you're doing -- if it were one file per client, then not so much. – Obau

The way to handle this for showing the pictures in Access is, I think to use an unbound image control and a listbox. In the OnCurrent event, you'd do a Dir() on the folder to get the list of pictures and then use that to populate the rowsource of your listbox. Then the AfterUpdate event of the listbox would load the image in the unbound image control. It also might be possible to bind the image control to the listbox, but I haven't worked with the new A2007 image control, so couldn't say for certain. I'd suggest you post a new question on this so it doesn't get buried here in comments. – Obau

Answer 3

To create the entire directory structure with one command:

link text

Matilda