Question regarding architecture of a networked database

[ep63]

BACKGROUND
I work work for a department that has a small number of users (5 or 6) who work in different states.

We use an access database to do analysis on small numbers of customers, say 20 for each user and maybe 2-3 records average per customer. Most of the work is written analysis based on information populated into the database from another source, although there is some entry of data as well.

Currently each user has a separate copy of the database populated with his own customers. Each user works locally on their database and periodically backs up to a central server. After all the work for the project is complete (maybe 60 days start to finish) all the databases are combined on the central server.

The central server version is then used for manager review and then as an archive/reference of the project.

There are many problems with this, including that it is difficult for the manager to review completed work in a timely manner. The databases are not combined until all users are done analyzing all customers, where as each customer is completed (2 or 3 days) the manager would like to review them.

I am told they once tried a split database with front ends on each users desk and the back end at the central server, but it was way too slow at the remote locations.


QUESTION
I have beeen thinking about ways to improve this situation. I am wondering what would be the consequences of reversing the typical link direction of a front/backend database. Basically leaving a full database on each user's desk, but having a central database that links to each user's tables.

I am envisioning a process whereby when a customer is marked as complete by a user, the data would be transfered from the working tables to tables that are linked to the server. Once at the server, the various user's completed customers analysis could be combinded programatically and the manager could have acces to all the completed analyses locally.


1) Would the slow links only effect the central database or would it effect the user as well? Would an intermediate linked database on each user's desk help?

2) Since the record volume is small and no querys would be sent across the network, would network traffic be manageable?

3) Is there another solution that I should consider?



Thanks for any thoughts.

[A S MANN]

Give the details of your network. Is it cat 5 / cat 6 cable? What Type of switch/ Hub you are using. Is It a Static IP address given to each sysyem of set to Auto(DHCP) ?
The DATA in network need to move from a one location to another by various ways
1: Keep One Back end at access\SQL Server\Share point and multiple Front end given to each user.
2: full DATABASE to each user and a append query to append new added data to back end.
Use Share point server as web depository (Installed at Server) and use Front end to access it.Data can be seen / used by Browser.
The proper network is backbone on which data move from a location to another.
I can help you to configured your network properly to solve the issue

[ep63]

No sure about the network cable/switch/hub - We have a secure gateway and sign in to Novell and use Juniper. We have people working from home in some instances on broadband cable and other in offices in different states.

It is a pretty sophisticated network, but we are just a small unit in the whole and want to keep control over our work application for flexibility.

We don't have SQL server or Share Point although if we had to get it we could, but I have enough access knowledge and experience to maintain/develop my self, whereas I have no SQL server experience.

I think you are saying that what I am proposing would work, but you would recommend SQL or Sharepoint to hold the data.

In the mean time, I have created a database at the central server, and have linked it an offsite database. The offsite database is a full combined database and the one on the server just has the linked tables at this point.

I have not seen any performance degradation at the offsite database at this point, so this may be the answer - my concern is that I don't know what the links do to network traffic. I think not much since so little data is involved but am not sure if just having links exist have a large cost to network performance?

[NTC]

Access FE & BE must co-reside on the same LAN. As your users have discovered in their attempt to put it at the central server.

You have a consolidation method, that seems to be semi manual but works. Why not simply consolidate info more frequently? Depending on how that is done it shouldn't be interruptive to anyone. You could have the remote locations send in their file daily or weekly so management can review things in a more timely fashion.

If you are inclined to move toward the Sharepoint product, and have Access07 or later then the consolidation can be somewhat more facilitated by using Sharepoint at the central site; this configuration permits each remote user to 'work locally' and then consolidate the data to Sharepoint.....

Otherwise it is moving to a web oriented solution; and again Access with Sharepoint using webforms could be a solution but there are a myriad of other potential web environments.

hope this helps.