Desktop App with External SQL Server

Hello Experts,

Currently i  have an app wit the user interface and database both in Access (seperate files),.  As I'm planning to migrate the DB to an offsite SQL server(accross the country at that)with also an online portal accessed world wide, I'm worried about connectivity speed and dropped connections.

I'm not a network person by far, but my client expiriences too many dropped connections (maybe i should contact their service provider who is Rogers DSL Canada), also my connection is steady (also DSL), but I've noticed when connecting to the external server via SQL Management it was slower then when connecting to a local DB.

I'm looking for suggestions on how to make this quick and stable, as this is a dispatch application in a fast-paced environment.

Some random ideas i've had were moving the server close and somehow having a direct connection to the data center (if that is even possible), or having a failover server at the client and at the host, or having a T1 connection, which I am not sure what it is? I would like to keep the server outsoursed as my client does not have any IT department.

Any ideas will be appreciated.
APD TorontoAsked:
Who is Participating?
Nick67Connect With a Mentor Commented:
If you license the server as a terminal server, you can run as many concurrent RDP sessions as you have licences and the server's hardware could support.
Virtually any server purchased in the last 3-4 years should be able to support 3-5 concurrent users without problem
<ideal upload/download speeds> 10 Gbps if you could get them <grin>
But you can't.
Speed is not really the limiting factor for RDP.
You aren't passing huge amounts of data.
To grasp the idea--satellite internet is fast, you can download large amounts of data quickly.
It's crappy for RDP.
Your mouse click has to go to orbit and back before anything happens.
That's latency, and high latency networks are crappy for RDP.

Shaw runs a fake 10.x.x.x addressed network.
RDP to static IP'd Telus servers is murderous, because they expect you to be accessing cached web pages, not ACTUALLY doing stuff on the internet.
I don't know what Rogers is like.

Have your Toronto guy do a tracert to your Vancouver server.  Look at hop many hops it takes, and how many ms each hop takes.
Fewer hops and low ms are best.

Here's my tracert to
Why it routes to NewYork before going to SoCal is anybody's guess, when I am in northern Alberta EE tracertHere's one to Telus's own server.
Much better
 Telus tracert
In all seriousness, Access is not the front end for what you are describing.  I am beginning to explore Azure, but that has distinct limits.
RDP over fast ADSL is workable if there isn't too much latency
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
We have Access-SQL Server apps that run just fine over WAN connections.  They run more slowly than over the LAN, but it is workable.  They tolerate slow & dropped connections very well - SQL Server doesn't get corrupted like an Access BE would.  The main issue is a) how fast and reliable you need your app to be, and b) how good your connection to the server will be.

I don't know how SQL Azure would compare with a hosted SQL Server database in a data center.  There are pros and cons.  You'll have more control and flexibility with your own database on your own (shared or private) server, but the simple subscription model of SQL Azure has its benefits too.  Luke Chung at FMS has some articles on using SQL Azure with Access:

You'll definitely need to optimize your Access application in either case.  I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

If Access doesn't seem like the answer because of your need for speed, then you'll need to start thinking about a rewrite in ASP.NET, which is a much bigger effort and a whole new learning curve if you aren't familiar with it.

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

APD TorontoAuthor Commented:
For the remote desktop we are talking about 5 or 6 simultaneous users - can you use thats many RDP connections at once?

The best way to describe my appication is a taxi/delivery service, where reservations come in constantly, but there are few more details then just From/ To.

If I were to rewrite in ASP Classic, then we will be looking at time outs and some wait times, not to mention development time.

What I was acually looking for were hardware suggestion like moving the physical server closer or adding another failover server at the client site or changing connection types.
It has been my experience that when Access loses it's network connection, DAO based files bomb, and badly.  Now, SQL Server based backends don't get corruption, but the frontends can and do.  I have tried running local frontends to VPN backend on Telus ADSL in Alberta.  Forget itm. It's not happening.  Our Poweredge 1900 runs 7-8 RDP clients and hosts the backend without breaking a sweat.  3G+ clients work, but are slow and flakey due to 3G+'s high latency.  Physical proximity is not the issue.  Network robustness, latency and speed are.
APD TorontoAuthor Commented:
are you saying I can run several of RDP sessions at the same time?  If so, I assume with different user IDs?

I'm more leaning towards sticking with Access as the front-end, and SQL as ack-end.

to give you an idea, my client is in torono, but the server is in vancouver.

Perhaps, i should be asking, what would be ideal upload/download speeds and what utility i can use to measure?
APD TorontoAuthor Commented:
for ee i get 19 hops, 179ms w/1 timeout

for my web server i get 9 hops, 163ms, 2 timeouts

for my SQL server, hops 1-6 were 6ms, then 7-30 timed out.

What does all this mean?

<<Speed is not really the limiting factor for RDP.>>
but lke i said, i'm lening more towards keeping the FE in ACCESS on the users desktop, versus RDP, thats why i ask re supload/download speed.
APD TorontoAuthor Commented:
Also, re- RDP. you aren't permitted to install Office on a server, correct?
I thought I had put another post in here, but it must have gotten lost.
You can install Office on a server.
No Problem.
You can install the Access 2010 runtime on the server, which is free, also.

What you cannot legally do is install Office on a server and have machines that DON'T have Office installed accessing it as a means to avoid licence fees .
Other than that, it's all good.
APD TorontoAuthor Commented:
thanks.  i'll definite cosider
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.