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
36 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 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41872738
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
ID: 41872916
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 35

Expert Comment

by:PatHartman
ID: 41872992
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 13

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 250 total points
ID: 41873012
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 35

Expert Comment

by:PatHartman
ID: 41873023
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
ID: 41873034
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
ID: 41873039
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
 
LVL 35

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41874297
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
ID: 41874531
Firebird is totally free.....no restrictions what so ever..
0
 

Author Closing Comment

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

Expert Comment

by:PatHartman
ID: 41874699
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
ID: 41874709
"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 35

Expert Comment

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

Expert Comment

by:John Tsioumpris
ID: 41874734
Thats fine by me :)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

813 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

11 Experts available now in Live!

Get 1:1 Help Now