?
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
Medium Priority
?
56 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 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 38

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 17

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 1000 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 38

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 17

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 38

Accepted Solution

by:
PatHartman earned 1000 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 17

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 38

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 17

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 38

Expert Comment

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

Expert Comment

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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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