Improve company productivity with a Business Account.Sign Up


Access 97 vs. VB 5.0 ?

Posted on 1998-06-16
Medium Priority
Last Modified: 2010-05-03
Help - I Need Input !

I am currently programming in Access 97 to build several multi-user applications for our office. Eventually, we will have to move to SQL Server.
So, My questions are:

1) What are the major differences between Access 97 vs. Visual Basic 5.0, and are these differences enough to take on VB ?

2) What are the differences and similarities between VB and VBA ? ( Hopefully, this is not a redundant question. )

Thank you for your input !


Question by:mikemc1
  • 2
  • 2

Accepted Solution

tomook earned 150 total points
ID: 1463474
Tall order here. First, the syntax is very similar. The basic grammar is the same. The objects and tools available to you are different. There are many things you cannot do without in Access which do not exist in VB, for example DoCmd. There are other ways to accomplish the important DoCmd actions in VB. For example,
    DoCmd.OpenForm "Form1"

In addition, the event models are completely different. How much code do you have on BeforeUpdate and AfterUpdate events of controls? The intrinsic VB controls simply do not have these events. This is not a problem, you just code around it. The mindset is different between the two tools.

Bound forms are the rule in Access and less common in VB. Data binding is transparent to you in Access, in VB you use data controls. The data events actually happen on the data control, not the form.

btw, I have done many successful SQL Server conversions while retaining Access as the front end. You do not receive the full benefit you would moving to VB, but you still often get orders of magnitude speed increases. You sacrifice some flexibility and you cannot lock down the database as tightly as you can if you use VB.

If you are going to go whole hog and convert the front and back ends, it is often a good idea to make two projects. The back end conversion can be made *almost* transparent to Access. There are automated tools, like Access Upsizer (, which convert your forms and modules to VB and fit the event models together as well as they can be fit. Let me know if you want more info on that.

Reports are a problem. Crystal Reports is in no way comparable to Access reports. Getting better, but still a long way to go. Data Dynamics just introduced ActiveReports, which is an Access-like report generator for VB. n-Dimensional is working on a report converter using ActiveReports, so keep your eyes open.

Is there anything else you would like to know?

Author Comment

ID: 1463475
Thank you, this information was very helpful to me. I do, however, have a follow up questions for you.

1) Is it common to use Access 97 as a front end to an SQL Server    database ?
2) Are you saying that Access 97 is faster than VB 5 ?
3) How serious is the lack of flexibility with Access 97 ? Is
   this reason enough for most corporations to use VB over
   Access 97 ?
4) How serious is the problem regarding the lack of a tight lock    down on databases within Access 97 ? Is this reason enough for
   most corporations to use VB over Access 97 ?

Thanks again - Mike

Expert Comment

ID: 1463476
1) I don't know about the general state of the industry, but we find that even when an app is moved to VB, it is common to keep an Access app around for reporting if nothing else. Many times an EIS (executive information system) will be in Access due to its flexible reporting, even if everything else is in VB.

2) Access97 development is faster than VB5, runtime VB5 is faster, smaller, and consumes fewer resources.

3,4) The flexibility lacking in Access is a problem for certain classes of applications. Primarily high security requirements or high volume OLTP rule out Access.

The main security problem is Access must have writable views in SQL Server to accomplish anything. This means anyone with access to the server and permission to the view would be able to freely manipulate the data in that view outside your user interface. This can be prevented with VB, using RDO/ADO and stored procedures. For example, we had instances where we needed to give some users and processes write only capabilities. In Access, if you can write you can read.

In regards to OLTP, reduces the performance of client/server systems. Access adds transaction layers over the server transaction layers, executing extraneous locks at times, which slows everyone down. In many cases, you will not notice, but the problem is there. In an environment small numbers of updates and large numbers of connections, VB allows opening a connection, do your work, and disconnect, freeing up server resources.

I want to reiterate that we have used both Access and VB in client/server environments and each has its strengths and weaknesses.
LVL 12

Expert Comment

ID: 1463477
You can accomplish security when it comes to insert, update and delete by using triggers on the SQL Server.

Author Comment

ID: 1463478
Thank you taking the time to help me. Your input is greatly appreciated !




Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

606 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