You can find the Excel tips you're looking at Pro-business Excel VBA Programming by entering the keyword in the textbox below and clicking the "Google Search" button.

Sunday, May 4, 2008

Advantages And Disadvantages Of Microsoft Excel Database Application

Yes, it is possible to make Excel as a database and be able to be a database application just like Access. I am sure that you won't believe me when I say that anything that you can do with a regular programming language is also possible with Microsoft Excel.

But of course, there are advantages and disadvantages in using Microsoft Excel as database. What are the advantages?

  • First and foremost, if you have the Microsoft Office in your PC, you don't need anything else to be installed in your computer. You are good to go and can start with your programming straight away.
  • Second, if you are a beginner, you can easily learn the Excel VBA Programming because you are half familiar with the programming platform that you are going to use, which is Excel.
  • Third, you can start with small Excel database application and as the need grows especially on records that are getting huge, you can still use the same Excel application and upgrade your database platform from Excel to other high-end databases like Access, SQL Server, Oracle, MySQL, etc.
  • Fourth, Excel is capable of connecting directly to OLAP databases and can be integrated in Pivot Tables.
  • Fifth, You don't need to create a your own financial modules. Excel is rich in financial functions like Fixed Assets Depreciation, Amortization, etc.
  • Sixth, Excel is portable. You can send it to someone through email.
Excel database application also has its disadvantages. But these disadvantages are sometimes have some workaround that can be done to fix or sometimes there are little effect that you can just live with them. Here are the disadvantages:
  • When you develop your application into one file it can be huge and will make your program execution very slow. But as I told you, there are workarounds. Design your program to have compact codes to lessen the bloating of the file. Another way is to separate each application function into more than one files. For example, if you are going to design a purchasing program, you can separate each of them; the purchase requisition function, item-and-price comparison matrix function, vendor master list maintenance, item master list maintenance, purchase order function, etc. Each will have small file, therefore execution is faster.
  • When you separate functions to multiple files, Excel files are prone to get lost in your hard drive especially when you break your application to too many files. You should practice a good folders/sub-directories management.
  • Excel worksheet as a database has a limited number of rows or records that you can store. But when you reach the 65k lines limit, you can convert it easily to Access and the rest of the application is can still be used.
  • If you can send Excel via email, you can accidentally send your file to someone who should not have it or not authorized to have it. Solution? You can password protect your Excel file. You can do it in two ways. First, Excel is capable of password protection in many ways (I will also discuss that in the future). Second, you can create your own password protection by Macro/VBA programming.
So far there are only 4 disadvantages that I can think of. Of course, some of you has something to say about an Excel as a database application, either positive or negative. But one thing is for sure, if it is a negative one, there is always a solution and you can always do something to fix it.

In subsequent posts, I will give some examples on how to create Excel database application. Everything can not be touched in one writing so it can be a series of posts that you should follow. To be informed of subsequent posts, subscribe to this blog. It's for free and can be done in a few seconds. Just click any of the subscription method on the upper right part of this webpage or just click here.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


6 comments:

gsteward said...

I am very interested in learning how to password protect an excel file containing sensitive information or code.I hope you post on that soon. Love this blog by the way!

OK Pinoy said...

Stew, I will do that the soonest possible. :)

Thanks,

tommydraw123 said...

Joel;

How do you save a password for Database query. I have used the saved password function and said yes. But when I close and open Excel program I have to put in all the password touches to the database structure. I may have multiple times of the same password because it touches different parts of the database.

OK Pinoy said...

Hi Tommy,

I presume that you are using MSDASC.DataLinks object to create connectionstring to connect to your database (please see my post on that Building Database Connection String Programmatically). When you check the "Allow saving password" checkbox in the DataLink Properties window, the connectionstring it produces includes the password and if you want to save the password you need to save that connectionstring. The simple way to do that is to store the string into a cell in a new blank worksheet. But of course, you need to lock and hide that cell and put a separate password to the sheet on locking.

I hope this helps.

Regards,
Joel

Anonymous said...

Joel
I use excel as a database extensively...however there are couple of disadvantages
a) It is difficult(not impossible) to make an Excel DB relational
b) It is difficult to make it multi-user for Edit/Update.
For read there is no problem

Anonymous said...

Nice post and this enter helped me alot in my college assignement. Say thank you you for your information.