Solved

Seed opinions - Access vs. VB

Posted on 1998-08-13
7
167 Views
Last Modified: 2010-04-30
Our project is to create an operations application w/ multiple windows and databases: customer maintainance, orders, etc.  Calculations will be standard; totals on invoices, credit limit maintainance, etc.  This app will live on a central server with up to 20 or so users on simultaneously.                  Question: I seek opinions on whether it would be advisable to use Access or VB to build this app.  Of course, speed is an issue, and database security, and multi-user issues, and design, and anything else that comes to mind.                   Thank you for your thoughts.
0
Comment
Question by:RUSTY
7 Comments
 
LVL 3

Expert Comment

by:SPECIALIST
Comment Utility
My answer would be to use Visual Basic as the front end and Access in the background.  But with your situation with 20 users, security and speed, it really sounds like you may need a SQL server with VB,(and of course it depends on the size of a database also) but it is more expensive and very difficult to maintain. with access you can syncronize the databases but it is really a pain in the neck.



good luck!
0
 

Expert Comment

by:guinness
Comment Utility
Rusty,

Your best bet would be to use Visual Basic to create this soultion. Let me explain why. Firstly, with Visual Basic you are going to experience improved use as you can customize the forms you wish to use to enter data. Next, you are going to be able to add the .exe Visual Basic file to each computer taking the load from the server in which you wish to keep the Databases on. Also, you will have more control over the environment in which the user uses. They do not have the open environment of Access, so you have more safety of not having to worry about 1 user messing up your entire database.

Design may be somewhat more difficult in Visual Basic if you don't have the background, but if you know what you are doing you should be able to do this just nearly as quick as it would take to set up the forms and databases in Access. Of course, if design is the issue, I would for sure use Access. Not only because of all of the above options, but you can also use an SQL solution also. Plus, don't forget that Microsoft does have an Access Component so that you can add funcionality to your project if you are using an Access Database.
0
 

Author Comment

by:RUSTY
Comment Utility
Can you tell me more about the Access Component that adds functionality to a project?  What does it do?
Also, is the VB exe installed on the front end really quicker than, say, splitting the database in Access?
And, as far as security, can we without a password prompt at every turn set security as good with VB as with Access?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:RUSTY
Comment Utility
Adjusted points to 20
0
 

Author Comment

by:RUSTY
Comment Utility
Adjusted points to 25
0
 
LVL 1

Accepted Solution

by:
rlarner earned 20 total points
Comment Utility
Hrm...  I don't know what guiness was talking about for the Access Component.  He may have been refering to DAO (Data Access Objects), a technology MS has that is designed to work against Access databases (although it can work against an ODBC connection.)  
DAO in VB is the layer that I would probably use.  Keeping your executable code seperate from the database itself can often times be very helpful.  (e.g. if you screw up the forms in an Access database, suddenly your entire database can become unaccessable -- but if the executable is separate, then you can always open up your database in Access)  Just as a design policy I always prefer to keep data (e.g. databases) seperate from "executables" (e.g. forms).

I don't understand what you mean by 'splitting the database in Access'.  However, there is not really any benifit to writing something in Access.  Access forms are either written in macros (which tend to be slow), or modules, which use a bastardized version of VB anyways.

For security, you can (in my opinion) make the VB executable much more secure than Access, although it takes more work.  Access does have some limited access control, but you can't set it on a per-record basis.  You must give someone a password to the database that (at least) allows them to edit an entire table if you just want to allow them to edit one field.  In a VB app, on the other hand, you can hard code a username/password connection to the database that the user will never see, and then implement your own security.  That way the user can never open the database directly -- they must always go through your executable. Then, each of your VB forms would check your defined permissions for the user and only allow them to view/edit/delete/etc. exactly those things which you want them to.

Also, as someone mentioned earlier, if you are really looking for speed or robustness, you may want to switch to a different database (e.g. some SQL server).  Access is not very robust, no matter what MS says.  If you ever try to deal with any advanced database manipulations or with VERY large amounts of data (greater than 10mb), Access can become VERY flakey.  Access also has a large problem with database size -- it tends to grow and grow, even if your tables are not growing in length.
Also, if your database ever gets very large, if you are using Access itself to run your application and putting your database on a server, it will bring down the ENTIRE database before you get to use a single bit of it!  This may even be a problem with a VB app accessing an Access database, and may be another reason to switch to some SQL server.
Now, multiuser and Access do not always go together very well, especially in an authoring enviornment (ie where multiple users are allowed to EDIT the data at one time).  Performance in this situation can _DRAG_, and reliability is very questionable.  This may be yet another reason to switch to some SQL server and use an ODBC connection.  
Now, both VB and Access can be remapped to use ODBC connections -- but it seems to make much more sense to not keep around Access if you don't need it.

Anyways -- I do recommend going with the VB executable.  Good luck whichever you choose!
0
 

Author Comment

by:RUSTY
Comment Utility
Thank you.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 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…

771 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

15 Experts available now in Live!

Get 1:1 Help Now