mikemc1
asked on
Access 97 vs. VB 5.0 ?
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 !
Mike
Mikem@ams1.com
Mcmillan@etc.net
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 !
Mike
Mikem@ams1.com
Mcmillan@etc.net
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
You can accomplish security when it comes to insert, update and delete by using triggers on the SQL Server.
ASKER
Thank you taking the time to help me. Your input is greatly appreciated !
Sincerely,
Mike
Sincerely,
Mike
ASKER
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