Solved

Split Access Database Optimizing Help

Posted on 2014-01-30
10
663 Views
Last Modified: 2014-01-31
I have created a Access 07 database that we store employee info along with vaction time requests. The db was intended for maybe a max of 2 people from the resource management team to be in it at a time but was just told that now I have to make it available for the employee's managers. It could be out company network but working in Access over a network is a nightmare when I split it.

I thought about creating a front end for the managers that would pull only their employee info into a temp local table. Once they were done working in records some code would just update the modified records but I think I am making it sound much easier than it really is.

More than anything I am looking for any resources out there that might be able to help with the network issue such as optimizing tips or examples that anyone might know about. At this point I have a split database and I am striping is bare to make it as lean as possible but it just doesnt look like its helping.

thank you,
0
Comment
Question by:spaced45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 39822931
Funny you should ask. I was just looking at the link MVP Luke Chung posted on his FMSINC web site. Of course, I've seen the article many times.  It's a summary of many things that would likely be posted here in this Q - so it's a good place to start.

Significantly Improve the Performance of Microsoft Access Databases with Linked Tables
http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

There are many other similar 'tips' on the site as well.

mx
0
 
LVL 1

Author Comment

by:spaced45
ID: 39822943
I am sure that it would be nice to create a temp connection to the database, grab all realated data for the manager and then have it submit back to the database through some update code. What do you think though? Nice but too complex and too much work? Or worth it? Thanks I'll take a look at the info now and let you know what I think.
0
 
LVL 75
ID: 39822959
I guess you mean a pseudo disconnected scheme ?

"but working in Access over a network is a nightmare when I split it. "

There are remedies for this, some  of which are eluded to in Luke's KB.

Here at work, I have 38 dbs in use by a combination of 160+ users over a 1Gb Fiber Optic WAN.  So ... trust me, it can be done :-)

For the dbs that have a large number (say >20) simultaneous users, I do employ a pseudo disconnected scheme - instead of Linked tables. And I also employ the Persistent Connection mentioned in Luke's KB.

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:spaced45
ID: 39822963
I think I might be missing something. The article says that it can be used for multiple backend dbs. Is the person assuming that the front end db may have multiple connections to backends? So does the code go into each frontend or each backend?

Were you able to understand it better than I am DatabaseMX?
0
 
LVL 1

Author Comment

by:spaced45
ID: 39822972
I think the part that is really confusing me is when it says

"For instance, if you have a form that controls the application and remains open during the entire time the user is using your database, add the code to the OnOpen and OnClose events."

i dont really have just one form open the whole time. Seems to me like that would be a cluster and make it confusing for the user. "Controls the application?" Could he be referring to like a "HOME" form or something? I dont know.
0
 
LVL 75
ID: 39822985
"Is the person assuming that the front end db may have multiple connections to backends?"
Yes ... the code takes that into account.

 So does the code go into each frontend or each backend?
FE.

I will be back on line later tonight ... and we can drill down on it.

mx
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39823157
Have you ruled out a SQL back end?

And possibly doing the initial split using Access and then later building out an internal intranet website for it?
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39823914
You can also have a HIDDEN form open when the app launches, and use that for your persistent connection. I use this technique for my apps, and it works well.

What sort of network environment are you dealing with? Will everyone be local (i.e in the same physical location), and on the same WIRED network? If so, then in most cases a standard split application, with the appropriate precautions, works in most cases.

If you're dealing with offsite users, then you'll have to setup some sort of Remote Desktop scenario (unless you have a dedicated, fast, and wide pipe to those offsite users).

If you're dealing with Wireless, then just don't do it. STandard bound Access apps just don't work well with wireless.
I thought about creating a front end for the managers that would pull only their employee info into a temp local table. Once they were done working in records some code would just update the modified records but I think I am making it sound much easier than it really is.
That depends on many different factors. For example, if two users could edit the same record, then you'll have to factor in something to handle "conflict resolution". That can be a monumental task, depending on the data needs. If you have truly autonomous data for each Manager, then it's fairly straight forward.
At this point I have a split database and I am striping is bare to make it as lean as possible but it just doesnt look like its helping.
What sort of issues are you having? For example, does the app load okay, but FormA takes forever to pull up? Or is FormB okay, but ReportC just churns and churns?
0
 
LVL 1

Author Comment

by:spaced45
ID: 39823974
Jim, I have and a SQL server would have been my first choice although we are not allowed to maintain our own. This is a departmental project and I've been given a stick and a rock to create fire.

Scott, most of the managers have dockable laptops that they use in the office. When they are home they use VPN. Now that I know has cause a ton of issues in the past with corrupt files. That is a great idea about the hidden form so I am trying that now. You are right conflict resolution is not something I have the time for or the desire to take on.

Employees currently submit Infopath forms when requesting vaction time which then submits to the database. In order to limit the about of hands in the db I set up a Infopath form for the resource team to process the request. From there the record just sits there.

Where the managers play a role in the process is that they need the information to reconcile their employee timesheets. For example once an entry has been recorded then they would check our HR system to see if the item is there and correct then add a check mark in our database to indicate that its out there. I tried to also create a Infopath form for them to do this but they complained about not being able to create custom filters among other things.
0
 
LVL 1

Author Comment

by:spaced45
ID: 39824638
Thanks everyone for the help. I came up with an idea but I thought it would require another post. Here is the link to the new question. Any assistance with that question would be much appreciated. Link to New Question
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

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