Extra Logic Layer

[epicmove]

It's been a while since I have had to do any access programming and have recently been working on n-tier web applications with separate Data Access and Business Logic layers.

It led me to develop a more flexible structure for any insert/update/delete methods within Access. For example a method to add a Product may look something like this:

' Insert a new Product
Public Function InsertProduct( _
                ProductGroupID As Integer, _
                ProductName As String, _
                ProductRef As String) As Boolean
                
                                    
On Error GoTo Err_Function_InsertProduct

    Dim db As Database
    Dim qdf As QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Qry_InsertProduct")
    
    With qdf
    
        .Parameters("pProductGroupID") = ProductGroupID
        .Parameters("pProductName") = ProductName
        .Parameters("pProductRef") = ProductRef
            
    End With
    
    qdf.Execute
    
    qdf.Close
    
    Set qdf = Nothing
    Set db = Nothing

    InsertProduct = True
       
Err_Function_InsertProduct:
    If Err.Number <> 0 Then
        MsgBox Err.Description
        InsertProduct = False
    End If
End Function

This function will then return true or false depending on whether the record was inserted. Parameters are added in the Access query in a similar way to an SQL Stored Procedure and it means I can use this one query in many different places within my application (rather than hard coding my criteria to a control on a form).

This is the level of flexibility I want for retrieving or selecting data and prompts the question of how this could be achieved.

Lets say I have a query "Qry_GetProductDetailByProductID". A normal access thing to do would be to set the criteria of my ProductID field to the control where we will retrieve the value.

But if I want to use this query in multiple areas of my application, that means creating multiple queries. Whilst I could retrieve all the Product records and then apply a filter (passed as an OpenArgs), this is not as efficient as retrieving just the one record from the database.

So we have a couple of techniques that I can think of:

1) Use a recordset object and assign the recordset fields to your individual controls on your form e.g.:

' Get Products By Product ID
Public Function GetProductsByID(ProductID As Integer) As Recordset

    Dim db As Database
    Dim qdf As QueryDef
    Dim rs As Recordset
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Qry_GetProductsByProductID")
    
    qdf.Parameters("[pProductID]") = ProductID
    
    rs = qdf.OpenRecordset

'......  then loop through recordset fields

My problem with this method is that a) it can be quite long winded and b) it can be difficult to clean up your recordset objects if you are making it a public function.

2) My second method would be to use a similar query and have a method that extracts the SQL from the query definition and uses the VBA Replace function to replace your parameters with variables. You then use the new SQL string you have created as the RecordSource for some object (e.g. a form). I have done something similar below:

' Return an SQL Statement that will return Asset Details
' for a specified AssetID. Assign to the objects RowSource Property

Public Function GetAssetDetails(AssetID As Integer, DbObject As String) As String

Dim db As Database
Dim qdf As QueryDef

    Set db = CurrentDb
    Set qdf = CurrentDb.QueryDefs("Qry_GetAssetDetailsByAssetID")
    
    Dim strSQL As String
    
    strSQL = Replace(qdf.SQL, "[pAssetID]", AssetID)
    
    qdf.Close
    
    Set qdf = Nothing
    Set db = Nothing
    
    DoCmd.OpenForm DbObject, OpenArgs:=strSQL
    
End Function

This will create the custom SQL string I require and then open the form and pass the SQL string as the open args. On the Forms Open event, you can then use the following code:

Private Sub Form_Open(Cancel As Integer)

    Me.RecordSource = OpenArgs

End Sub

Both these methods work although I prefer the second described. My question is more one of curiosity as I am sure there are many members of this forum who program in more complex languages who have adapted n-tier style techniques for Data Access within MS Access.

Thanks,

Ben

 

[MarkK]

Cooler still is once you've encapsulated all that logic into a class module, you can then have that class module expose other classes that are automatically instantiated based on the data in the first class. So say you write a Customer class that has members like this...

cCustomer Class

private m_id as long
private m_address as cAddress

private property Get AddressID as long
[COLOR="Green"]  'this item is the AddressID for this customer from the customers table[/COLOR]
  AddressID = GetData("AddressID")
end property

public property Address as cAddress
  if m_address is nothing then
[COLOR="Green"]    'create and load this customers address here[/COLOR] 
    set m_address = new cAddress
    m_address.load AddressID
  end if
[COLOR="green"]  'return the object which encapsulates it's own data[/COLOR] 
  set Address = m_address
end property

public function Load(CustomerID as long) as cCustomer
  [COLOR="green"]'loads and optionally returns a cCustomer object[/COLOR]
  m_id = customerID
  set load = me
end function

private function GetData(field as string) as variant
[COLOR="Green"]  'returns a single field value from the customers table for the current object[/COLOR]
  GetData = dlookup(field, "tCustomer", "CustomerID = " & m_id)
end function

See how that cCustomer object exposes a cAddress object? Here's the cAddress class, or some of it...
cAddress Class

private m_id as long

public property Street as string
  Street = GetData("Street")
End Property

public property Get City as string
  City = GetData("City")
end property

public function Load(AddressID as long)
  m_id = addressid
  set Load = me
end function

private function GetData(field as string) as variant
  GetData = dlookup(field, "tAddress", "AddressID = " & m_id)
end function

So the Customer object exposes its Address property, so for all future coding, you can get the address of a customer using the cCustomer object only, and the Address property it automatically exposes. So say a user selects a customer name in a combo, which gives you the CustomerID, all you do is...

dim cst as new cCustomer
with cst
  .load me.cboCustomer
[COLOR="Green"]  'message box displays the street and city of the customer[/COLOR]
  msgbox .address.street & vbcrlf & .address.city
end with

And each of these classes needs an AddNew method, a Delete method, and so on, and the address object might expose a single property, say FullAddress that combines Street, City, Province, Postal Code, etc..., The cCustomer class might have an Orders collection, and so on. And your custom classes expose their members via intellisense so it makes it really easy in the future to drill down on objects that expose other objects.

So anytime I want new functionality for a customer, say a count of orders, I write it as a custom property of the cCustomer object, like...

public property Get OrderCount as integer
  OrderCount = dcount("*", "tOrder", "CustomerID = " & m_id)
end property

Put it in the customer class module, and it's available via intellisense when you instantiate the customer.

dim cst as new cCustomer
msgbox cst.load(me.cboCustomerID).OrderCount

I think this takes what you're talking about to the next level, where the data and the funtionality for the type are inseparable and the type exposes other types, which may expose other types, making your data extremely easy to retrieve and manipulate.

Cheers,