• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Seed opinions - Access vs. VB

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.
1 Solution
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!

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.
RUSTYAuthor Commented:
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RUSTYAuthor Commented:
Adjusted points to 20
RUSTYAuthor Commented:
Adjusted points to 25
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!
RUSTYAuthor Commented:
Thank you.
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

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now