MSAccess Front End Link to Back End Need Faster Process

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
Am I missing something?

Aren't the tables in the FE file already "Linked"?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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!


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:

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)
LambelAuthor Commented:
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?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.

LambelAuthor Commented:
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.

Dale FyeCommented:
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.
LambelAuthor Commented:
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.
LambelAuthor Commented:
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  :-)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.