sharing database across a WAN

I apologize for posting a thread that has most likely been covered in some detail. However I still am not finding any real good solutions to this so, thought it was worthwhile to start a new one:

I have created a database for a company that has two branches; one in Seattle and one in Florida. Originally the database was intended for the Seattle location, now they want to share it with the Florida branch as well. They have tried doing so by placing it on the network in a shared directory. They are connected by a DSL line, specs of which I am unsure. They were unhappy with the performance of the database when used at the remote site (Florida).

All of my research seems to indicate that MS Access does not work well as shared across a WAN and that corruption problems could result from trying to do this. Basically it appears to me that sharing across a WAN is not a good idea even if they were to beef up their bandwidth.

There are really only two main tables that need to be syncronized between the locations. I am working on a solution whereby they will use the send object command to send an email attachment with two text files representing the data in the two tables. I have tables linked to the text files (after they are moved to the correct folder on the PC) and then append queries will appent the data to the respective tables.

I have tested this and it can be done, and I also believe it is fairly straightforward for the end user.

But...I really do not care for that solution very much. It would seem to me that it would be best to have them actually working on the same database.

So, my question is...what is the best way to handle this scenario?

Thanks!

If they have a windows server you may find using terminal services on the server and remote desktop on the workstations a good solution. You would keep the db in one location and performance should be acceptable subject to the server being beefy enough etc. as all you are doing is transfering a screen image to the workstations. I'm sure your network administrator can give you some guidence on this.

I agree with John. We use a Citrix/Terminal Server set up.

The problem is that Access usually hauls all of the data across the network to carry out operation on the user's machine. Citrix/Terminal server carry out the computing on the server, and only the screen display and key presses/mouse movements are sent across the network.

If this is not viable, you can help reduce the data traffic with good design, making sure your forms are based on queries and so on. You could split the database and include all of the non-essential tables in the front end and just leave your two main tables in the back end. This might help.

Thanks, I think that perhaps terminal services is the way to go on this.

One more way to do this, that we do, is use replication. I have the master on our LAN here in Portland, OR and the replicated version is in Sacramento, CA. Then, I use Microsoft Replication Manager 4.0 (available in the MS Office 2000 Developer edition) to do the automatic synching.

John.Woody said:

If they have a windows server you may find using terminal services on the server and remote desktop on the workstations a good solution. You would keep the db in one location and performance should be acceptable subject to the server being beefy enough etc. as all you are doing is transfering a screen image to the workstations. I'm sure your network administrator can give you some guidence on this.

Click to expand...

I have clients who do this between cities quite successfully.

Is it possible to handle more than 1 offsite location this way, or can remote desktop/terminal server only handle 1 remote?

My understanding is that it's like PC Anywhere or the like, where the Access application is displayed at the server, and then viewed at the remote location. If so, you can only have one offsite user.

SHADOW

You have to buy the correct number of licenses for terminal services to cover the number of concurrent users. Windows server software comes with one for administrative purposes but additional licenses can be purchased. I beleive this may be dependant on which edition, i.e. with server2003 small business edition may not support more than one session where as standard or premium will. Don't quote me on theses specifics, check before you buy

Again I agree with John. We have hundreds of terminal services users, based on clustered servers. So long as you have the server power, I don't think there is an upper limit. When our roll out is complete it is likely that we will have around 2,500 users.

John.Woody said:

You have to buy the correct number of licenses for terminal services to cover the number of concurrent users. Windows server software comes with one for administrative purposes but additional licenses can be purchased. I beleive this may be dependant on which edition, i.e. with server2003 small business edition may not support more than one session where as standard or premium will. Don't quote me on theses specifics, check before you buy

Click to expand...

Ok, but can they handle concurrent Remote Desktop sessions with each user doing their own thing to the same database?

SHADOW

shadow9449 said:

Ok, but can they handle concurrent Remote Desktop sessions with each user doing their own thing to the same database?
SHADOW

Click to expand...

Yes. A terminal services user is no different from a PC workstation user as far as this is concerned.

Thanks for all of the good information, I'm still listening.

Depending upon size

I have 140 offices across the country for Six Sigma users, and diestrict users.

The districts could only handle excel, so i built automation to send an Excel data extract file through email, saved to a server, and then 140 districts updated the ACCESS forecast database in less than 3 minutes. The excel code to read the predesigned file is on the board here.

The BlackBelt databases are small, so i set them up with their own
database, and then update the identical server based database after they are finished, instead of driving the app across a frame relay network. . . very slow and inefficient to do anything but copy a file across the network.

The other point is that because the file size is small, i copy the main database across the FR Wan faster than opening it and updating it. . .

not sure how big your application is. . . mine is small, but it generates a 200 page report at the end of each month. . . ugh! and the forecast database is big, 100 megs on just the datatable, and i hit some container limits. . .

sportsguy