Results 1 to 9 of 9
  1. #1
    Access4444 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13

    SharePoint Table vs. Back End Split Database

    Hi,



    I have a split database that has approx. 12-15 users. The main table in the back-end is almost 1.8GB and I know access has a 2GB limit. One option I have thought of is creating 2-3 more back-ends to get my max storage up to 6-8GB and then union the tables and funnel them back into the front-end, the users would use the tool the same way, but I wanted to know if publishing the main table (1.8GB) to SharePoint would allow me to circumvent the 2GB max?

    thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no,you cant circumvent the limit.
    compress the db.
    spit your big tables to other databases, but link them all into 1 central backend.

    this should keep the size down.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're not storing images in these tables by any chance?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Access4444 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13
    the records are holding attachments and they could be holding many per record. standard pdf, word, etc. documents are attached to each record.

    To ranman - so my initial approach should work? or are you recommending something similar but different?

    OR...should I convert it to an ACCDE format?

    thanks!

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So that's the reason for the atypical size you have. It's why in Access, links are usually stored in a table rather than the objects themselves. A sql server database is more appropriate for what you have but I'm guessing that's not an option for you. In that case, I'd agree that splitting as suggested will become your only option - aside from using links to documents instead.

  6. #6
    CQCDave is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2014
    Posts
    47
    I ran into that problem with the attachments. I created a folder of my network and stored at pdf's and hyperlinked the files to the record as needed.

  7. #7
    Access4444 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13
    Yes, I agree that's why the database has grow and has become an issue. The thought behind the attachments in the database was the one stop shop and centralized place for all things associated with the particular process/tracking, but a hyperlink to the document could also achieve a similar feeling for the end users.

    what is the best way to accommodate hyperlinks and multiple ones since each record usually carries 3-5 attachments.

    thanks!

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would set up a table of attachment references (tblAttachRefs) as long as you can associate a parent record key (something that is common with the records that have multiple attachments, such as Order_ID) as the FK in the attachment table. I would suggest the field for the attachment location should be text (as opposed to hyperlink) if you're going to use vba to work with the links.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    I agree with the others that using hyperlinks would be an easy and viable choice. I'm just saying for the record that if you did want to keep the files attached (again I don't think you do) you could migrate it to SQL Server Express which is free from Microsoft and you can actually install it on a normal machine (doesn't have to be a Windows Server machine) pretty easily. There's plenty of books and online material where this is described and it's quite easy.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 01-09-2015, 12:15 PM
  2. Split Database Back End password
    By eww in forum Access
    Replies: 4
    Last Post: 05-30-2014, 11:09 AM
  3. Replies: 27
    Last Post: 05-01-2014, 12:21 PM
  4. Replies: 1
    Last Post: 04-10-2014, 12:17 PM
  5. Moving split database to sharepoint
    By Cindy morgan in forum SharePoint
    Replies: 0
    Last Post: 09-17-2013, 10:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums