I usually code in VB and go straight to
the database for very short and quick hits
Yes, ms-access works very well that way also, but it depends on how conerned
the developer was about bandwith.
The JET database engine that ships with ms-access is remarkable little file
based engine. In applications where you deal with small files in the 50,000
record range, and say 50 tables (and a good portion of them are related to
each other!), and say about 5 to 10 users, the little engine just screams in
terms of performance (we are talking about a typical standard cheap office
LAN). With a well designed application, I find it HARD to create forms that
take more then 1 second to load. I have on more then one occasion replaced
mini computer systems with a ms-access on a file share for 5 or 6 users, and
the resulting performance on these small tables of 50,000 records is simply
amazing. Of course, tables with 50,000 records and only 5 or 6 users is not
much these days, but that little JET engine really does perform well.
However, what about using JET on a WAN?
It is amazing how many companies start deploying a WAN without having asked
some real basic questions about how the wan will function. When we talk
about a typical office network, we are referring to what is called a local
area network (LAN). A WAN is a wide area network. By wide area, we
typically mean different buildings, or even employees that want to work at
home (tele-commute), or even just a salesman on the road. Often, some
companies will actually connect branch offices via cheap high-speed
internet. The cost savings of using the internet to build a WAN can be
incredible. I seen companies get rid of huge high priced leased lines and
save thousands in monthly connection fees. Using the internet to build a
wan is usually referred to as a VPN (virtual private network).
The only other question we need to know here is how fast is the connection?
Before I give any more details here, the above is about all you need to know
about networking. Save the rest for the tech guy. I don't support, or setup
networks. I also don't work on my own car. However, I know about brakes,
oil and the kind of gas I am supposed to put in my car. When planning a
trip, I do look up the distance I am going to travel. I think we all agree
to not look at the distance we plan to travel would be silly.
So, how fast is the network? Did you bother to ask?
A typical office network is rated at 100MBits. In fact, I really don't care,
or even want to get into a whole bunch of talk about bits, bytes and kinds
of mumbo jumbo. Really, all we really need here is some sense of scale,
and the differences in speed between network connections that we plan to
use.
So, your office network has a rating of 100
A typical high speed internet connection that we can use to build our WAN
has about .7 to 1.5 mbit rating
So, lets take the middle rating of 1. That means that our high speed
internet connection is 100 TIMES SLOWER then the office network. Thus, your
typical WAN built around a high speed internet connection (VPN) is about 100
times slower then that cheap office network.
And, all those cool commercials about how fast high speed internet is
compared to phone lines is correct. A 56k dial up line is rated at .056.
Actually, the phone line is closer to about .04 (you never get an actual 56k
connection). So, high speed internet is about 1/.04 = 25 times faster then
phone dialup. And, yes those TV commercials are about right when they say
they are about 30 times faster then your dialup line. So, we have;
Office network = 100
High speed internet = 1
Dialup modem = .04
Ok, so do you think ms-access is going to work over a dial up line? Often,
you will find that ms-access is slow with a office network (the 100 guy).
So, if something takes 4 seconds on your office network, it will take 400
seconds on your WAN. 400 seconds = MORE THEN SIX minutes! On the dialup
line, you get 100 / .04 = 2500 seconds, or 41 minutes.
This means that ms-access typically will not run across a WAN.
We are NOT talking about some network fix here that can be overcome by a 30%
increase in speed. Even doubling your speed by increasing the speed of the
wan by 2 times, or re-writing the software to use 1/2 the bandwidth is NOT
EVEN CLOSE AND IN THE SAME BALL PARK of your office LAN!
When you walk into a sporting goods store and ask the sales man for a nice
little inflatable boat, so far that is ok. However, when you tell the sales
man that you are about to visit your family that lives across the ocean, and
the boat is your means of transportation then we have a problem. Some one
along the way needs to mention that rowing a small rubber boat across the
ocean is not reasonable. Of course, the difference in speed in your wan vs.
the LAN is the same thing. So, while most would laugh at the sheer folly of
rowing a rubber boat across the ocean, the same applies to trying to use a
wan that is 100 times slower. How can a company start to deploy a wan
without asking what is the current speed required for running existing
applications? The fact that I seen so many questions in newsgroups about why
the wan is slow is why I wrote this article.
In my opinion, any network admin who deploys a wan without first gathering
some VERY SIMPLE and BASIC information about the existing network
applications should get a real earful (perhaps termination is too harsh, but
if you are paying for outside tech help to set this network up, and the
recommendation is coming *from* your support company, or the ms-access
developers), then I would get a refund on time billed to set this stuff up.
To have a company spend real support dollars on a setting up a wan with the
pre-text that everyone with high speed internet will have a good usable
remote connection is really wrong. In other words, if you are reading this
article right now, you probably have someone who has already failed to ask
some basic questions about the network and its speed. I guess the real
question is who is at fault when this kind of thing happens. The same goes
for the sporting goods store when they sell you a boat? Should the store
tell you it is a bad idea to cross the ocean in the little rubber boat, or
perhaps do we need a warning label on the boat? Where I come from, this
stuff is NOT allowed to happen when a boat or a network is being setup!.
So, here is the ms-access question we are really asking:
Why is ms-access slower on a network connection that is also 100 times
slower?
Answer:
Because 100 times slower is a lot slower!
There should be no surprise here.
Companies like Microsoft when hiring people actually ask questions like:
How would you move Mount Fuji?
The reason why the above is asked, is that companies need people with a
sense of scale. How big, how far, how much!. We are not talking about a some
real complex software algorithm or even some type of complex networking
setup or protocol. We are simply asking what is the speed of the wan
compared to the office network. It is a dead simple question, but I continue
to be absolute stunned when networks are deployed, and basic questions about
speed are not asked!
In fact, a typical ocean liner can make the ocean voyage in about 6 days.
So, 100 times slower means that we are talking about 600 days. That is well
over one year. In fact, some single personal boats have ROWED across the
ocean in about 70 days. While the row boat was a small 20ft boat, the scales
of a factor of 100 times starts to really give one a sense of scale here.
Hence, trying to row across the ocean is MORE REASONABLE then tying to use
access on a wan that is 100 times slower!
In fact, the guy loaded up the row boat with lots of beer and milk. Hum, not
a bad idea at all! (and this is a true story!).
This network story gets worse!
If all of a sudden we had a super net (a really high speed internet) that
some are now starting to experience, then could you still use ms-access in a
file share across a wan? The answer is no, because WAN'S are subject to
minor and temporary disconnects. Ms-access when used in a file share mode
DOES NOT take well to any interruption in the connection. In fact, the
result is usually a corrupted file (you will have to go to the previous days
backup to restore the data). So, one bad connection can blow out the whole
mdb file. Since a bad connection can damage the file, even if we did get the
speed out of a VPN, the risk factor would be so high as to again make this
whole idea useless. This also why you can't risk running ms-access in a file
share mode on a wireless network either!
So, what can you do for WAN? After all, many companies do have remote use of
their appcltions, and how do they do this?
There is a few solutions what will work well in a lower bandwidth
environments with ms-access.
Use a true client to server setup.
You can move the data store from ms-access to sql server, and then optimize
your access
application. MS access is a great front end to sql server. In fact,
ms-access is NOT a database, but only a client to your database engine of
choice. In fact, ms-access is really just a nice development tool to build
applications with. The office cd does come with two database engines that
you can use with ms-access. One is JET. JET is the default engine for
ms-access, and it is not a client to sever based system. It is what we call
a file share. It is this file share that you need to eliminate over the WAN.
The other data engine on the CD is a free 100% compatible sql server based
engine. This server engine is called the desktop server, or MSDE (Microsoft
Data Engine). This engine is the same as sql server, and it is free if you
have the office CD. It means that ms-access will be running in a true client
to server mode. You can even use the Enterprise manger tools from sql server
to run and mange this little engine. It really is the same as sql server,
but is throttled for 5 or less users. Some people even have claimed to run
25 or more with the this engine by using programming tricks. Regardless, if
you outgrow the small engine, then it is a transparent process to switch the
data and code to sql server.
So, for low bandwidth applications, you might consider using the sql server
engine (either sql server, or the desktop engine that ships on the office
CD. This engine has been on that CD for the last two versions, and we
already beta testing office 2003. I am mentioning this fact since often
people don't realize that ms-access is now a true client to server product.
This just simply means that you can use ms-access to create secure, robust
and scalable applications for the corporate world. There are companies now
running ms-access applications with 1000 user seat counts. Really, the only
limitation of ms-access is going to be how much the server database can
handle. This means that ms-access has come a long way and has grown up from
being just a pc based database. I would even bet that ms-access is the most
popular client to Oracle next to Oracles own products!
With good ms-access designs, then using sql server can work in a high speed
net connection with ease. That means ms-access will work well with just a
speed rating of 1. Some even have had success with using dialup lines to sql
server.
Consider a Web based system.
Another possible solution to limited bandwidth is to turn the application
into a web based system. However, this really means that you are not using
ms-access at all anymore. In fact, you are talking about re-writing the
application and setting up a web server.
Consider using Replication.
Replication is a process where two disconnected databases can connect to
each other and exchange data. This type of setup can work very well for
salesman to take orders with a notebook. When the sales people get back to
the office, they then synchronize with the database on the server. All new
records added by any salesman will sync into the main databases. Further,
the reverse also happens, and thus all new data records will eventually find
their way to each notebook. Thus, replication is a good solution when having
a constant connection is not possible. Replication can also work quite well
even over a WAN/VPN setup.
Use Thin Client Technology
If you need to run an EXISTING ms-access application over limited bandwidth,
then Terminal Services from Microsoft is another real winner. It simply
rocks! TS is a thin client technology. Thin Client simply means that all
processing occurs on the server, and only screen updates and mouse clicks
are sent down the wire. The beauty of this system is that it essentially
works like a web based system. The difference between TS and web based
system is that TS does not use HTML, but a propriety and MORE efficient
protocol. You can even run TS inside of a web browser. For remote users, it
is fabulous technology. In fact, TS is really a remote control technology.
The remote support built into windows XP is based on the same technology
that TS uses (Remote Desktop Protocol). This is one of my favorite choices
for remote use of ms-access since you don't have to re-write your existing
application. In other words, you can deploy and use your existing ms-access
appcltions for remote use anywhere in the country and not have to re-write
or change one thing in your application.
More info in TS can be found at:
http://www.microsoft.com/windows2000/server/evaluation/business/termsrv.asp
I hope this article sparks you to simply ask a few questions to the network
person as to how fast the WAN is compared to the office network. I also hope
the above article will help those budding ms-access developers to avoid some
embarrassment when considering to deploy a access application across a wan.
