Results 1 to 4 of 4
  1. #1
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25

    Class Method

    I've used MS Access for over 20 years and want to develop an application using class method, like Customer class, Supplier class, PO class, Item class etc. I'm currently using MS Project VBA and would like to follow that OOD/OOP model. I believe I need to define application, collections, then objects. How do I convert from a relational table to an OO data structure and refer them in a form? Any quick examples? good books? I'm currently studying Access templates to find some clues. Thanks for your advice in advance.

    The development environment is MS Access 2007, Windows 7
    Last edited by AndreT; 01-17-2011 at 11:24 AM. Reason: Add info

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    I usely define a class module for each important data table, and then:
    define a property for all fields and needed calculated fields, define methods for all the things this class can do (there is always a LoadX) method.
    A simple example: the class UserInfo

    Code:
    Option Compare Database
    Option Explicit
    
        Dim lngUserID As Long
        Dim lngUserLevel As Long
        Dim strUserName As String
        Dim strUserLogin As String
        Dim strUserPassword As String
    
    Public Property Get UserID() As Variant
    On Error GoTo Err_GetUID
    
        UserID = lngUserID
        
    Exit_GetUID:
        Exit Property
        
    Err_GetUID:
        UserID = 0
    End Property
    
    Public Property Let UserID(ByVal vNewValue As Variant)
    On Error GoTo Err_LetUID
    
        lngUserID = Nz(vNewValue, 0)
        
    Exit_LetUID:
        Exit Property
        
    Err_LetUID:
        lngUserID = 0
    End Property
    
    Public Property Get UserLevel() As Variant
    On Error GoTo Err_Getlevel
    
        UserLevel = lngUserLevel
        
    Exit_Getlevel:
        Exit Property
        
    Err_Getlevel:
        UserLevel = 0
    End Property
    
    Public Property Let UserLevel(ByVal vNewValue As Variant)
    On Error GoTo Err_Letlevel
    
        lngUserLevel = Nz(vNewValue, 0)
        
    Exit_LetLevel:
        Exit Property
        
    Err_Letlevel:
        lngUserLevel = 0
    End Property
    
    Public Property Get UserName() As Variant
    On Error GoTo Err_GetName
    
        UserName = strUserName
        
    Exit_GetName:
        Exit Property
        
    Err_GetName:
        UserName = "?"
    End Property
    
    Public Property Let UserName(ByVal vNewValue As Variant)
    On Error GoTo Err_LetName
    
        strUserName = Nz(vNewValue, "?")
        
    Exit_LetName:
        Exit Property
        
    Err_LetName:
        strUserName = "?"
    End Property
    
    Public Property Get UserPassword() As Variant
    On Error GoTo Err_GetPW
    
        UserPassword = strUserPassword
        
    Exit_GetPW:
        Exit Property
        
    Err_GetPW:
        UserPassword = "ERROR"
    End Property
    
    Public Property Let UserPassword(ByVal vNewValue As Variant)
    On Error GoTo Err_LetPW
    
        strUserPassword = Nz(vNewValue, "ERROR")
        
    Exit_LetPW:
        Exit Property
        
    Err_LetPW:
        strUserPassword = "ERROR"
    End Property
    Public Property Get UserLogin() As Variant
    On Error GoTo Err_GetLogin
    
        UserLogin = strUserLogin
        
    Exit_GetLogin:
        Exit Property
        
    Err_GetLogin:
        UserLogin = "ERROR"
    End Property
    
    Public Property Let UserLogin(ByVal vNewValue As Variant)
    On Error GoTo Err_LetLogin
    
        strUserLogin = Nz(vNewValue, "ERROR")
        
    Exit_LetLogin:
        Exit Property
        
    Err_LetLogin:
        strUserLogin = "ERROR"
    End Property
    
    Public Sub LoadUser()
    On Error GoTo Err_LoadUser
    
        Dim rst As New ADODB.Recordset
        Dim rstUser As New ADODB.Recordset
        
        rst.Open "tsysCurrentUser", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
        With rst
            If .BOF And .EOF Then
                intError = cerrNoRecords
                Err.Raise 64000
            End If
            .MoveFirst
            Me.UserID = !cuUserID
            .Close
        End With
        Set rst = Nothing
        
        rstUser.Open "select * from tsysUsers where userID = " & Me.UserID, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
        With rstUser
            If .BOF And .EOF Then
                intError = cerrNoRecords
                Err.Raise 64000
            End If
            .MoveFirst
            Me.UserLevel = !UserLevel
            Me.UserLogin = !UserLogin
            Me.UserName = !UserName
            Me.UserPassword = !UserPassword
            .Close
        End With
        Set rstUser = Nothing
        
    
    Exit_LoadUser:
        Exit Sub
        
    Err_LoadUser:
        Call gsgErrorHandling
        Resume Exit_LoadUser
    
    End Sub
    You could for example add a method ChangeUserPasword to this class.

    To use the class in another module

    Code:
    dim objUser as New UserInfo
    
    objUser.LoadUser
    debug.Print objUser.UserName
    succes
    NG

  3. #3
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25

    Nice code, but ...

    Thanks NG for the code you share with me. I've noticed you instantiated one object from one record in the table. How can I create a collection with all the records converted to objects? Does that mean I need to bring the whole file into memory? I think I know how to create a single object from a single record based on user input. Anywhere I can find how to create an Application object? a collection object? from my MS Access database relational tables? I use MS Project VBA extensively, and would like to create an OOD/OOP similiar to that. Any advice on websites? books?

    Thanks for you advice.

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    most times you just want to do something record by record, but if you need to perform actions on whole recordsets at once, just add a method like myObject.LoadAll, and alter the SQL in that method so it gets all records it needs...

    gr
    NG

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Class in Access
    By Huddle in forum Access
    Replies: 2
    Last Post: 07-15-2010, 04:08 PM
  2. Base-34 class or alternetives
    By is49460 in forum Programming
    Replies: 3
    Last Post: 06-29-2010, 07:59 PM
  3. Storing asset class Correlations help!
    By tdham in forum Database Design
    Replies: 1
    Last Post: 04-27-2010, 08:33 AM
  4. object or class does not support the set of events
    By couch potato in forum Programming
    Replies: 2
    Last Post: 03-20-2010, 08:55 AM
  5. In over my head with a database class
    By fixittech in forum Database Design
    Replies: 3
    Last Post: 01-22-2010, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums