Acctg DB: Pros with Cons

All right. Automating MS Office is no laughing matter. For example, the files I made in the article "Fast Forward" of this series, works to post data from Excel to MS Access.

Oops, I forgot to tell, that to make a procedure (VBA code) callable from different sheet or button or worksheet, you have to transfer it to a module. Yes, it may confuse a layman, so I decided to employ a different approach. To adhere to PWRP (Programming Without Really Programming) principle, I decided to make the complications of these matters to myself. You may not know how to stop an invisible instance of Excel via code or such issues that spun my head when I first implemented this years ago. That is why I decided to make an Add-in which you can call within Excel to implement the system with "KISS" principle.

In addition, I do not want to antagonize my JPIA and accountant friends with issues like MS Excel and MS Access being so hackable, so I am incorporating some security in the add-in that may help bulletproof the system. But doing such things are tough, I accede. However, I am conducting my own tests, and the strategy I found is fairly good (so far ... security vs cracking/hacking involves many aspects).

In the meantime, going back to the series installment, here is how you may create a data capture design within the spreadsheet: Video (opens in new window) to the tune of "El Bimbo" by Eraserheads.

The said video illustrates the design of forms using the spreadsheet. There is another back-end design strategy that involves going to the VBA window (can be seen by pressing "Alt-F11"). But I prefer to expose the user to the spreadsheet for the following reasons:

1. The user being at ease with UI (user interface).
2. Capability to populate the data by formula or reference to an existing Excel data.
3. Capability for multi-row data (like the items listed in a Purchase Order).
4. Ability for inter-field data dependencies (even inter-row) and dependent calculations.
5. Images, textbox and other drawing features can be utilized.

Anyway, the video shown above is about the following:
- changing the 'backgound picture' of the spreadsheet
- using 'fill color' on selected cells or range of cells
- creating 'merge cells' for data entry
- formating the cells for the following effects:
*sunken effect - using "Cell", "Format", "Border": combining dark single lines for top and right borders together with light border lines on the right and bottom areas
*raised effect - using light border on top and left sides with dark double border on right and bottom ( the top and left sides can also use double border styles for this effect)
- combining the cell unprotect and the sheet protection (can be password-ed, select unlocked cells to restrict the user selection or focus to specified cells and hide formula)

- the 'data validation', 'cell concatenation', 'conditional formatting' and other formatting techniques can also be employed here (see previous portion of these series or look for Excel help, in case additional info is needed for these features)

In the meantime google "prcview" if you need to stop any process that may be running invisibly, in your course of experimenting with some automation.

Also, there is a free useful app "TaskPrompt" from skynergy.com that can be incorporated in any system development consistent with this series. However the accompanying 'mdb' file of said download is password-ed. So if you want to interact with it with another interface, you have to ask them.

Ciao and enjoy! And watch out for my Add-in coming soon.