Reaching Data Limit, Multiple Back Ends? Good Idea or Not? (1 Viewer)

Hey,

Have a little predicament, I have created a database which works great, But after a little under a month of use its now at 1.7 GB

We store a lot of images in our database because it logs build issues and people attach images of problems / solutions / drawings to records.

There are 10 tables in total, 3 of which will be attachment heavy.

I was thinking do I split it into multiple backends, or is there another way? I am aware that if I do split into multiple backends then I will need to code the RI, and I have no clue how to do this.

I also can't move it to SQL due to company rules.

Thanks for any advice you may have.

Cat

Put the attachments in a structured folder?

I'm not sure how that would work.

Is it possible to have the database save them into this other folder so that it behaves the same way or would you need to link to it like a hyperlink?

We need all attachments to be with the record not in a folder outside of the database due to network issues we frequently have across sites

The attachments would be stored in a folder(s) and either hyperlinked or just simply the path to the attachment.?

However if you say 'We need all attachments to be with the record not in a folder outside of the database, then this is not an option.

Hi. Just thinking out loud but perhaps one thing you could try is have a separate back end for each attachment field or table.

For myself, I'd go with Gasman's solution. In your database you save the full path and filename for each attachment. If you are hitting close to 2gb/month, I'd suggest making the path include the YYMM so you have multiple subdirs to hold each month's attachments. This should also ease your backups unless attachments get edited often.

If you decide you really really really need to keep these in tables then you may want to use the same kind of logic but replace the subdirs with subordinate back ends. You would divide them up by parent ID number (1 back end for every group of N parent records based on your experience) so all records for a range of parents are stored together. Much more difficult to do but keeps all child records together.

I agree. Using attachment fields will be a permanent problem in terms of file bloat. You can certainly use multiple BEs but that's a sticking plaster rather than a cure.
Storing the file path instead will result in a leaner, faster app with no worries about hitting size limits.it also will allow you to upsize to SQL Server in the future if you wish (not possible with attachment fields).
You'd be better off solving any network issues than sticking with your current approach.

Thanks for the advice guys. I will look into how we can do this.

Do I need to start another thread in forms to talk about best way to do this or just continue this thread?

Suggest a separate thread in the Forms forum with a link back to this thread

We need all attachments to be with the record not in a folder outside of the database due to network issues we frequently have across sites

Click to expand...

Can you elaborate on what "across sites" means?
Are you using an access backend over a non-hardwired network?

Something important for you to do PRIOR to starting your thread; since you've started hitting issues already you will want to identify how much space per parent record you will need for "Attachments". This will help drive your decision making on HOW you want to structure your directories/subdirectories for attachments. You may find it much easier to set up a "By date" structure that allows you to segregate attachments based on the date they are entered OR create a directories/subs based on the Parent's ID. This also leads into how they will be backed up/how often. With a couple GB of data in a month, you will also want to project out how to store for the future / how easily you want to access older attachments.

If you are already at 1.7 G after a month, you are in deep do-do and splitting the BE into multiples isn't going to help for very long. That leaves you two choices.
1. rather than embedding the picture in a table, just store the path and use the path to open the picture when you need to.
2. Convert the BE to SQL Server which is more efficient at storing objects. You will still have a huge table but you may be OK if the growth curve levels off.

All-in-all, I think option #1 is what will work best.

PS - if you choose #2 be aware that the Attachment data type is "Access" only. SQL Server does not support this data type so you will have conversion issues and have to modify your forms as well.

I'm with Pat on this. She has worked with big databases before and is definitely a "voice of wisdom" for this class of problem.

Here is where you run into, well, ... crap. At 1.7 GB/month, you will run into the Access file size limit every month. BUT you will run into a program limitation of another kind. You can only have either 16 or 32 DB files open at once. I think the number depends on version, and I don't recall when it changed.

You can keep a year's worth of BE files open, but more than that, you would have to open and close the specific files you need. Dynamic file juggling is a good way to confuse yourself and inadvertently cause DB corruption. This will be a dead solution before 3 years has elapsed.

Here is where you sit: You have painted yourself into a corner - or your company has. This next part is probably going to sound a little brusque but it is not meant to be insulting. I am just going to tell it like I see it.

You claim that an SQL engine will not be allowed by company rules. That, to be blunt about it, is baloney. If the company wants something that Access can't manage because of raw data volume then either they didn't really want it or they will see about just how firm that rule really is. If you tell them that long-term, you need an SQL engine, you will get an SQL engine.

However, there is the issue of image size, which if you remove it from the DB, would alleviate the size requirements considerably. You claim that you don't want the files in a folder outside of the DB's BE file. But again, if your choice is to make the file external or to not be able to do this at all, how long do you think that particular barrier will last?

Remember, it is TRIVIAL to create a folder for images that would reside under BE file's folder, just to keep them separate from the BE's folder. The hyperlink will be unlikely to take up more than 250 bytes. Probably less. If you are worried about the security of the files, they will have the same security as the BE file itself if you want, using permission inheritance.

You can't worry about the size of the images either. If they would have fit on the disk while embedded in the BE file, they will fit when NOT embedded in the BE file.

Hey,

Thanks for the advice guys, not harsh at all. I totally agree with you on all of the above.

SQL server is 100% out. If it cant work without SQL then its getting binned. Ruling was from company overlords who dont actually understand what or why we are doing this.

I'm going to start another thread in forms in regards to getting the attachments to save somewhere else on the server which we can then hyperlink to. As long as it's as easy to use as an attachment box then the people who are using it will be happy.

Link to new post is here https://www.access-programmers.co.uk/forums/showthread.php?p=1653908#post1653908

If anyone is willing to help me on the other thread would be appreciated

I'm with everyone else I would never put image files into Access - although its an option I have always ran out of space incredibly quickly doing this.

PS - if you want a cheap option for a SQL server Postgres is excellent - open source and likely to be able to cope with anything. I suspect that mgmt are just anti any kind of development so you probably won't be allowed that either.

MS Access 2003 connects to postgres no problem so I suspect whatever version you have will also.
https://www.postgresql.org/

Postgres is free

it really is trivial to do this by storing the images in folders, and you never really have to inspect the folders by hand, although you can if you need to.

simply store the filename and application.followhyperlink filename will open it with the registered programme for the filetype.

I know this is an old thread, but I have the same issue with reaching capacity for the back end. (Please tell me if your preference is that I create a new thread as some forums frown on bringing up same issues that have been discussed before.) The files being stored in mine are Contract pdfs and some .doc files. This is fine for our division, but the parent org is looking fondly at my DB to incorporate for use for the whole org. In that case, I will run out of storage space. Here is where it is now.



Creating a hyperlink to the shared drive would be an option for us for files. My question is how would I set up the field in the table to be able to handle multiple file links? Currently, I'm using an attachment field, and I can add multiple documents in there. If I use a hyperlink field, that is only one link. I'm thinking my only option here is another table. I think I just answered my own question...

mistyinca1970 said:

I know this is an old thread, but I have the same issue with reaching capacity for the back end. (Please tell me if your preference is that I create a new thread as some forums frown on bringing up same issues that have been discussed before.) The files being stored in mine are Contract pdfs and some .doc files. This is fine for our division, but the parent org is looking fondly at my DB to incorporate for use for the whole org. In that case, I will run out of storage space. Here is where it is now.

View attachment 91664

Creating a hyperlink to the shared drive would be an option for us for files. My question is how would I set up the field in the table to be able to handle multiple file links? Currently, I'm using an attachment field, and I can add multiple documents in there. If I use a hyperlink field, that is only one link. I'm thinking my only option here is another table. I think I just answered my own question...

Click to expand...

Hi. I think it would better to start a new thread; and if you like, you can put a link to this one.

FWIW, I would recommend storing the files to a network folder and use a child table, if you need to have multiple files associated for each main/parent record.

I agree with dbGuy. So essentially the answer to your situation has little to do with pictures or paths (although it's good that you're going in that direction, definitely preferable); rather, it's a classic table structure type question and you definitely should set it up so that the primary record is in one table, and the paths to the pictures which belong to it are represented by multiple records in a child table.

It might be tempting to go down some alternative path, like storing path1;path2;path3 in a column or something, but that will just generate problem after problem after problem in the future. Doing it the right way scales smoothly and infinitely.

Currently your database is almost 300MB. The limit is 2GB (2000MB approx) so you have a long way to go yet.

However, it will rapidly grow as my understanding is that you are currently saving files as attachment fields. That is BAD news
As already stated, files should be stored externally to the database. All your database should contain are the paths to your external files