Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Seed opinions - Access vs. VB

Posted on 1998-08-13
Medium Priority
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
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

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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 1468713
Adjusted points to 20

Author Comment

ID: 1468714
Adjusted points to 25

Accepted Solution

rlarner earned 60 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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 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…
Suggested Courses

636 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