All I can say is:
WOW !!!
Impressive. (Now I've got to figure out how you did it. Heck, I didn't even know that a TreeView control was available in VBA).
Recursive file listing help, Please
- Thread starter notarat
- Start date
WOW !!!
Impressive. (Now I've got to figure out how you did it. Heck, I didn't even know that a TreeView control was available in VBA).
"Lagbolt"...That's going to be a strange name for my first-born, but I think that's what I'll call him after looking at that code
I ran it on my Desktop and it looked fantastic!
I then ran it on my share drive, and it was looking really good, right up to the point that it errored out. (not your code's fault, though...)
I found some permissions issues that caused the code to crash.
It seems that one of my in-laws, who had "Full Control" permissions to her section of the tree, (so she could set appropriate permissions for her family) went into the folder properties and removed the Admin Group from her directory structure.
This "permissions" issue tends to happen, from time to time, because each parent has a section of their shared space set aside to record a Living Will video to be played back to their spouse or family in the event they have a major illness or accident and another video in case they pass on unexpectedly...these obviously need to be secure as they are of a very personal nature, but sometimes the parents get a little too zealous in restricting access, or just goof up and set the permissions incorrectly.
In my searches, I saw saw a section of code (pasted below), that handles permissions errors.
Err_Handler:
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT "" ~~~ Permissions Denied ~~~""" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
Resume Exit_Handler
End Function
The "permissions denied" situation is handled a bit more gracefully. Where would I insert that into the code?
Once I corrected the permissions issue, I re-ran it and received a Run-time Error 3075 Syntax Error concerning a missing Operator.
I'm pretty certain I can find it and fix it, but thought you'd want to know about it.
As a quick follow up to my post above, I believe the Syntax error I am receiving with Lagbolt's code (Lagbolt, the coding god. not my son) is due to the code encountering a single quote (possessive) in the filename, such as "Bob's Motorcycle Restoration.ppt". It seems that when it encounters a file with a ' in it, the code expects the rest of the "missing operator".
OOPS! I forgot to post the wording on the error dialog box:
Run-time error '3075'
Syntax error(missing operator) in query expression "Syntax error(missing operator) in query expression "Activity POC's.xls','O:\Shared_Documents\langer.family\Documents\Point of Contact\Langer Family', #1/9/2008 8:03:55 AM#, #9/20/2006 9:52:00 AM#, #12/10/2008 7:46:35 AM#, 'Microsoft Excel Worksheet, 38912, 'Jaimie.Janger', 'mark.langer'.".")'.')'.
Not sure why there is a space in the word, Documents. I typed it as it appears on the error dialog and it doesnt have the space there
Run-time error '3075'
Syntax error(missing operator) in query expression "Syntax error(missing operator) in query expression "Activity POC's.xls','O:\Shared_Documents\langer.family\Documents\Point of Contact\Langer Family', #1/9/2008 8:03:55 AM#, #9/20/2006 9:52:00 AM#, #12/10/2008 7:46:35 AM#, 'Microsoft Excel Worksheet, 38912, 'Jaimie.Janger', 'mark.langer'.".")'.')'.
Not sure why there is a space in the word, Documents. I typed it as it appears on the error dialog and it doesnt have the space there
Awesome Modification!
It went through everything on the first share without any problems. I'm running a comparison right now between the old method and this one to see how fast this scripting pulls the data as compared to the old DOS method.
Hmm..."Lagbolt Jal Thomas"...My kid is going to need either a good therapist or a good left hook...
MarkK
bit cruncher
- Local time
- Today, 15:15
- Joined
- Mar 17, 2004
- Messages
- 8,431
Here's a later version. This handles the case where a file--or any member of the delimited list--may contain an apostrophe, which is one of the errors you got.
Sorry it gets a little complicated, but I wanted to write the 'delimited list' classes for my own library, so thanks for the opportunity, but it gets harder to follow
To handle various Insert errors you can add an error number to the Select Case statement in the DoInsert() method of the cFileData class. You can see I've added a catch-all which will write any errors not otherwise handled. Replace or remove the 'Case 1234' example. Probably you want to handle the permissions error here.
If an error occurs, this version adds the node anyway but with a red 'do not enter' icon, so you get a little more feedback.
Cheers,
Mark
Sorry it gets a little complicated, but I wanted to write the 'delimited list' classes for my own library, so thanks for the opportunity, but it gets harder to follow
To handle various Insert errors you can add an error number to the Select Case statement in the DoInsert() method of the cFileData class. You can see I've added a catch-all which will write any errors not otherwise handled. Replace or remove the 'Case 1234' example. Probably you want to handle the permissions error here.
If an error occurs, this version adds the node anyway but with a red 'do not enter' icon, so you get a little more feedback.
Cheers,
Mark
Thanks Jal! (And Lagbolt!)
Just wanted to say that I am now running it on the root level of my share that holds about 550GB of data.
Performance-wise, it looks like it'll take 24.9 hours to complete, which is about 3 times longer than my old scanning method.
It looks like I'll have to figure out a way to identify and resolve the performance issue, but it works so far and has populated about 145MB of data into the database thus far. I started the script at 10:39am CST and it's now 1:32pm CST.
I have already gotten about 90% of the queries moved over from my old Access program and it's looking really nice now because I have created a new Form menu for it.
I'll upload a copy the whole, completed thing when I get it finished in case anyone else has a need for something like this for their home network.
To say the least, you guys ROCK! I really appreciate all the help!
Just wanted to say that I am now running it on the root level of my share that holds about 550GB of data.
Performance-wise, it looks like it'll take 24.9 hours to complete, which is about 3 times longer than my old scanning method.
It looks like I'll have to figure out a way to identify and resolve the performance issue, but it works so far and has populated about 145MB of data into the database thus far. I started the script at 10:39am CST and it's now 1:32pm CST.
I have already gotten about 90% of the queries moved over from my old Access program and it's looking really nice now because I have created a new Form menu for it.
I'll upload a copy the whole, completed thing when I get it finished in case anyone else has a need for something like this for their home network.
To say the least, you guys ROCK! I really appreciate all the help!
Well here's my dumbed down version now that I have completed my work on it (took a holiday for part of December and January)
I hope you guys don't mind that I stupified it so my relatives could run it on their machines on their parts of the shared drive.
Thanks for all the help!
I hope you guys don't mind that I stupified it so my relatives could run it on their machines on their parts of the shared drive.
Thanks for all the help!
I compact on exit most of the time.
Just for informational purposes, it can handle a share drive using over 950Gb of space that contains over 750,000 files.
It runs in about 8-9 hours on the big share if I'm not doing anything else on the network like streaming and playing Everquest II and using Bit Torrent.
No out of memory errors or anything like that. Runs great!
Again, You guys rock!
Just for informational purposes, it can handle a share drive using over 950Gb of space that contains over 750,000 files.
It runs in about 8-9 hours on the big share if I'm not doing anything else on the network like streaming and playing Everquest II and using Bit Torrent.
No out of memory errors or anything like that. Runs great!
Again, You guys rock!
- Local time
- Today, 23:15
- Joined
- Sep 12, 2006
- Messages
- 15,980
amazing how long it takes to go process a whole hard disk.
i am not surprised there were no stack probems - a hard disk file structure is quite shallow, so there wouldnt be many nested items pushed onto the stack
you can see stack issues quite quickly though, if you do say a recursive fibonacci function then it runs really slowly for a relatively low fibonacci number.
i am not surprised there were no stack probems - a hard disk file structure is quite shallow, so there wouldnt be many nested items pushed onto the stack
you can see stack issues quite quickly though, if you do say a recursive fibonacci function then it runs really slowly for a relatively low fibonacci number.
Have you ever used "LS file list generator"? It is a tiny little program, no installation needed, can run it from a flash disk if you want, it is written in C# i believe, very very fast...
I work with electronic evidence so I am constantly dealing with millions upon millions of files, terabytes of data... this little utility will output what you need, FAST, you can output to a CSV, htm, or text and then import to access, it will give you all you need, in my 6 years in the field i have not seen a utility as fast and small as this.
http://www.aplusfreeware.com/categories/util/files/ls.zip
I work with electronic evidence so I am constantly dealing with millions upon millions of files, terabytes of data... this little utility will output what you need, FAST, you can output to a CSV, htm, or text and then import to access, it will give you all you need, in my 6 years in the field i have not seen a utility as fast and small as this.
http://www.aplusfreeware.com/categories/util/files/ls.zip
I was using LS previously, but it didn't give me the information I needed. (Specifically, Owner)
I needed that information as well to identify when someone took ownership of a folder structure and the files within away from my Admin group.
I do agree though that it is a very nice and fast utility. I used it for a couple years.
I needed that information as well to identify when someone took ownership of a folder structure and the files within away from my Admin group.
I do agree though that it is a very nice and fast utility. I used it for a couple years.
I went ahead and downloaded the AccessEnum.zip program from the MS Tech Net site and I threw a Macro button on a new tab of the database to call it.
Now I can pull a listing of all folders and files, listing the Security Group (or groups) that have rights to the fole/folder and what level of rights they have.
Now I just gotta find a way to call the API to retrieve that information via VBa in Access, instead of using the EXE (as you can probably tell, I try to minimize the amount of exe files as much as possible, lol)
Now I can pull a listing of all folders and files, listing the Security Group (or groups) that have rights to the fole/folder and what level of rights they have.
Now I just gotta find a way to call the API to retrieve that information via VBa in Access, instead of using the EXE (as you can probably tell, I try to minimize the amount of exe files as much as possible, lol)
