none
Managing database with a lot of attachments RRS feed

  • Question

  • Hey everyone,

    so I am building a Access 2013 database that will be used in sales. For every new buyer there are two attachments (two pictures, total about 1.5MB) that are needed to be inserted and shown in a form when they open buyer info!!! this is really important!! There are already 500 buyers for me to put in the database (so that is about 750MB database already). This would mean that it is very large database and it will soon reach 2GB. Also since Office 365 is used this database would be accesed on two or more computers or a smartphone. If i would change something in a single 700 or 2GB database, uploading this change to Onedrive for bussiness would be too long and would not be fast and affective, actually worse. So my question is, can you give me a solution for this? should i put attachements in multiple databases, or use hyperlinks to a folder where i would put pictures, but that is also not effective? My only problem with hyperlinks is that it doesnt opet a select window like adding attachements (there needs to be easy way like that to add pictures) and it doesnt show a picture in the form. Maybe i am doing something wrong. If anyone could help me with managing this i would be very thankful.

    Friday, July 10, 2015 9:03 PM

Answers

  • Thank you for the reply but with paths there is something else that is worrying me. Example if I add path and store files in a map C:\Users\Mark\Onedrive\... and I add this exact path to the database. When I go to another computer where the user is John the link wont work right? His link to Onedrive would be C:\Users\John\Onedrive\. And the database wont be able to locate it.

    Do you have any more solutions because this database has to work on multiple computers over Onedrive where it will be stored?


    In that case, you store only the relative path to the item: onedrive\attachmentfiles\.  In another location, in a table or code, you set up rules for user access to that path.
    Saturday, July 11, 2015 12:34 PM

All replies

  • Hey everyone,

    so I am building a Access 2013 database that will be used in sales. For every new buyer there are two attachments (two pictures, total about 1.5MB) that are needed to be inserted and shown in a form when they open buyer info!!! this is really important!! There are already 500 buyers for me to put in the database (so that is about 750MB database already). This would mean that it is very large database and it will soon reach 2GB. Also since Office 365 is used this database would be accesed on two or more computers or a smartphone. If i would change something in a single 700 or 2GB database, uploading this change to Onedrive for bussiness would be too long and would not be fast and affective, actually worse. So my question is, can you give me a solution for this? should i put attachements in multiple databases, or use hyperlinks to a folder where i would put pictures, but that is also not effective? My only problem with hyperlinks is that it doesnt opet a select window like adding attachements (there needs to be easy way like that to add pictures) and it doesnt show a picture in the form. Maybe i am doing something wrong. If anyone could help me with managing this i would be very thankful.


    You should store the path and file name in your database only.  If you are going to have multiple attachments, then you probably want a separate table "Attachments" to store as many path and file name records for the item in question as necessary.  You can then wright code to open your attachment in whatever application is suitable.
    Saturday, July 11, 2015 12:08 AM
  • Thank you for the reply but with paths there is something else that is worrying me. Example if I add path and store files in a map C:\Users\Mark\Onedrive\... and I add this exact path to the database. When I go to another computer where the user is John the link wont work right? His link to Onedrive would be C:\Users\John\Onedrive\. And the database wont be able to locate it.

    Do you have any more solutions because this database has to work on multiple computers over Onedrive where it will be stored?

    Saturday, July 11, 2015 8:15 AM
  • Thank you for the reply but with paths there is something else that is worrying me. Example if I add path and store files in a map C:\Users\Mark\Onedrive\... and I add this exact path to the database. When I go to another computer where the user is John the link wont work right? His link to Onedrive would be C:\Users\John\Onedrive\. And the database wont be able to locate it.

    Do you have any more solutions because this database has to work on multiple computers over Onedrive where it will be stored?


    In that case, you store only the relative path to the item: onedrive\attachmentfiles\.  In another location, in a table or code, you set up rules for user access to that path.
    Saturday, July 11, 2015 12:34 PM
  • Hi,

    Pardon me for jumping in... You mentioned that your database is in O365 for users to access it in smart phones. I just wanted to clarify, does that mean you're talking about a "web app" database and not a "desktop" database? Because if it's a web app, then the limit is more than 2GB.

    Saturday, July 11, 2015 4:05 PM
  • I think it's time for you to upgrade to SQL Server Express.  The free edition is limited only by 10GB.  It's $50 (I think) to upgrade to a version that doesn't have any size limit (practically speaking).

    https://www.microsoft.com/en-us/download/details.aspx?id=42299


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, July 11, 2015 6:54 PM