File management system

[renovator]

Hi,
I am helping a friend with a one man law firm and I am setting up a legal file management database to convert a hundred boxes of paper documents and electronic documents into one system.
I want to automate as many steps as possible because he work endless hours.
My first problem is how do I detect new files in huge list of directories from within Access

The rough design is
1 - create a table calls filed_docs
file number auto generated primary key
file name
hyperlink
creation date
possible duplicate ( If file name is the same as another)
original directory path
last change date



2. create table filed_docs_metadata - the file meta data which varies by file type
3- create table filed_docs_tags which allows the user to enter multiple tags
4. create table filed_docs_citations which scraps the file for citations
5- scan all selected folders for word, pdf, html, ppt documents
6- copy these files over to a new directory called added_to_the_database and update the above files if no match is found on file name, creation date, last change date
7- check online for a file with the same name - sort of like how the link button works in word
regards renovator

[CJ_London]

My first problem is how do I detect new files in huge list of directories from within Access

I would use the filesystem object - see this link for more information - it is for excel, but the same principle applies

http://software-solutions-online.com...n-a-directory/

Once you have the file object, you can look at the created/amended dates to identify new files

you can create a function to scan through a directory, dropping down into subfolders, scanning through those, dropping down again etc

Note that these searches are sequential (i.e. not indexed), so may take some if you have many folders/files, so as you find files and store the path in your db brake down into a couple of components such as path and filename to take advantage of indexing (putting a * in the front of a search e.g. ...like '*Name*' does not use indexing).

If you consider the windows folders to be part of your db system and therefore subject to enforced naming conventions you can store files logically - e.g. perhaps something clientid_clientname_doctype_docversion, and organise the folders in a similar manner.

Rather than storing name and hyperlink, consider using shellexecute to open a file

ShellExecute(0, "Open", """" & DBPath & me.filename & """", vbNullString, vbNullString, 1)


Alternatively, consider using sql server (/express) for the backend - it has functionality for managing files.

[redbull]

you can create a function to scan through a directory, dropping down into subfolders, scanning through those, dropping down again etc

Just a tip on this, the dos tree command would be a quick way of getting the directories folder structure, and files. Just run it in cmd.
tree "c:\targetdirectory" > "c:\mydirectorytree.txt" /A /F

Then use the fso to loop each line. The statement above might only show the directories... not the files, but if you look up the attributes of the tree command you can fix that.



Parent directories will start with a "+"
first sub directories will start with a "| +"
second sub directories will start with a "| | +"

[ssanfu]

I am helping a friend with a one man law firm and I am setting up a legal file management database

I would strongly suggest not having the legal files on C drive.
Having the legal files on a different drive will reduce the number of files that you will have to search.
And backing up the legal files will be easier.

Whether there is a 2nd physical hard drive in the desktop/tower or an external USB hard drive, not having the legal files on "C" drive is much safer (IMO).
Its not a matter of IF the hard drive will fail, it is just WHEN the drive will fail! I forced my CPA to put tax return data/files on an external drive and did full backups daily.
Sadly, he had 3 hard drive failures in a year and a half. But the backups saved him.....


You do know the 3 rules of computing, right???
Rule #1 is Back up
rule #2 is Back up
and Rule #3 is ..... Back up!!!