Another way to deal with attachments (1 Viewer)

[psyc0tic1]

Attachment fields are best avoided as they cause databases to significantly increase in size, reducing performance. Eventually you may hit the 2GB limit.

A much better approach is to store the files externally. You then add a text field with the file path of each file. If you google or do a forum search, Yu should find working examples

ridders... I have been searching and reading for several days now and am a little confused about storing files externally.

From what I am seeing you have to manually put files somewhere and then manually put a link in the database to them. Am I correct in this?

Is it not possible to have the same function as the attachment field with the upload dialog that will allow you to choose the file to upload... it create a folder on a shared drive using the record ID as the folder name (in this case it would be "AuditID" the reason for this is to avoid duplicate filenames causing issues), then upload that chosen file to that folder and create the link to it that can be used in reports?

I have looked at a bunch of "linking" examples but none seem to have this ability.

 

[isladogs]

From what I am seeing you have to manually put files somewhere and then manually put a link in the database to them. Am I correct in this?

Correct .... though not necessarily manually

There are many examples of this online including several at this forum
I've posted the attached example before but can't remember where.
It includes more than one method of displaying images in Access where the file path has been stored ... in this case, you browse to a folder containing images and the paths for all files in the entire folder are imported.
In your case, you would want to save the paths permanently but that's not the purpose of this example

The attachment includes a PDF explaining the approaches as well as an example.

I did have a client who wanted me to convert a database with about 700 attachments into file paths as his database was heading for 2GB. I did find a utility online to convert attachment field contents to file paths but the contract fizzled out & I no longer have a link for this

HTH

 

[psyc0tic1]

That is some good stuff there.

However... in my case as you say... I have many people updating records in the database and fortunately there is rarely attachments that need to be added. See... all of these people are in different states and locations and they take pictures of issues found with parts they are inspecting or testing so I need them to be able to simply attach them to the record.

How about this?

Is it possible to make the back end somewhat an archive-able situation as in multiple back ends by year thus avoiding reaching the 2 gig limit?

Example...
one back end to store all of 2015 data
one back end to store all of 2016 data
one back end to store all of 2017 data
one back end to store all of 2018 data

but still keep the AuditID field ongoing rather than starting over or duplicating.

I can easily create the back ends and move the relevant data to the corresponding years but no clue if this will work or how to accomplish the relations.

 

[isladogs]

Yes you can use multiple backends but you're making lots of work for yourself as you would need to add new table links, queries, reports & module code for each year.
Many years ago, I tried it - BAD IDEA

Strongly recommend you just bite the bullet now & replace your attachment fields with stored file paths. The longer you leave it, the more work it will be to change ... and if you leave it too long you will reach 2GB limit

And remember you can't upsize the BE to SQL Server Express (10GB file size limit) as it can't handle attachment fields

 

[psyc0tic1]

Fair enough... thank you very much for the insight.