Acctg DB: Fast Forward

It took quite a while to update these series. I was thinking ahead and was shopping for an import player to play along with our MS Office team members. Looking around for a robust Database container is not that easy. My criteria were: - it has to be easy to work with, given the member players we have, it has to cost us nada and it has to be able to work in a networked system like a small business.

I tried and tested several but I found out that MS SQL Server 2005 Express is free for download and I tried it with satisfactory result. So I decided to include this as the Shaq center of our MJ Excel-lead team.

It is download-able at: MS SQL Server 2005 Express

And here I will show you a technique I will name Alley-oop Slam (data alley-oop slam - that is or DAOS). It can be compared to a Michael Jordan alley-oop to Pippen for a slam dunk.

Let's take this sample Excel list:

Name the range, say "usrlist". Then save the file as List.xls.
Open a blank MS Access database and create an MS Access macro as follows:













Note the following parameters:
Action: TransferSpreadsheet
Transfer Type: Import
Table Name: tblUsrList (.. referring to the destination MS Access Table container)
File Name : C:\DBAcctg\List.xls (.. .. please adjust depending on the directory and filename from which the data is extracted from)
Range : usrlist (.. the range name of content area from the spreadsheet)

Now if you name the MS Access macro as "AutoExec", it will run whenever the MS Access Database is opened. Therefore whenever we are in the Excel spreadsheet we can execute the DAOS by a VBA code that opens the MS Access file.

You can test it from the Excel UI by clicking on the 'record' button of the Excel 2003 "Visual Basic" toolbar and then clicking the 'stop' button. Press 'Alt' and 'F11' (simultaneously) and you will be taken to the VBA Editor window where you may complete the VBA code as follows:

Sub Macro1()
Shell "MSACCESS.exe C:\DBAcctg\Database1.mdb", vbMinimizedFocus
End Sub

(* note - The "Database1.mdb" above must be adjusted accordingly if have you named differently the Access file and/or the folder.)

For this to be executed properly we have to create some adjustments.

1) Our Excel VBA code must clear the list when it is already posted.

2) It must make provision for possible changes in the number of rows to be posted by readjusting the definition of the range name "usrlist".

3) It must contain a code to open the MS Access Database so that the data pass is communicated. (The above code will do)

4) The VBA Excel code has to get a signal from MS Access before clearing the posted data from the spreadsheet list. The signal can be via a refresh of existing pivot table with count operation of the data contained in MS Access.

Some other fine tuning can be as follows:

5) Creation of another MS Access file that will be the real container of the data. The table to be used in "Database1.mdb" will be linked from the Ms Access data file container (or MS SQL Server 2005 Express - if so warranted in the future, especially for web-enabling the system).

6) Adding the "Quit" or "close" MS Access macro line so that the file is closed after execution, leaving us with the spreadsheet interface.

7) Renaming this data transfer macro to "AutoExec" and fine tuning the Excel data encoding interface (via validation, conditional formatting, protection, etc).

For those who are more conversant with Accounting (and not even VBA) rather than Database, don't worry we will proceed on your phase of learning. So the code above is enough for now. You may also manually run the MS Access macro to test if data pass is successful.

The point here is, we have the capability to use MS Excel as a very adept player to handle the ball and create the openings before data is passed to it's proper container. Excel will be good especially as the computational capability will be there in the front-end of the Accounting DB system. That means we don't need to open a calculator application when encoding data. We have more than enough on the UI (user interface) to do even such complicated automated data population like in a complicated mortgage schedule. I have watched certain Oracle programmer automate this system and I can say they are not able to do it as easily.

Anyway, we will shift back and we will employ as much PWORP (Programming WithOut Really Programming) in next installment of this series.

Subscribe and enjoy!


-------------
Creating MS Access 2003 macro:


Creating MS Access macro (version 2007 & 2010):




note:

My examples will be using MS Office 2003 for backward compatibility. The MS Access macro above is captured from MS Access 2007 (they are essentially the same). Going around in MS Office 2007/2010 will be different because of the 'ribbon' interface design. For example, the "Developer" tab in the ribbon contains the buttons formerly in the "Visual Basic" toolbar of 2003 (and earlier). The MS Access 2007 macro creation is shown when you select the "Create" tab of the ribbon. The "TransferSpreadsheet" macro action in 2007 has some adjustments.


ciao