Seed opinions - Access vs. VB

Posted on 1998-08-13
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.
Question by:RUSTY

Expert Comment

ID: 1468710
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!

Expert Comment

ID: 1468711

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.

Author Comment

ID: 1468712
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?
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 1468713
Adjusted points to 20

Author Comment

ID: 1468714
Adjusted points to 25

Accepted Solution

rlarner earned 20 total points
ID: 1468715
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!

Author Comment

ID: 1468716
Thank you.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
LINQ return type for nested group query 6 74
VB6 Compile Compatibility Issue 4 90
Notepad++ how to remove delimiter : from beggning of the line? 3 94
Advice in Xamarin 21 57
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

863 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

20 Experts available now in Live!

Get 1:1 Help Now