I was presenting at the DevConnections conference in New Orleans last week, when one of the attendees approached me to discuss a problem. The attendee’s company had created quite a large application using Access. They were now running into problems because the number of users had started to climb. The plan to solve the problem was to rewrite the application in Visual Basic. I almost cried.
Access is not a database. Access is a tool for creating front ends for databases. To my mind, Access is the pre-eminent rapid application development tool for creating database applications. Because a tool for creating database applications is useless without a database, Access has always shipped with a database engine: Microsoft Jet. Access 2000, ships with two database data options: Microsoft Jet and any of the many databases that Access 2000 will also automatically link to including Microsoft SQL Server databases.
The problems that the attendee was having were completely unrelated to Access. What his company had run into was a limitation of the Jet database engine. The solution to this problem wouldn’t be to replace Access. If any part of the system should be replaced, it would be Jet. The user needed to upgrade to a database engine that could handle multiple simultaneous users (Oracle or SQL Server, for instance). There would, of course, be problems with this process. Versions of Access prior to 2000 don’t work efficiently with client/server database engines. However, a simple upgrade would allow the user to look at their system and decide which parts of the application were running too slowly and would need tweaking. Frequent Smart Access contributor Russell Sinclair has a book coming out from APress on upsizing from Access to SQL Server that would be helpful if they decided to go with SQL Server (I’ll declare my conflict of interest here: I was the technical editor for Russell’s book).
If the attendee’s company did convert to Visual Basic, what could they expect to get? Unlike Access, Visual Basic fully compiles your code into machine language that can execute directly on your computer’s CPU. Access, on the other hand, compiles only to a form of pseudo-code that must be interpreted before it’s executed. This imposes a performance burden on Access applications. However, most database applications aren’t constrained by the speed of their code, but by the time that they take to access your data. To sum up, your database runs at the speed of your hard disk. Worrying about compiling your application code is like worrying about speeding 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 Windows functions and subroutines that are themselves fully compiled.
Even if your program will run faster when compiled, you have to consider what your costs are. Converting a program to run with Visual Basic typically means writing a lot of code to extract data from a database, 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 code to write, and it’s code that Access, with its bound forms and reports, takes care of for you. Given how expensive programmer time is vs. the cost of computer upgrades, the cheapest way to speed up an application constrained by code execution time might be to buy a faster computer.
If it does turn out that writing code to move data between fields and forms is the most efficient development process, Access doesn’t stop you from using that technique. With client/server databases this is often the best way to use Access. In this situation, you might be well advised to use Visual Basic, since the development style that you’re using is the 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 parts of your application where performance isn’t critical or where bound forms gives you the response that you need.
Of course, it’s not an either/or situation. I use Visual Basic primarily to create objects and assemble them into components. When I have a performance problem with Access that’s actually related to the time that it takes to compile my code, I move that code into a Visual Basic ActiveX DLL and create a new component. I then call that component from my Access program. In addition to clearing up an interpreted code bottleneck, I also get a new object that I can potentially use from other applications. Finally, with Access 2000 the rules are starting to change. Access 2000 allows you to create applications with bound forms and SQL Server databases that run efficiently. The only thing that would be too bad would be if the next version of Access continued to work only with SQL Server