Solved

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

Posted on 2016-11-03
14
26 Views
Last Modified: 2016-11-04
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
Comment
Question by:Dustin Stanley
  • 6
  • 5
  • 3
14 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 13

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 250 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
Firebird is totally free.....no restrictions what so ever..
0
 

Author Closing Comment

by:Dustin Stanley
Comment Utility
Thank you guys. I will see what I can do with your the information you gave. Again thanks for your time!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
"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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I think we're going to have to agree to disagree on this point.
0
 
LVL 13

Expert Comment

by:John Tsioumpris
Comment Utility
Thats fine by me :)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now