Wholly Crap

[SaraMegan]

I was just approached by one of our attorneys about adding something to my database which I think is borderline ridiculous. I basically just want affirmation... Or contradiction is fine, too, I suppose...

Anyway, my database tracks all of the files we have in the office. It shows whose case they are, who is assisting on the case, what type of case, when it got here, where it has been or where it is, the status of the case, and legal action that has been taken on the case. We have over 1200 files in the database. It is out on the server (Novell) for the four secretaries and the three attorneys to use.

This attorney just asked me if it had the capability to hold or link to copies of the entire file. Meaning, he wants to be able to scan in the entire (on average, three inch) file and be able to pull all of that up using the database. I told him it's possible to link the database to other office documents, but I don't know much more about it.

BUT, even if it *can* be done, isn't that kind of ridiculous and a giant waste of space? They need to keep all of the paper copies anyway, and putting all those documents out on the server would just take up so much space.

I have to talk to the head attorney about it, and I'd like to be better informed for when I do so... so if anyone can tell me either why I'm right or why I'm wrong or anything at all, I would very much appreciate it.

Thanks in advance,

A Stressed Sara

 

[Hayley Baxter]

Hi Sara

I currently have a database which links to lots of contract documents, although I do not have nearly as many documents as you need to hold. The contracts will however increase as we keep old ones for tracking purposes and all the new ones that get signed off. Like you we need to keep hard copies of this but I've scanned in the documents to allow everyone accross our different sites to have more efficient access to these.

Hay

 

[SaraMegan]

Does that take up a lot of space? Do you think I should try it, but recommend a limitation? If each case has five one-page word documents, that's about 20 kb each, for 100 total per file, making it well over 120,000 kb, and as the database continues to grow, that will also expand. And we're not the only ones on the server... We're a State Agency with several different offices relying on the server.

I don't know much about networks and servers, but I know the network guy here and I don't think this idea will make him happy... Any further recommendations on how to go about this?

--Sara

 

[Alexandre]

My 2 cents:
You would not embed the documents in your DB, just register their path (this assumes that on registering a file, you create a copy into a secured folder for users not to delete/move them afterwards). To open those files from your DB, you would use either an hyperlink data type, or the Shell function.

It can be really worth for new working documents if they are created on computers in a network (users creates a file, opens and register it through a db front-end on his computer, a copy is created, registered and centralised in a special folder of the server...).
It is till do-able with scanned files, but they are much more space consuming... And if only for that use, you should make sure that are really committed in doing the work of scanning and archiving their files (time consuming and tedious... they may give up rapidly). Anyway, big hard disks are cheap nowadays...

 

[bodhran1]

I'm still a newbie (so go easy on me, eh?) , but, if I had to tackle this, I'd look into storing all those "other documents" in PDF format, then just give a link to them in Access?


Hope this helps,

dp

 

[SaraMegan]

Thanks

Thanks Hayley, Alex, and Neighbor! I'll look into all of that and see what I can finagle. Hopefully we can work something out... I was just so excited because my database was all done, and then he hits me with that... Lawyers...

bodhran1 - Where in Mass is Uxbridge? I'm right outside Concord, NH. (Actually, I work *in* Concord...)

Hopefully heading into a smooth afternoon

--Sara

 

[The_Doc_Man]

Sara

My experience tells me that you need to confront that attorney with an informed "project plan" for case-file management. In passing I'll mention what I can about your question and your requirements.

The first and foremost question you should ask is for a complete specification of the requirement. You want to know a few things right up front.

1. What is the average size of these files?

2. How many files are likely for a given case?

3. How many cases are expected in a given long time (like, at least per month, but per quarter or per year is better)?

4. What is the retention time for these files? (Also known as life cycle). I.e. when can I start dumping these files to an off-line storage facility? Or the round file?

5. What are the security requirements for the files?

6. Can any of the law partners access the files remotely now? Is that going to be a requirement in the future? (This is a MASSIVE security question.)

Now, here is why you want this: Disks have finite capacity. The amount of space you are talking about at any time is determined by the answers you got for questions 1-4:

file space = Time Integral (dsize/dt) of (( (avg. file size) * (# files/case) * (# new cases)) - ((avg. file size) * (# files/case) * (# cases retired)))

Yep, calculus. Ugly, ain't it? In English, add the size for all new cases entered this time period, subtract the size for all cases retired from on-line status this time period. Carry forword all cases added and not yet retired.

Now, here is the catch: There will be an induction period in this equation where you haven't reached the "time to retire oldest cases" where you will reach the most amount of space you need to have on your disk. (Or disks, plural. VERY plural.) You can compute how much space this is, talk with your systems admin person, and tell the attorneys how much this requirement will cost. Make sure your sys admin person mentions the cost in terms of consumables, too. Like, backup tapes to hold the copies of the files you are keeping. (You ARE performing backups on a regular basis, aren't you?)

Then, when they stop screaming from this one, point out that the longer the time period before they retire the cases, the higher this peak will get. And if they don't want to retire ANY cases, then the cost will slowly build towards infinity. (Most servers won't handle an infinite number of disks, though, so find out what the real drop-dead point is from your systems admin person.) At which point you have to either upgrade the server to handle more disks, or add another server to the office intranet to handle more disks. In either case, the answer is "more disks." That cost should also get some attention.

Now, when they are finished jangling from THAT little shock, ask them about the security requirements and remind them that unless they transfer files using one of the privacy tools (PGP or PKE), they are exposing files across the net. Last time I heard anything about this at all, it was grounds for a breach of the lawyer-client privilege. I.e., as soon as this was done, the lawyer was deemed to have breached the wall of privileged communication, so could be compelled to testify against his/her own client. This ONLY applied to cases where no encryption method was used. So bring along your friendly local security manager on THAT one. Then point out the added costs of assuring that the files are not exposed to random browsing through the internet (if anyone needs remote access.)

As to storing the files, I agree with Alexandre. Just store the file specification so you can open it later. I know you are a "newbie" so I won't bother you with the details. Besides, I have never tried to open a PDF from Access anyway, so I cannot advise you on the fine details. However, in general, it is possible for you to use automation to launch an application. I have seen the Acrobat library and I know Access can see it, but I have no idea what methods are exposed if you add it to your references.

At least in theory, you can do something like creating an application for acrobat (reader), then use whatever method they use to open a file. If you make the application visible and instruct it to go Full-Screen, it will look like the database opened the document via OLE. But in fact, it would be opened by object automation, not really the same thing.

Now, the reason you only want to keep the file spec and not anything else is file space issues. Databases have size limits. The BEST way to avoid the size limits is to store only the smallest, cheapest possible amount of info. Storing the specification is usually cheaper than just about anything else.

This was a little disjointed but it might help you at least a little bit. Good luck!

 

[bodhran1]

Sara,

Uxbridge is due south of Worcester, just minutes from the RI border.

It's a nice quiet town, but located close enough to Boston and Providence for nights out.

It's growing fast (like most burbs in Mass) very quickly, but I wouldn't mind a settling down here once prices on homes get a bit more realistic.

Looking at a map of NH, I notice we have the same surrounding towns. Webster, Hopkinton and Sutton.

You'd think that we could have been more creative with names, back when? LOL

dp

 

[SaraMegan]

Thanks, Doc Man! That's quite a brain full! (Especially when you whipped out the Calculus. Woah.)

Nobody access the db remotely, so we're at least safe there. Also, the network administrator has only given access to the people in this office, who each have network passwords and the db itself is password protected.

Files can hang around here for up to 26 years. (Yup, some files are older than me...)

Judging by all you've said, I don't think it is something we can do. If it costs anything, it's basically off limits. The state doesn't want to pay for anything it doesn't *really* need. And nobody will believe that it needs all that, especially when the records are in paper format directly across the hall.

Anyway, I've gone off, I think... I'll figure out what I can about what you said and go from there. It's kind of a bummer, our network dude hates people... I don't think he hates me, actually, but he hates everyone in my office and very much loves talking over my head and making me feel like a dolt.

Anyway, thank you. That was exactly what I was looking for.

--Sara

 

[SaraMegan]

dp,

Funny about that Map thing. I'd like to say we were here first, but I don't think that's true. I like New England a lot. I'm moving to Chicago for a few years, but I plan on having a family in New England, probably New Hampshire.

Nice that you're close to Boston and Providence. I'm not close to much at all. Boston is 90 minutes with light traffic (which doesn't happen...)

It's a good thing I've got this great job to keep me busy instead.

--Sara

 

[SaraMegan]

P.S.

Doc Man,

Yes, I am backing up my database, dually, daily. I've learned *that* lesson, thank you very much!

--Sara

 

[The_Doc_Man]

Thought that would be what you needed. When anyone says "let's store this FOREVER" all you need to do is tell them just how much FOREVER costs. They usually fold up like trailers in a tornado. Nobody realizes just how expensive long-term storage can get, despite the continuing decrease in storage costs. There is a variant of Murphy's Laws in effect here - the amount of data to be stored always increases faster than the cost to store it decreases - or something like that.

I learned my own variant of this lesson with a branch of the Dept. of Defense. Congress passed laws talking about 30 years of data storage for certain classes of personnel information, so on my main system, I have 1.2 Terabytes worth of disks spinning at the moment. Not all of that is occupied, but it is there because we might actually have to store some things for that long, and it should now be obvious just how quickly that can build up.

Sorry you have a dork as a network manager. From your description, he qualifies as a dork, anyway. Antisocial, talks over your head intentionally or carelessly, seems uncooperative. Did you get this schmuck straight out of high school? Or did he at least take a FEW college courses before going out in the world to make his mark?

At least, I try to not STAY over your head, though sometimes I accidentally get there for a second. I learned long ago that you lose the chance to meet some great people if you can't learn to bring yourself out of the clouds every now and then.

FYI, when a network dork does that "talking over your head" trick to you, check your bullsh*t detector. Because he is trying to get rid of you or avoid work in some other way.

 

[SaraMegan]

Thanks, Pat. I'll definitely look into those.

 

[Travis]

Sara,

I just got through reading these posts, I did notice one thing that did not get mentioned that you should also bring up...

"ISO9000" or "International Organization of Standarizations"

You will need to go over the requirements set here and meet them.