Solved

Seed opinions - Access vs. VB

Posted on 1998-08-13
7
172 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:SPECIALIST
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!
0
 

Expert Comment

by:guinness
ID: 1468711
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
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?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:RUSTY
ID: 1468713
Adjusted points to 20
0
 

Author Comment

by:RUSTY
ID: 1468714
Adjusted points to 25
0
 
LVL 1

Accepted Solution

by:
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!
0
 

Author Comment

by:RUSTY
ID: 1468716
Thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

690 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