I'm a self trained vb6 programmer who uses DAO. Below is one particular typical bit of code which i could turn out:

Sub cmdMultiplier_Click()  'Button on form, user interface ' 
  dim Rec1 as recordset
  dim strSQL as string

  strSQL = "select * from tblCustomers where ID = " & CurrentCustomerID  'inline SQL '
  set rec1 = GlobalDataBase.openrecordset(strSQL)    ' Data access '

  if rec1.bof <> true or rec1.eof <> true then
    if rec1.fields("Category").value = 1 then
      PriceMultiplier = 0.9         ' Business Logic ' 
    else
      priceMultiplier = 1
    end if
 end if
End Sub

Please pretend the above may be the entire source code of the CRUD application. I understand this design isn't good, things are confused together. Ideally it will have three distinct layers, interface, business logic and data access. I sort-of get why this really is desirable but I'm not sure what it is done and that i suspect this is exactly why I do not fully get why this type of separation is nice. I believe I'd be considered a lot further in the future if a person could refactor the above mentioned absurdly trivial example into 3 tiers.

a trivial example, yes, but with the fundamental elements - they simply belong in 3 different classes (see below). The primary reason behind this is actually the "separation of concerns" principle, i.e. the GUI is just worried about GUI things, the Biz Logic layer is just worried about the company rules, and also the data-access layer is just worried about data representations. This enables each layer to become maintained individually and used again across programs:

'in Form class - button handler
Sub cmdMultiplier_Click()
    PriceMultiplier = ComputePriceMultiplier(CurrentCustomerId)
End Sub

'in Biz Logic class
Function ComputePriceMultiplier(custId as Integer) as Double
    Dim cust as Customer = GetCustomer(custId)
    if cust.Category = 1 then   'please ignore magic number, real code uses enums
        return 0.9
    end if
    return 1
End Function

'in Data Access Layer class
Function GetCustomer(custId as Integer) as Customer
    Dim cust as Customer = New Customer    'all fields/properties to default values
	Dim strSQL as String = "select * from tblCustomers where ID = " & custId
    set rec1 = GlobalDataBase.openrecordset(strSQL)    ' Data access '
    if rec1.bof <> true or rec1.eof <> true then
        cust.SetPropertiesFromRecord(rec1)
	end if
	return cust
End Function

[a 'real' application would cache the present customer, have constants or saved methods for that customer query, etc. overlooked for brevity]

Contrast this together with your original everything-in-the-button-handler example (that is appallingly common in VB code since it is very easy to get it done this way) - should you needed the cost-multiplier rule in another application, you'd need to copy, paste, and edit the code into that application's button-handler. Presently there could be two places to keep exactly the same business rule, and 2 places in which the same customer query was performed.

What's the reason for the button?

My steps could be:

  • extract the part being able to access the database. (warning: air code ahead)

function getCustomer(CurrentCustomerID as Lengthy)

strSQL = "choose * from tblCustomers where ID = " &lifier CurrentCustomerID set rec1 = GlobalDataBase.openrecordset(strSQL) result = 1

if rec1.recordcount >0 then getCustomer = rec1 else getCustomer = false endif finish function

  • compose the company logic function:

function getCustomerDiscount(customerID as Lengthy)

customer = getCustomer(customerID)

res = 1 if customer then if customer("category")=1) then res = .9 endif endif

getcustomerdiscount = res

finish function

  • then, alter the button:

Sub cmdMultiplier_Click() pricemultiplier = getcustomerdiscount(currentcustomerid) finish sub