It’s Not A Database

I was presenting at theDevConnections conference in New Orleans last week, when one of the attendeesapproached me to discuss a problem. The attendee’s company had created quite alarge application using Access. They were now running into problems because thenumber of users had started to climb. The plan to solve the problem was torewrite the application in Visual Basic. I almost cried.

 Access is not a database. Access is a tool forcreating front ends for databases. To my mind, Access is the pre-eminent rapidapplication  evelopment tool for creating database applications. Because a toolfor creating database applications is useless without a database, Access asalways shipped with a database engine: Microsoft Jet.  Access 2000, ships ith two database dataoptions: Microsoft Jet and any of the many databases that Access 2000 will alsoautomatically link to including Microsoft SQL Server databases.

 The problems that the attendee was having werecompletely unrelated to access. What his company had run into was a limitationof the Jet database engine. The solution to this problem wouldn’t be to replaceAccess. If any part of the system should be replaced, it would be Jet. The userneeded to upgrade to a database engine that could handle multiple simultaneoususers (Oracle or SQL Server, for instance). There would, of course, be problemswith this process. Versions of Access prior to 2000 don’t work efficiently withclient/server database engines. However, a simple upgrade would allow the userto look at their system and decide which parts of the application were runningtoo slowly and would need tweaking. Frequent Smart Access contributor RussellSinclair has a book coming out from APress on upsizing from Access to SQLServer that would be helpful if they decided to go with SQL Server (I’lldeclare my conflict of interest here: I was the technical editor for Russell’sbook).

 If the attendee’s company did convert toVisual Basic, what could they expect to get? Unlike Access, Visual Basic fullycompiles your code into machine language that can execute directly on yourcomputer’s CPU. Access, on the other hand, compiles only to a form ofpseudo-code that must be interpreted before it’s executed. This imposes aperformance burden on Access applications. However, most database applicationsaren’t constrained by the speed of their code, but by the time that they taketo access your data. To sum up, your database runs at the speed of your harddisk. Worrying about compiling your application code is like worrying aboutspeeding in the city: You just get to the next red light as fast as possible.In any case, much of your Access pseudo-code consists of calls to Windowsfunctions and subroutines that are themselves fully compiled.

 Even if your program will run faster whencompiled, you have to consider what your costs are. Converting a program to runwith Visual Basic typically means writing a lot of code to extract data from adatabase, move it to the fields on the form, track whether the data is changed,and move it from the form back to the database. This is a fair amount of codeto write, and it’s code that Access, with its bound forms and reports, takescare of for you. Given how expensive programmer time is vs. the cost ofcomputer upgrades, the cheapest way to speed up an application constrained bycode execution time might be to buy a faster computer.

If it does turn out that writingcode to move data between fields and forms is the most efficient developmentprocess, Access doesn’t stop you from using that technique. With client/server databasesthis is often the best way to use Access. In this situation, you might be welladvised to use Visual Basic, since the development style that you’re using isthe one that you’re obliged to use with Visual Basic. On the other hand,sticking with Access allows you to continue to use bound forms with the partsof your application where performance isn’t critical or where bound forms givesyou the response that you need.

Of course, it’s not an either/orsituation. I use Visual Basic primarily to create objects and assemble theminto components. When I have a performance problem with Access that’s actuallyrelated to the time that it takes to compile my code, I move that code into aVisual Basic ActiveX DLL and create a new component. I then call that componentfrom my Access program. In addition to clearing up an interpreted codebottleneck, I also get a new object that I can potentially use from otherapplications. Finally, with Access 2000 the rules are starting to change.Access 2000 allows you to create applications with bound forms and SQL Serverdatabases that run efficiently. The only thing that would be too bad would beif the next version of Access continued to work only with SQL Server.

About Garry Robinson

He is the guy who originally put this Smart Access web site together. Head to www.gr-fx.com.au for more about him and his company. His main claim to fame is winning the Microsoft MVP award for services to MS Access from 2006 to 2018.
This entry was posted in Editorials. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.