Solved

Split Access Database Optimizing Help

Posted on 2014-01-30
10
646 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
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 84

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

947 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

23 Experts available now in Live!

Get 1:1 Help Now