Considering a simple app to calculate the number of payments it would take to payoff a credit card, how might I architect this in Access to optimize for separation of database, user interface, and business logic layers?

Let’s consider a simple app with a table like this:

Accounts
Account_ID Long,
AccountName VarChar(255),
CurrentBalance Currency,
MinimumPayment Currency,
InterestRate Single

This is where we will persist data and potentially allow the user to enter multiple accounts.

Now let’s say we have a form for just one account at a time with the following fields:

frmAccount
Account_ID TextBox (Hidden),
txtAccountName TextBox,
txtCurrentBalance TextBox,
txtMinimumPayment TextBox,
txtInterestRate TextBox
txtMonthsToPayoff TextBox

We want the txtMonthsToPayoff TextBox to calculate based on the entered data of the Balance, Minimum Payment, and Interest Rate. This is one element of business logic.

We also don’t want the user to be able to save a record that does not have txtAccountName, txtCurrentBalance, txtMinimumPayment, and txtInterestRate Filled in.

In terms of database persistence, the form could be bound to the table. Or we could leave the form unbound and create our own persistence layer to perform CRUD functions on records (CReate, Update, Delete).

As far as business logic, I’m only seeing two functions here so far:

1. Validate that the data is correct and can be saved
2. Calculate the number of months it will take to pay the balance to 0 based on the payment.

For those functions we could either create a function to directly interact with the form controls, OR we could abstract the Account Information into an class object like this:

clsAccount
property AccountName String,
property CurrentBalance Double,
property MinimumPayment Double,
property InterestRate Double,
method IsAccountValid Boolean,
method CalculateMonthsToPayoff Long

This could allow us to use the form, but we wouldn’t have to. We could create a command line utility to do this or we could use a Web Browser control to create our own user form for persistence and to make it all formatted in beautiful ways.

This structure would actually separate out not only the data persistence, but the user interface as well from the application logic. We could port the business logic to Excel and put the elements of clsAccount into cells on a spreadsheet.

This is panning out to be an interesting little exercise for me. What do you think so far?