Results 1 to 6 of 6
  1. #1
    Cratebug is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    11

    Link split database backend to another?

    Hello all. Just joined the forum. David M. from Chicago. Hope everyone that reads this is in the best of health and spirit.

    I am not sure if this is the right place to post this question but...

    Is it possible to link the back-end of one split database to the back-end of another split database?

    The issue I'm having is slow performance from the database when working remotely or at home as opposed to in-office.



    I am working with Access 2010 version and the .mdb split db files are located on a company network drive. The database back-end has one main table. It has approximately 185 fields and about 2700 records total. The front end is linked to the back-end and has 15 forms. Each form has at least 20 to 35 bound control fields.....with a good number of them being calculated control fields. Not to mention all of the unbound control fields. Each form has it's own query as a data source and they all have macros and vba coding running in the background. The front end file size itself is no bigger than 13 megs. I am not sure how many controls each form specifically has but I know its significant and suspect its the reason why the db takes so long to load.

    In office, the database works okay....meaning, it's not slow. But working remotely, it is a nightmare. It takes between 2 to 3 minutes to fire up. Once in, opening a form takes anywhere between 30 seconds to 2 minutes.

    What I have tried already is creating another smaller regular db (non-split), with just one query, one small form with perhaps 5 fields at most.....and I have it linked up to the main back-end db table of the current db. I tested it out while working remotely from home and although it is just one small form with a smaller query as it's data source, it still takes long to load. I am only guessing that aside of the fact that I am working remotely, load time is slow also because I am still indirectly linked to that larger, slower front-end via the back-end of the main split db.

    The person I report to suggested creating another smaller database, split it so that it has its own front and back end.....then link the smaller back end to the first back end. So again, my question is....


    Is it possible to even do that? (I haven't tried yet)

    If so, will something like that even work?



    My team has approximately 7 users and they all work off of an .mde copy of the db front-end. 5 of the users work remotely and they have grown grey hair waiting on this database to load and there is no end to their frustration and complaints.

    Any and all suggestions are welcome and appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This sounds like you could have a design problem (independent of the slowness)
    The database back-end has one main table. It has approximately 185 field
    You can link your front end to multiple backends.

    Depending on what you are doing with the data (read Only), you could import the data to a local table.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    When you are working from home or remotely, where is the front end located - on the PC you have with you, or on a remote location? (and where is MS Access itself located?) I suspect your issue is due to a slow connection speed somewhere; remember that MS Access does all its processing locally, which means that running queries can result in a lot of data having to go from the back end to the front end. You can sometimes improve speed by putting more indexes on the back end tables, but there is no guarantee of that.

    When connecting to a backend database, you connect to the data file directly; don't go through an intermediate other front end (I don't know if that would even work). Connecting to a back end data file will not be affected by having another (different) front end connected to the same file.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My thoughts.........

    The issue I'm having is slow performance from the database when working remotely or at home as opposed to in-office.
    So it sounds like you are using Access across a WAN. Not a real good idea. See this article by Albert D. Kallal. Even though it was written (posted) [SIZE=2]09 Aug, 2003, it is still mostly valid. http://www.kallal.ca/Wan/Wans.html
    (Edit: forgot to add link )

    Since SQL Server Express is free and the max size is 10 Gb (Access is only 2 GB), I would try that first. (but I don't think SQLSE supports calculated fields)


    185 fields in a table seems excessive to me. Might look into normalization. I would also question the calculated fields. They will slow down the dB. (Don't know how much because I never use them.)


    Another good article by Albert is: http://www.kallal.ca/Articles/split/index.htm



    And welcome the the forum.

  5. #5
    Cratebug is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    11
    Thanks for the responses guys....researching.....

  6. #6
    Cratebug is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    11
    Update:

    Definitely a design issue. Have condensed the larger split db to less than half its original size.....alot of unused objects. Smaller db is linked and definitely works faster. Thanks for all the input fellas!!

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

Similar Threads

  1. Replies: 4
    Last Post: 11-23-2015, 06:21 PM
  2. Link to new table in split database
    By tazzzz in forum Import/Export Data
    Replies: 3
    Last Post: 11-11-2015, 03:20 PM
  3. Multi Users on backend of split database
    By Tammy in forum Database Design
    Replies: 10
    Last Post: 12-16-2014, 01:56 PM
  4. Problems accessing split database backend
    By DG2012 in forum Access
    Replies: 7
    Last Post: 11-20-2014, 06:22 PM
  5. Link Manager access for split database
    By geraldk in forum Access
    Replies: 1
    Last Post: 08-21-2012, 07:51 AM

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