What is the difference between Access and Excel?

There's a lot, but I'll go into the generals with this.

Access does not allow for data corruption so easily by a user, such as selecting all but one column, Access will still include all fields for sortings, Excel won't. Excel now has a warning message box that comes up, but in it's early days, it just sorted regardless. Therefore to say the least, Excel is very clumsy with data management.

Access can store X number of records, Excel can only store up to 65536 records on the same sheet without having to use a different set of columns or continue on with other worksheet(s).

Access has a 2GB memory usage limitation as opposed to Excel having a 80 MB RAM (160 in XP and 03) usage limitation before it crashes.

Access can query data very easily and NOT run into issues too easily. In Excel, if using multiple files to compensate for the RAM usage limitation not to mention that calculation also eats away the RAM, try using External Link formula. This works fine at first, but when it references at least roughly 13,000 cells externally, Excel crashes.

Access Forms has lots of form events that one can use (though I don't care for how data validation works on bound forms/objects cause it not so user friendly for those that's predominantly mouse users, so I had to build with UNBOUND objects to make the forms more user friendly without sacrificing the strict data validation checks), Excel UserForms has very little Events to work off from.

Access has a fairly good security model, but I'm still not saying it's real good as it still lacks a few things for what I would like to see from a security stand point of view. Excel Security is nearly non-existent. Yeah, it does have security items, but it's not that great as I can demostrate too many issues such as users can still copy hidden cells on a protected worksheet and paste the information onto another worksheet that's not protected. That's just one issue of several.

Access is designed to have multiple users working in the same DB file(s) along with the various safety precations items to help protect the data such as record level locking. Excel has very limited multiple user capacity within the same file.

Excel has a lot of calculations/functions that is easily and quickly used for small scale projects. When you need to find the result of some expression, Excel is the way while finding some information based on some data criteria, querying the data in Access is the way.



Okay, so Access has all of these different advantages over Excel, so why then don't more users switch to Access?

Access is not nearly as intuitive as Excel is. This is where most users fringe away from Access.

Okay, so we still have a relatively large number of users that understands that this is going to require some additional time & learning, and accepts that. Now as they get to building their DB program using the wizards and other automation features of Access, they then start to run into different types of issues as they test things out. At this point, frustration starts settling in cause things aren't working as well as one expected. Now, a large amount of the remaining people drops out at this point.

Okay, so this means we have to do some Macro/VBA work, which in turn means we should learn how to write in SQL (Using the QBE helps a lot in the aid of this learning process of SQL Writing) in addition to learning how to create macros/VBA codes, since that is what some of the RowSources are going to be dependent on within the forms and/or controls. At this point, we are basically down to the technical and programmer type of people to set this up.

After going so much further and learning how to work in the programming environment of Access, this group starts to run into other issues that is not liked. At this point, there's 3 options:

Option A, quit developing in the program and find some other program to do the development in. (Result, developer frustrated and doesn't want to continue with Access)

Option B, continue programming in the program, but don't provide any solution to the issue (Result, developer gives up on trying to find a solution to the issue directly, but rather work around the issue, thus the impacted users gets frustrated)

Option C, continue programming in the program, and do some R&D to find a direct resolution. However, when the solution is found, it may require a lot more development time than what was initially planned for to still keep the requirements of the program, but at the same time, allow the ease of user friendliness (Result, developer may be frustrated and not like the issue at hand, but decides to continue programming including programming a work around into the program that will keep the requirements and allow for user friendliness of the program. This however also means the program will be released at a later date). Example of this, users that predominantly use the mouse enters text in a textbox. User then clicks on the Cancel command button. If validation on the textbox fails (Cancel Property set to True in either the BeforeUpdate or Exit Events of the textbox) before the focus is sent to the command button, the command button never gets triggered, cause the focus never leaves the textbox. This may be an acceptable behavior for MOST command buttons, but THERE ARE THOSE FEW, when it's not acceptable. This particular issue took me 2 months to find and create a resolution to this issue. This particular issue also now requires me to add additional coding to all of my forms, be it via calling the centralized validation code with parameters/arguments.

So by the time that all of these different levels of frustrations and issue went through, all you have left is a small handful of developers that reached the last option choice and went with Option C.



Okay then, how best to use Access and Excel?

Access should be used for all of it's DB means, and maybe even a lot of the analytical stuff via VBA coding.

Excel is good for 2 things

If you need to create a prototype, you can do this very quickly in Excel unlike in Access, it may take a while to do. However, don't spend much time creating this prototype as it's only meant to give a feel for how it would work on a general level basis in Access.

Excel is very easy to create things like charts and other data analytical tools from the data that may be retrieved from Access.

Excel is good for SHORT-TERM solutions and small scale projects while Access is good for LONG-TERM solutions and large scale projects.