Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

Split Access Database Optimizing Help

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
spaced45
Asked:
spaced45
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
spaced45Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
spaced45Author Commented:
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
 
spaced45Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Jim P.Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
spaced45Author Commented:
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
 
spaced45Author Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now