Best way to replicate tables b/w databases?

[axilla2]

I have 3 front ends where all the data entry takes place. The data is stored in each of the front ends. I tried using linked tables so that the 3 front ends all point the data to a central place, but because the 3 databases are in US, Ireland and Singapore, i get corruption due to network issues.

So...my problem at the moment is these 3 databases need to be synched up every day so that we can run the reports.

I am invisioning yet another database, the 4th, that will take information from all 3 sources and combine them into one table. This has to be automated to the point where i dont have to touch it every day

I've already started on the 4th database and created a marco to pull the 3 individual tables.
What's the easiest way to actually combine them into 1 table? The macro just copies the tables with different names (tbl1, tbl2, tbl3).. i need to get these all into one.
Sounds pretty elementary, but i dont know what would be the easiest way.

thanks..
any other comments certainly appreciated. i'm relatively new to this. so bear with me

 

[mpastore]

I would look into Citrix terminal services. it was made for this scenario.

Mike Pastore

Hats off to (Roy) Harper

 

[axilla2]

Hi Mike,
we do use Citrix quite a bit. My initial design was based around Citrix, in other words, 1 central dbse and all 30 users hitting the same dbase through Citrix.
Good intention, but the thing crashed in 30 minutes. major corruption, i had to scap the idea and go back to 3 databases. I think the volume was too much for the Citrix server or the database to handle.
Anyway, if you have any insight on the Citrix issue that may give us another opportunity, please let me know. I am certrainly open to suggestions.

thanks-

 

[mpastore]

How big are your databases?

Mike Pastore

Hats off to (Roy) Harper

 

[axilla2]

not very large, the largest of the 3 compacts to about 5mb. the biggest table being about 4000 records. the rest of the tables are lookups, <20 a piece.
although, i have to compact regularly, the size is 15-20mb normally.

 

[mpastore]

30 users may be a little high for an Access db if they are all inputting data simultaneously. I'm surprised you experienced problems so quickly though.

Something tells me this may be more a network issue than an MS Access problem. Have you considered going ASP for data input? This would get around having to use citrix at all. This may be over-simplifying the issue, especially if you have already written a lot of code to handle form events.

Wish I had more to offer on this. Good luck.

Mike Pastore

Hats off to (Roy) Harper

 

[axilla2]

i've considered actually throwing the whole thing out the window and going with Lotus Notes, but at the moment cannot allocate the resources needed to develop a new database, nor do i see the management willing to jump for it.

For now, just looking for a temporary solution until we can get the whole thing on ASP.
Thing is, I'm not really a developer, i basically tinker with Access and a few other things and so far we've been limping along until they can get the resources allocated to do the web interface.

so how do i go about synching these tables in the mean time? i'm talkin any half-assed way that'll work. i just need to get 3 tables into 1 via a macro. if this thing's gotta be a rig, that's fine. it'll be something to have a laugh over years down the road

 

[Comstreem]

I have a similar problem to this, Multiple locations out in the field with one main location that all the data has to be dumped to. Since I am using Access 2002 I was planning on exporting the data to an XML file from each of the locations, then importing the data to a copy of the database at the central location. However the original designer included an AutoNumber fields for the Primary Key of the tables which could never be imported properly. Fortunately this has been changed and my only problem now is how to handle Duplicate/Updated records since XMLImport will not overwrite (dumps errormessage into a ImportError table) I suppose I'll have to create temp tables for importing and exporting on both ends then update the main tables and flag update/insert errors.

Maybe that helps, Maybe not... seems like a plan