Results 1 to 5 of 5
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    Linked DB's, size

    I have a general question regarding linked databases. Since I have not used this feature before, I would like to know:




    1. If each DB has assize limit of 2 GB, and if each approaches 2GB, will the linked DB’s (via linked corresponding tables) still function correctly, without crashing?
    2. Assuming that the answer is yes to the first question, is there a limit to how many such DB’s can be linked, and is each subsequent destination Table renamed in sequence (i.e. Table1 becomes Table11, Table12, Table 13, etc.)?

    The reason for my question is due to the fact that since we import images into the Tables (I know that we should keep images in tables outside of Access) our DB tends to reach the 2 GB limit over time.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Each database can be a maximum of 2GB though in practice its better to keep each no more than 1GB or so.
    You can have as many linked databases as you like - the most I have in any production database is 7 (both Access & SQL Server).
    However bear in mind you cannot create relationships and set referential integrity between tables in different databases,

    You definitely shouldn't store images in Access due to file bloating. Instead store the path to the image files.
    If you do that, its unlikely you will ever need to worry about file size.

    Table names will be whatever you call them but you should use meaningful names e.g. tblClients not Table1.
    Access will only modify the names with e.g. a trailing 1 if you have duplicate table names
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @ isladogs

    Thank you for your advice. We are in the process of moving the images off the Access DB. The main reason for linking the databases is to keep access to historical data. There will be no need to create any relationships between the various historical data (Linked DB's)

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    when you get close to 2 gig you may get 'false' errors, like:
    no such function as: LEFT(

    we all know Left,mid, right ARE functions, but Access will get 'dementia' and vb wont work.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is a matter for debate but I have a number of BE's where the file size is 1.7/1.8gb without issues. But that is pure data, not attachments

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

Similar Threads

  1. Replies: 12
    Last Post: 10-21-2018, 05:49 PM
  2. Replies: 3
    Last Post: 04-20-2017, 05:34 AM
  3. Access table linked to excel - field size problem
    By tzmyo in forum Import/Export Data
    Replies: 1
    Last Post: 10-02-2013, 01:03 AM
  4. Replies: 2
    Last Post: 03-20-2013, 04:25 PM
  5. Linked OLE Objects bloating size of database
    By Nosaj08 in forum Database Design
    Replies: 18
    Last Post: 07-09-2009, 05:54 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