Updating three databases automatically

[krets]

I've been trying to come up with a way to update databases across three different locations in an automated fashion. I can't use a linked backend because our networks are currently overloaded so adding and accessing records takes way too long. I looked into replication but everything I read and heard said that replication can be a nightmare.

So here's what I'm thinking, please feel free to shoot as many holes in this as possible.

The database contains license information for technicians and pharmacists that work for my company. The two main tables are EmpTable and LicTable. We have sites in three locations that are abbreviated CB, CA, and OP. So what I am thinking of doing is creating three backends with tables named like CAEmpTable, CBEmptable, etc. Each table would have a column that would store the date any record was last modified.

Whenever a user opens the database, I would have a form that runs and would check a table to see when the last update happened. I only need to update once a week so when the form checks the field, it will automatically run an append query to combine the tables.

I haven't quite worked out all the details but from your experience, do you think this would work? What would be the downfalls?

I would greatly appreciate your input.

 

[Remou]

How do you see these updates working? That is, are the people in each location allowed to change data? How does that data get back to a central location? I think you would be much better off with a secure website.

 

[krets]

We would love to have this on a secure website. The problem is that right now our IT department is so bogged down with other huge corporate projects that the wait time for something like that is six months to a year. So in the meantime we're trying to come up with another solution.

My thought is that the update will run once a week. Whoever the first person is to open the database on Monday morning will be greeted with a form that tells them the database is being updated. Once the update queries have run the form will close and the person will go about their business. I'll probably also have a button somewhere that will allow admins to force an update if they would like.

The updates will consist of running an append query to pull any newly updated records from the other two databases and adding them to the local database.

Does that make sense?

 

[Remou]

So people in location A cannot make changes to records belonging to location B? You will probably need an update query as well. You will also need to remember that your database will be out-of-date almost all the time.

A little ASP is all that is needed for a website. Be adventurous!

 

[fneily]

Why do you need three separate databases? One should do.
You heard about replication but never tired it. So you don't know if it would work or not. Talk to people who had traffic accidents and driving a car is hard!
Networks are overloaded... talk to your network admins. What would be the number of records crossing the network? Less then half a million?
INTRAnet or INTERnet?

 

[fneily]

Or to paraphrase an old hair cream commercial -
"a little DAP will do you!

 

[krets]

The three sites are in different locations across the US. If the backend is kept on a network folder in one location the other two sites experience extreme wait times while data is loaded.

I have not tried replication, no. But from everything I have read and been told it is more headaches than it is worth.

It would be far less than half a million.

See, these are the problems that we are having a tough time solving because our IT resources just aren't available to answer the questions. Even if they were available, the implementation times on anything that would make sense are so long that we would still need an interim solution.

 

[jrbarnett]

Another possibility is to have a frontend app talking to SQL server or MySQL backend. If the apps are done properly with unbound forms or as ADPs, the bandwidth requirements will be far less than a linked table solution.

John