Any downside to executing an Application Object from one Access database to another?

Posted on 2013-09-17
Medium Priority
Last Modified: 2013-09-17
I have a split Access 2007 database.  The front end is distributed to about 20 users who run it on their desktops.  The backend which houses the data is out on a server and the users link to the tables.  When remote users use the databse it is extremely slow because Access does all of the queries at  the users desktop and so it transferring a large amount of data.  

To get around this I have been doing some tests using the Access Application Object process, sending the request to the backend database and running the queries there.  Then the data is  appended to a temporary table and then it is pulled to the front end database.  This speeds up the process exponentialy as only small amounts of data are passed back and forth.

My question is are there any downsides to doing this?  Usually there are only 5 or 6 users on the databae at a time, but on Fridays it can be up to 12 to 15.
Question by:Scott Palmer
  • 2
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 39500433
Just make sure to add in; error handling/Validation/Rollbacks, ..etc,  so that if any part of this system fails, you revert back to the original state.

Author Closing Comment

by:Scott Palmer
ID: 39500753
Thanks, had not thought very hard about those things.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39500990
...for example, if the computer crashes in the middle of creating the temp table, ...etc
...Glad I could help...

LVL 75
ID: 39501012
"Then the data is  appended to a temporary table and then it is pulled to the front end database. "
I don't quite get it. If the tables in the FE are linked to BE ... and you are running queries in the BE directly on those tables, why do you need a Temp table - and what is there to pull back into the FE ?

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

627 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