MSAccess Front End Link to Back End Need Faster Process

Posted on 2011-10-06
Last Modified: 2012-05-12
I  am building a MSAccess db where the backend data tables will sit on a network drive, and the front end will get distributed out to multiple users.  To link the two I run a process at startup that loops through all the tables on the backend, linking the ones the are on the list of tables that I need linked.  But it runs too long in testing on a user's machine.  It is looping through all the hidden files, as well as the data tables.  Is there a faster way to do this?
Function IsLinked(mytable As String) As Boolean
    Dim obj1 As Access.AccessObject

    'Returns True if table is in alltables
    For Each obj1 In CurrentData.AllTables
        If obj1.Name = mytable Then
            IsLinked = True
            Exit Function
        End If
    Next obj1
End Function

Open in new window

Question by:Lambel
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    I generally add a table to the front-end of my which contains the names of all the tables I want to link, and the source of those tables.  Then I just loop through a recordset based on that table.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Am I missing something?

    Aren't the tables in the FE file already "Linked"?
    LVL 75

    Accepted Solution

    Possibly the slowness is because of the problem is the Sub Datasheets property in tables.

    Scroll down to 'Tables: SubdatasheetName'

    Be SURE this property is set to None for ALL tables, both in the FE and BE.

    You many need to use the code shown to set the property.  This makes an AMAZING difference when linking or with linked tables during operation over a network for sure.

    fwiw - case study.
    A few years ago, I discovered this.  Here is a short story on Subdatasheets:

    Client, 35 tables in BE - LAN
    Relink Time with Subdatasheet Property set to Auto: 5 Minutes
    Relink Time with Subdatasheet Property set to None: 30 seconds.

    Even just opening a table with 2-3 records can take 5-10 seconds with that

    property set to Auto. Set it to None - table opens instantaneously!

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    Good question.

    In many of my applications, I overcome some of the security issues by droping all of the linked tables when the application closes, and again as the first step when the application opens (in case of an abnormal closure).  I just assumed the OP was doing the same thing.

    When I look closer at the OPs code I realized that this function is just trying to determine whether a table is linked or not.  Which can be done in a single line of code in his/her main program.

    IsLinked = (DLOOKUP("Type", "mSysObjects", "[Name] = """ & TableName & """") = 6)

    Author Comment

    fyed:  No, most of the tables reside in a Back End.
    There are 13 tables that need to be linked on opening of the Front End.
    MX: Do I need to run this each time I link the tables to the front end, or just once on each table and then it is set for good?  How can I view these properties?
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "Do I need to run this each time I link the tables to the front end, or just once on each table and then it is set for good? "
    Just one time.

    " How can I view these properties?"
    See image.

    Be sure to read over the Allen Browne article also.


    Author Comment

    fyed: You are correct, this is just the loop for checking whether the table is linked. My apologies: that probably wasn't enough of the code to include.  Let me provide more info:

    This is a reporting database tool that will connect with an ODBC connection to an oracle database, run multiple queries, including looping queries on large archive databases, and output report files to a sharepoint site, along with email notifications to delegates for the regions reported on.  

    I will have multiple users running a front end from multiple physical sites.  They need to share the back end data.  The back end data consists of about 13 small tables used to set parameters for running queries and reports.  They share the back end over a network drive that is historically slow to access for many sites.  
    When we tested out opening the front end on my prototype user (who is located in another physical site), it took about 15 minutes to load. Each table it linked to was taking about 30 seconds, and the form load is taking another 5 minutes.

    I've added MX's suggestions to a new version, and hope to get the tester to try this out this morning.  I also suspect that the form I am using may be too big.  It has 8 tabs.  I am going to remove all but the first two, and try that, also.  

    If there is anything else you can think of that might help me speed up the load, I'd appreciate your comments.  And thank you for your comments and suggestions so far.

    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    1.  Once the tables are linked to your front-end, you should not have to relink them unless you are intentionally dropping the links.

    2.  You indicate "multiple users running a front end from multiple physical sites".  Do they each have their own front-end? or are they accessing a common front-end?

    3,  "they share the backend".  Do you mean they are sharing an Access backend, or are you talking about the Oracle database?  If Access, and you are running this over a "normal" WAN connection, that is probably your problem.  With the exception of "mx", whose situation is not "normal", I don't personally know anyone who has "successfully" implemented an Access FE/BE configuration over a WAN.

    4. "form load is taking another 5 minutes".  This is frequently the case when you have multiple tabs, each with its own subform.  What many of us do that use subforms is initially set the source object of those subforms = "", so that on form load, you are only populating the data for the main form.  Then, as each of the tabs is selected, we set the source object of the subform control on that tab to the appropriate form, and let that subform load.  Eventually all of the subforms that you need get loaded, but if a user is not using one or more of the tabs, then those tabs subforms never get loaded.

    Author Comment

    1) I agree - tables only link once
    2) Users each have their own copy of front end
    3) I think the answer is yes to both.  They share a small backend access db.  They also need to connect with the oracle db to run the queries.  
    Forgive my ignorance: The users are connecting across a shared national network drive...Is that what you call a Wide Area Network?
    4) That is a good idea, I'll try it.

    MX:  Thanks for the suggestion.  My usre just finished testing this out.  Unfortunately, the difference in performance wasn't noticeable.

    Author Comment

    The next thing I am going to try is to separate the first tab of the report (frmReportGenerator) from the rest of the form tabs, and only open up that initially.  The rest of the tabs are for maintaining the various report and email parameters and won't be used on a daily basis, so there really isn't a reason for them to open up initially.

    I've learned a lot with this first try at designing a db tool across a network.  I hope the next time around I will be able to save myself some time by keeping these issues in mind from the start  :-)
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "The users are connecting across a shared national network drive...Is that what you call a Wide Area Network?"
    Generally, WAN is if workstations are spread out over physical areas, which I assume is the case when you say 'national'.  For example, we have a super high speed (1Gbit) stable WAN, wherein our 'shared drives' are 30 miles away.  

    " Unfortunately, the difference in performance wasn't noticeable."
    You should double check to be sure that ... the [None] setting actually 'took'.  Sometimes, it doesn't stick ... and reverts back to [Auto].   And be sure you do this on both the Back End AND Front End linked tables.  Even though they are linked to the BE, for some reason ... this makes a difference.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now