Access 97 vs. VB 5.0 ?

Posted on 1998-06-16
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 50 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help in WHSCRIPT 9 41
VB6 Compile Compatibility Issue 4 38
How does CurrentUser work? 10 26
Visual Studio 2005 text editor 10 20
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now