Attachment removal (1 Viewer)

[Denise2020]

We have a database with two tables that currently have an attachments field. My boss adores it and feels it is super easy and convenient, but I realize now that it is a problem I never should have created and that size will someday become a problem (The table after 3 years of use is 260,000 kb right now).

I am unsure how to go about fixing this issue. The consensus seems to be to use a text field with a file path instead. So my thinking is to create the new field in the table and then go through and manually enter the file path names and then delete the attachments from the attachment field after. Is this the right way of thinking? And I read that hyperlinks are "bad" and unmaintainable, but I don't understand how a file path is any different than a hyperlink except for one you copy and paste into explorer and the other you ctrl+click to open automatically? Or am I missing something?

Am I going about this the right way or is there another better/easier/more sustainable in the future idea I haven't considered?

Thank you as always.

 

[monheimx9]

If I recall correctly, the issue with Hyperlinks fields is that they work with Access Databases and that's it
You won't be able to make them work if you're planning to move on a SQL Server backend

So if you're gonna work with text fields and a path you might have a bit more work to do behind the scene with VBA
But it will be scalable and a bit more maintainable

So my thinking is to create the new field in the table and then go through and manually enter the file path names and then delete the attachments from the attachment field after. Is this the right way of thinking?

I would have done it the same way, I don't know about the others though

 

[gemma-the-husky]

We have a database with two tables that currently have an attachments field. My boss adores it and feels it is super easy and convenient, but I realize now that it is a problem I never should have created and that size will someday become a problem (The table after 3 years of use is 260,000 kb right now).

I am unsure how to go about fixing this issue. The consensus seems to be to use a text field with a file path instead. So my thinking is to create the new field in the table and then go through and manually enter the file path names and then delete the attachments from the attachment field after. Is this the right way of thinking? And I read that hyperlinks are "bad" and unmaintainable, but I don't understand how a file path is any different than a hyperlink except for one you copy and paste into explorer and the other you ctrl+click to open automatically? Or am I missing something?

Am I going about this the right way or is there another better/easier/more sustainable in the future idea I haven't considered?

Thank you as always.

It's a pain if you have to do it manually. Can you get the attachment path from the existing attachment? If not, could you just save the attachment in a new folder, and then update the pathname from the file you just created?

260Mb after 3 years implies you have a lot of time to resolve this, given your dbs can grow to 2Gb. 260Mb isn't that big, especially if a lot of it is the result of this "bloating".

Work on a copy, and see what you get the database size down to.
You open the hyperlink with application.hyperlink filename, and as long as your PC knows the correct file association it will work. A .xls file will open with Excel, etc. I expect you are familiar with that anyway.

 

[jdraw]

Denise2020,

You may get some insight from this thread