Db for MSDS sheets and safety stuff

[GBalcom]

I'm considering building a db for our company to host all of our MSDS Sheets. This may be upwards of 1,000 different sheets, most likely PDF's

My question is what is the best way to handle this many PDFs? Here are some of my options, and I'd like to hear some pros and cons about the different methods:

1. Use Access as a FE, with an SQL backend and embed the PDF's
2. Use Access solely, and the new attachment field
3. use access and hyperlinks to the pdfs, stored elsewhere
4. create a web db through access, and host it through a sharepoint service

Although I'd like to keep this inhouse, I'm really weighing the cost/benefits of doing this. I know there are also services for this kind of thing I could subscribe to.

Thanks for any insight,
Gary

 

[Catalina]

May be another question to ask yourself is how many (concurrent) users there will be and from where they have to access the db.

I have an Access db that contains links to over 14,000 images, and it works great.

The new attachment field is easy to use but still causes bloat. I understand it would also be a pain to upgrade to SQL Server later if you decided to do so.

Catalina

 

[GBalcom]

Catalina,
Thanks for the quick response. I don't believe we need to access this information outside of the building, (though It will be backed up offsite if ever REALLY needed). So, I'm ok with a local client server solution.

Wow, 14,000 linked images...that's a lot more than I'll have here....have you come up with a way to automate the the process of creating them?

 

[Catalina]

I don't have automation in linking them. Giving the nature of the database and the link naming it has to be a manual process.

It is a db I started in dBase III in the mid 1980's and moved it to Access in 2001, I believe. It is not growing very fast, so some manual adding is no problem to me.

Catalina

 

[Lightwave]

Put all the files in one directory

Create a variable and set it to the directory

Have a table with all files in - just a normal field with full filename as value eg file1.txt

Have a button that says on click open file directoryvariable/filename

Easy

You can hold essentially infinite files within a simple desktop database with zero bloat.

Do not include files within databases they generally collapse incredibly quickly.

Additionally if you want to move the files to another database you simply copy and paste the directory with all the files. You don't even need to put the files in the same directory because if you change the directory provided they are all in the same directory you can just change the variable that is the directory path.

Also multiple front ends can have different paths should you not want to unc naming conventions. But each front end will only need to remember one directory to get to all files.

 

[GBalcom]

Put all the files in one directory
Do not include files within databases they generally collapse incredibly quickly.

Thank you Lightwave,
but can you clarify the above statement? Also, what do you think about using the autonumber ID as the hyper-linked file name? I want users to access the PDF's through the db, and this would help ensure that I think.

 

[Lightwave]

Within Access 2007 you had a data type called OLE Object this would allow you to hold jpegs etc actually in the database part of the accdb or mdb.

Problem was that for every file you added it increased the file size massively often by 50mb for a single file. Its horrendous don't know why they implemented it - its effectively useless. Interestingly when you try and put a picture on a form it does something similar (bloats the file). I was putting 1 file in my databases and a single image would cause the file to bloat more than the whole of the rest of the file!

Now I don't know what version you are using but they might let you use something similar I would avoid it it will cause you nothing but problems.

ID's for filenames would work but you don't need it to have the datatype of hyperlink. I prefer text fields rather than hyperlinks because the user can change it and everytime they click on the field they don't automatically get the file (hyperlinks have an annoying habit of triggering when you are trying to edit them. A text field with button next to it which builds the path directory and file once click is hit seems to work best for me. If all the the files are in the same directory if you have files with the same name that will be a problem but that's not something I've come across.

If you have thousands of files renaming them as ID names would be time consuming and probably unnecessary. You are correct though you are going to have to tie the filenames up to their relevant records in the database. Hopefully this could be done semi automatically in excel so you end up with the appropriate table which will consist of the PKID and a column listing the name of the file.

 

[GBalcom]

All of what we have right now is in Paper format. So after an extensive physical inventory, I'm going to have to scan and do all of this manually anyways....unless someone can point me towards a better idea.