Need help understanding how to best store and query PDFs and other yearly training documents

[SkeletorPlus]

Hi.

My company requires a bunch of annual compliance training. I want to integrate it into our account tracking database but I know access can’t handle the files (I’m talking upwards of 800 documents a year). We are thinking about using sharepoint lists (among other reasons) to store it but it’s very clunky and there aren’t many good options to upload it (I kinda hate power apps and the list forms sucks to be honest). I’ve tried making ms access forms to upload but there’s a problem where you have to save the entry before you can attach a file, and if a file is required in sharepoint, it’s impossible to upload through ms access. We want to keep using access as a front end because again power apps sucks but really want the option to actually upload these in access.

Does anyone have any advice on what you would do in this situation? And has anyone had success doing a sharepoint lists based document store?

 

[tvanstiphout]

A quick search on GitHub finds this promising lead: https://github.com/OlgaKuzminichna/excel_templates

 

[theDBguy]

Hi. As I mentioned in your other thread, I just use SharePoint for this with no Access front end, and we're fine with it. When you said 800, is that the total number? For example, we're required around 10 courses but with around 35 staff, that's only about 350 total certs per year.

PS. I'm not using PA, because I don't expect the users to upload their cert PDFs from their phone.

 

[SkeletorPlus]

Hi. As I mentioned in your other thread, I just use SharePoint for this with no Access front end, and we're fine with it. When you said 800, is that the total number? For example, we're required around 10 courses but with around 35 staff, that's only about 350 total certs per year.

PS. I'm not using PA, because I don't expect the users to upload their cert PDFs from their phone.

I think the only real reason we use access is because there is a bunch of non-certification stuff we create data for an track (change request, tickets, etc) and access is the only way we have to aggregate this info, and we are looking to continue this integration.

But the reason I’m am sussing out sharepoint is because I think that sharepoint would be good regardless

 

[theDBguy]

I think the only real reason we use access is because there is a bunch of non-certification stuff we create data for an track (change request, tickets, etc) and access is the only way we have to aggregate this info, and we are looking to continue this integration.

But the reason I’m am sussing out sharepoint is because I think that sharepoint would be good regardless

I can understand that. I use Access for other stuff too. I even used Access as FE to some SP lists before. But for this particular case, using only SP for the staff to submit their certs worked perfectly. The super is happy with it when compared to collecting them via email before.

 

[Mark_]

To help define what your requirement is, can you answer the business rule(s) for certification tracking?
Do the certs need to come directly from the trainer?
Are they self-reported by employees?
Do they need to be verified before being saved?
Do you need to keep physical copies, electronic copies, or both?
Do you need to track who entered them in to your system?

How you would want to develop this for an employee to be able to "upload" a cert directly into the system will be different than if you need the original provided directly by the certifying agency / organization.

A very clear description of how it is done now will help provide some better answers, especially if you can include how they need to be stored and accessed.

 

[Pat Hartman]

I'm confused about what you are uploading. You are calling them "certs". Does this mean that they are documents coming from other organizations? If your company is doing the certification - i.e. training and testing, what document gets uploaded? Why aren't you simply recording the result in the Access database?

If you want to store the documents, whatever they are, in the database, that is easy enough if you use SQL Server as the BE. It doesn't have the same limitations as Jet/ACE and can support large numbers of documents. And, using the FE, can make them accessible via searches.

 

[SkeletorPlus]

I'm confused about what you are uploading. You are calling them "certs". Does this mean that they are documents coming from other organizations? If your company is doing the certification - i.e. training and testing, what document gets uploaded? Why aren't you simply recording the result in the Access database?

If you want to store the documents, whatever they are, in the database, that is easy enough if you use SQL Server as the BE. It doesn't have the same limitations as Jet/ACE and can support large numbers of documents. And, using the FE, can make them accessible via searches.

Sorry, I am being a bit vauge here. We have an LMS, but for compliance reasons, we have a lot of PDFs that are digitally signed that need to be recorded (government type forms, contracts and whatnot). It's really more a form store, and we like SharePoint because we can give view rights on the list to the person that the document was for to let them know what we have on file. The issue is when we get a new person we cannot upload a document in access to start (because they are not on the userinfo table), but when we upload a pdf for them in SharePoint they are added to the userinfo table. Also in access, we don't ever query the docs themselves, just the dates that they have for them on file.

To help define what your requirement is, can you answer the business rule(s) for certification tracking?
Do the certs need to come directly from the trainer?
Are they self-reported by employees?
Do they need to be verified before being saved?
Do you need to keep physical copies, electronic copies, or both?
Do you need to track who entered them in to your system?

How you would want to develop this for an employee to be able to "upload" a cert directly into the system will be different than if you need the original provided directly by the certifying agency / organization.

A very clear description of how it is done now will help provide some better answers, especially if you can include how they need to be stored and accessed.

We only upload documents where we are the last to sign, and they are government forms with PKI style signature. As mentioned above, we use this sharepoint list to display to users what forms we have of theirs on file so they can self service.

All I am looking for is a way for the UserInfo list on my team's sharepoint site to match my global userinfo site without giving permissions to anyone else (including view permissions). Again, the team sharepoint site has a userinfo table that reflects the memebers of the site and anyone we have uploaded a form for so far, but when we onboard a new person, we have to user sharepoint for the first form, and access for everything else.

Access is used because we manage a lot of other data we create, and sharepoint is used to give visibility on these forms enterprise wide.

 

[RogerCooper]

I use Access to store PDF's on a regular basis. I use the attachment data type.

You can use VBA to manipulate the attachments. For example, I automatically generate an e-mail with all the attachments for a specific record.

 

[Pat Hartman]

All I am looking for is a way for the UserInfo list on my team's sharepoint site to match my global userinfo site without giving permissions to anyone else (including view permissions).

I don't know what magic is updating the UserInfo table in SharePoint but Access can link to SharePoint and you can update the linked lists if this is an ordinary SharePoint list and not something hidden. If your users log in to the Access app, you can implement security to limit who gets to add items to the SharePoint list.

PS, the control to use Attachments is convenient but the Attachment data type does not upsize so if you use the Attachment data type and you find that you have to upsize, you have lots of changes to make to the app to make that happen.

 

[Isaac]

Hi.

My company requires a bunch of annual compliance training. I want to integrate it into our account tracking database but I know access can’t handle the files (I’m talking upwards of 800 documents a year). We are thinking about using sharepoint lists (among other reasons) to store it but it’s very clunky and there aren’t many good options to upload it (I kinda hate power apps and the list forms sucks to be honest). I’ve tried making ms access forms to upload but there’s a problem where you have to save the entry before you can attach a file, and if a file is required in sharepoint, it’s impossible to upload through ms access. We want to keep using access as a front end because again power apps sucks but really want the option to actually upload these in access.

Does anyone have any advice on what you would do in this situation? And has anyone had success doing a sharepoint lists based document store?

In my past, when needing to manage documents through an Access app, I would store the documents in a secret place on the network share and then reference the Path to the file in the Access app. I also did a "copy to local" method when viewing the file so that no file could ever be edited or locked exclusively.

Generally speaking, I would warn against using actual attachments in Access. Store them on your network and just reference the path -that is all you need.