|
by albert d. kallal using a wan with ms-access. - a instant network primer. 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 an 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
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 are 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
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! 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: 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! online part time jobs for students without investmentWebsite Testerpart time jobs weekends only 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 wans' 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! why a does jet file share corrupt when the connection breaks? i am not sure if you are a science fiction fan and seen movies like "the fly", or star trek. in these movies they have a device called a transporter. what happens is that you have a device that can re-assemble the person in a another location. this concept allows you to electronically transport a person to another location. if during transport the connection is broken, then you have parts of transfer on one side, and other parts on the other side. the result is a broken transport. you simply loose part of a complex jigsaw puzzle, and can't re-assemble it it. access and a jet file share is much the same as that transporter since pieces of the file are transferred over the network and used remotely. if those pieces can't make it back over the network, then you have a broken humpty dumpty that all the kings men can't put back together. when you run access on a single computer the mdb file is transferred from the hard disk to the computers temporary working memory (ram). this is a normal process, and is usually quite reliable. there is no network involved here. you have the following:
in the above you see the access file cusotmers.mdb on the hard drive. when you request a record in access only parts of the file are loaded into the computers memory. this is done for reasons of performance. despite popular rumors and miss-conceptions about a jet database file, the whole table is not transferred to memory. this process is quite different then word, or excel where the whole file is loaded from disk to ram. since ms-access is a database, and designed for maximized performance, then only the parts of the file are loaded into ram. this is why you can retrieve one customer record, and if the file has 100, or 100,000 records, the time to find and load this record into the ram is instant. remember, if you pull the plug on your computer when editing a word document, then the document never makes the trip back from temporary ram to the hard disk. if your computer freezes, or hangs up, then again the word document is not saved. in ms-access, a computer reset, or power off or freeze up is much more serous. the reason is that parts of the file are now on hard disk, and some parts are in the computers ram memory. hence, after loading some records from the disk into ram, you have the following setup: at this point, if you turn off the computer, or break the connection from the ram to the hard disk, then you have a file that is in pieces and cannot be re-assembled. the file is in a very dangerous state, and a system failure will means that those parts in ram will not make it back to the disk drive. when you have network, this means you are actually sharing the disk drive across the network connection. you thus have:
so, now you have a fragile connection from the remote disk drive over the network to your computer's ram. now, your computer must ensure that changes and those pieces make it back to the hard disk. if you power off your computer, or break the network connection, then those pieces on your computer will not make the trip back over the network to the disk drive. it is much more easy to break a connection between two computers on a network then from your hard disk to your ram. thus, a connection from your hard disk to your computers memory is quite reliable. however, if you share the file on a network, then other computers are pulling parts of the data file into their ram (memory). any break in the connection between the two computers means those pieces never make it back to the file the computer is on (this is just as bad as turning off the computer). the instant the connection is broken then the file can not be re-assembled and you have a corrupted file. the file can't be re-assembled, since pieces are in your computers memory. housewife part time jobs onlineOnline tutoring is another popular online part-time job. If you have expertise in a particular subject, you can tutor students online from anywhere in the world. Online tutoring platforms such as TutorMe or Chegg can help you find students to tutor.online jobs part time no experience it needs pointing out that when you use a true database server, the pieces and parts of the data file never leave the sever machine (they stay in the ram on the server box). so, data is sent over the connection, but never pieces and parts of the file. thus while ordering a book on www.amazon.com means that if you turn off your computer...your order will not be completed, but parts of the computer files on the amazon server machine never were placed on your machine (those parts never leave the server system). it is these pieces of the file" that you must eliminate when using a wan or wireless network. this is also the difference between a file share and a true database server like sql server. sql server will not corrupt when you break the connection because those parts of the file never leave the server. so, what can you do for wan? after all many companies do have remote use of their applications, and how do they do this? there are a few solutions what will
work well in a lower bandwidth environments with ms-access. use a true client to server setup. online part time jobsWorking from home can be a rewarding and flexible career choice, and online part-time job work at home promotion is a great option for those looking to promote products or services online. By following the tips and strategies outlined in this guide, you can set yourself up for success and build a thriving career in this exciting field.weekend part time jobs work from home you can move the data store from
ms-access to sql server, and then optimize your access 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 over a wan/vpn setup, but you are exposing your self to possible corruption and damage if the connection is not reliable. 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 applications 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 when you have a minute, you can also read my thoughts on thin client technology at: why bother with .net when you have thin client? 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 deploying an access application across a wan.
update: february 9, 2009: a new solution exists called sharepoint. time marches on and obviously we are not the only ones that want connectivity everywhere. one of the great new features for office 2007 is that ms access can be used with a new system called sharepoint. sharepoint is microsoft's road and plan to allow all products in the office suite to work seamlessly when people are connected to the internet (this is called "cloud" computing in the news media today). in very simple terms this means is you can develop applications in ms access with the tables (data) placed on sharepoint. ms access works well with sharepoint even in cases where you have limited bandwidth or connections such as wireless that can often break. perhaps even more amazing about sharepoint is you can use the free "on-line" edition of sharepoint called office live (www.officelive.com). the beauty of using cloud computing means that you don't have to purchase a server, you don't have to "pay" someone to setup a server. you don't have to pay to install software like sql server. you don't have to purchase anything at all. you simply just run on down to the office live web site and sign up. with this cloud version of sharepoint you simply install your access application on each user's computer. those user's can be anywhere in the world with any type of high speed internet such as wireless at an internet cafe and you all will be sharing your data. of course if your company already has sharepoint running at the man office then that would likely be the preferred solution over office live. however with a free online edition of sharepoint you can not only do this for zero cost but you'll likely be up and running in less time than it takes a phone call to get approval from your own main office to setup yours internal version of sharepoint. to say that this new sharepoint system opens up new doors and horizons for access developers would be an understatement of the century. here's a link to a video showing how this works in action: integrating office with live small business sharepoint technology is much based on the same concepts as replication (but you don't have to make any extra effort to set this up when you use access 2007 + sharepoint). so, access 2007 has what is called a "offline mode". this off line mode is much like email programs like outlook mail on your computer. you can edit and read and work with the data (or emails) on your computer without an internet connection. when you get back to the office or back to some civilized place where you have an internet connection, you simply click the online button and all the changes are shuffled back and forth between you and the data on sharepoint. without question i'll be writing some articles in the future about sharepoint. simply put, i seen the light, and for me the new road into cloud computing is ms access + sharepoint. update: april 2, 2010 access 2010 now has the ability to create web sites. these forms and reports are true browser based applications built in ms-access. these applications built in access can be consumed by any computer with a browser. this means even smartphone's, apple or even linux computers can now consume applications built in access. of course building 100% web based applications solves all problems of deployment and connectivity. the only technology required on the client side is a standard browser. no activex or silverlight or any type special add-in for the browser is required. for the server side, sharepoint 2010 and what is called access web services is required. here is a video of mine where i demo an access web application built 100% using ms-access. note how at the 1/2 way point in the video i switch to running the application 100% in a web browser. outlook style calendar form built in access - see this running in a web browser: (you-tube video - click here to watch)
access 2010 white paper about manageability and migration:
the access show: publish a access 2007 database to sharepoint with access 2010
|
