Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Desktop App with External SQL Server

Posted on 2011-09-09
Last Modified: 2012-05-12
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.
Question by:APD_Toronto
  • 5
  • 4
LVL 26

Expert Comment

ID: 36515242
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
ID: 36516825
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:  http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

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.


Author Comment

ID: 36516852
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.
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

LVL 26

Expert Comment

ID: 36517283
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.

Author Comment

ID: 36525584
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?
LVL 26

Accepted Solution

Nick67 earned 500 total points
ID: 36525711
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 www.experts-exchange.com
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

Author Comment

ID: 36526001
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.

Author Comment

ID: 36526037
Also, re- RDP. you aren't permitted to install Office on a server, correct?
LVL 26

Expert Comment

ID: 36552417
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.

Author Comment

ID: 36552433
thanks.  i'll definite cosider

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
File.Search issue 8 30
Sum in Split Form 17 27
Access Schema 6 24
query linked sql table field from access 4 20
Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question