• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

MS Access How To Set Up Keeping Part Sheets, Owners Manuals, PDF Forms and Other Documents In Access Organized

I was wondering ways to setup keeping track of documents like Jpg Pdf and so on in Access. These would mainly be for Repair Manuals, Parts Manuals, and General Documentation about a specific SkuID (SkuID Found in my SKUs Table).  The files will be stored in a folder just below the Database Backend.

The user would be using these files and searching for these files to use pretty frequently.

How would I set up the table and forms for this?

Thank you for suggestions and ideas!

db3.jpg
0
Dustin Stanley
Asked:
Dustin Stanley
  • 6
  • 5
  • 3
3 Solutions
 
PatHartmanCommented:
I separate the path from the file name in these logs because it makes the update a little simpler if you have to change your folder structure.  In some applications, I don't store the path in the individual records at all because the path is dictated by something else such as department, customer, Type, etc.  In those cases, I store the path at a higher level.  You might also consider grouping the documents by type.  For example, I have a drawing log application that keeps track of drawings related to a project.  There are a dozen different types of drawings. and the client prefers to keep the various types of drawings  in different folders.  So the architectural drawings are in a separate folder from the HVAC or the landscape drawings.  You could also add a field that identifies the file extension such as pdf, doc, xls, etc if the client wants to search on such things.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I will for sure seperate the file names from the path so there won't be any problems on a multi computer setup.

Ok I am going to give you an example Pat and you tell me what you think of my questions from that example.

Lets say I have a Dell Laptop Model A1B2 (SkuID 3303)

Documents needed for this could be like User Manuals, Service Manuals, and Parts Diagrams.

You say group them. Would I have a folder for only Service Manuals and then put all the SkuIDs (SkuID 3303,4456,7853,etc...) Service Manuals in it?
(Maybe a subform for this to show matching files for that specific SkuID)

And then the same for User Manuals and Parts Diagrams and so on?

You say store it at a higher level? Im a little confused. I planned on placing the folders diectly under the database file. So the database would be in a location and next to it would be the folder.

Also what would the table setup look like?

Of course if I was linking by SkuID then I would have to have a SkuID FK in the table but what other fields and what other links might I want?

Thanks for the help.
0
 
PatHartmanCommented:
By "store at a higher level", I meant - store the path at the level which controlled it.  So for example, If I wanted to separate files by department, I would store the path in the department record.

How granular you should get is largely dictated by how structured the existing file names are and the number of files that will be in any folder.  You want to make it as easy as possible for the users to get the file into the correct folder.  In one app, I programatically download completed files from an FTP folder.  The file name includes the ClientID.  I use that to look up the client record and find the folder name for that client's files.  Users don't want folders named 1234.  They want to see "My Special Robots" as the file name.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
John TsioumprisSoftware & Systems EngineerCommented:
Personally recently i began "investigating" the use of more robust file-based database engines like FireBird
The trick at least to my understanding is to plan carefully...like i have a catalogue with hundrends of images ...instead of simply storing the Image...i store the image which can be any size and also i store a thumbnail ....So the user gets the thumbnails and if he/she wants more detail it clicks and get the full image....
Image around 160x120 are around 2-3 kb ....are visually recognizable and my Firebird server (VM) serves them is seconds...and if it wasn't my application in Access 2003 probably i could avoid some overhead due to image manipulations....
Similar goes for PDFs...is something i want to do for a forthcoming project...store the cover as a thumbnail...some text as summary....and maybe some tags...
All in a single location with central management and a single file easily stored and transferred...
0
 
PatHartmanCommented:
That's a good idea for images John but I think Dustin is dealing with lots of documents, not images so having thumbnails wouldn't help him
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Thats why @PatHartman  i mentiond PDFs...covers...summarys..tags and so on....
Firebird doesn't care what you are storing...its up to "you" to organize table so that it can present the user the least needed information to access the needed information...
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok lets keep it simple for now but NOT to simple. Something that can grow with me. My main foucus for now is the Tech department (Repair, Diassemble, and Testing).

So I would store these files in a folder that only the Tech Room can access, Correct?

How granular you should get is largely dictated by how structured the existing file names are and the number of files that will be in any folder.

Lets just say we are starting all over from scratch. So there are no files yet. So think of it as a new canvas and tell me your best thoughts.

The Table:
Besides FileName,DateAdd, DateEdit,FileExtension and EmployeeID what other fields would you suggest?
What kind of relationships would I need?
Would this only be a single table situation or would I need multiple tables?

The Files:
How would you name them for simplicity to find?

Tags??? I know about them but never used or looked into them. Would this be a good thing for me?
I don't really plan on the end user to generate these files and such and name them and file them away. Just be able to say "Here is this product and here are the documents that go with it." so they can service, test or disassemble the product.

Filing and naming away should probably be left up to a higher or specific employee or manager.




That's a good idea for images
That is very true. I am for this specific question mainly using documents but I have a application that may just benefit from Johns answer.

PDFs do have covers and I use them in my own PC when looking for a specific one. Very helpful.
0
 
PatHartmanCommented:
So I would store these files in a folder that only the Tech Room can access, Correct?

Open in new window

That depends on your business rules.  I'm not sure why everyone couldn't see the manuals.
Besides FileName,DateAdd, DateEdit,FileExtension and EmployeeID what other fields would you suggest?

Open in new window

You need path unless you are storing it some place else.
What kind of relationships would I need?
The manual list will be a child table so it needs a relationship with its parent.
Would this only be a single table situation or would I need multiple tables?
I would use only a single table perhaps with a type code if that makes sense.
Tags??? I know about them but never used or looked into them. Would this be a good thing for me?
For what?  do you need to search for a manual by something other than the object the manual is about?

John's suggestion goes well beyond the scope of this question since it involves purchasing a product and all that entails.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Firebird is totally free.....no restrictions what so ever..
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you guys. I will see what I can do with your the information you gave. Again thanks for your time!
0
 
PatHartmanCommented:
I would be quite cautious of entrusting my production data to a "free" application.  Nothing is actually free in this world despite what the left would have you believe.  Someone is monetizing this application.  Volunteers may provide support on an "as they have time" basis much in the way experts answer questions here but don't expect to call me personally to solve a production problem just because I answered a question for you.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
"Free" applications probably are right now the backbone of our digital world ...MySQL, Apache, PHP.....to mention a few....
Nothing in this world is totally free because usually what you save from buying a product it is wasted on work hours...but....when something is free ...is free
0
 
PatHartmanCommented:
I think we're going to have to agree to disagree on this point.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Thats fine by me :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now