I have developed a customer order tracking system with Access 2003, Front end / Backend multi user scenario.
The situation is this. Customer orders are entered into our 3rd Party Accounting system which has an SQL back end. The problem is that this 3rd Party software does not provide the Order tracking or reporting funtionality that I require hence the development of my own system.
I have developed an Access application that links via ODBC to the SQL back end tables of our accounting database to extract the required data that I need to manipulate and enhance in order to get the data that I need. For obvious reasons I am not altering the data in the SQL database but merely extracting the data that I need to some small tables in an Access back end DB where I can do as I please.
At the moment I am extracting the data via a series of append and update queries (6 in all) with a macro which I have set to run via a form timer event every 5 minutes which works fine.
My query is this.
This application is a multi user application so if I were to distribute it as is then this auto update procedure would run every 5 mins for every user. While it only takes about 10 seconds to get through the queries it will be an annoyance to users who are in the middle of something and have to wait for them to finish for the application to start responding again. I have thought about just having a single front end application with the auto update turned on which would do the required updates every 5 mins as planned and distribute a second version of the app without the update facility switched on. This would work fine except I would have to ensure that the user with the update version (probably me) had the app running at all times to keep the data up to date.
I know that I could create or add my tables to the SQL database and create a local DTS package to do the updates automatically which is probaly "THE BEST" option, but one that would take a lot of learning on my account and I simply don't have the time.
What I am looking for are suggestions as to how I might perform these updates with the configuration as is, as seamlessly as possible.
Start Free Trial
