Solved

Split Access Database Optimizing Help

Posted on 2014-01-30
10
643 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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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.
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 33
Access 2010 3 15
Unbound TextBox: Change color when clicked 5 13
Spell Check in VB6 13 41
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

771 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

11 Experts available now in Live!

Get 1:1 Help Now