Results 1 to 4 of 4

Thread: Using Classes

  1. #1
    Join Date
    Mar 2009
    Location
    New York
    Posts
    7

    Question Unanswered: Using Classes

    Just wanted to know how many of you "gurus" use Classes in there projects. I write in house utilities as part of my job (usually in C# or C++) using Access as a back end (light weight).

    However, from time to time I write the code directly in Access and I like to use Classes when ever possible...I'm a little weird that way.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can create classes in Access. effectivledy each object (form, report etc) is a class in its own right

    but you can also create code moduels which are specifcally classes.

    the one real issue I have with Access is that classes are not treated as combineable reusable code as modern day .NET classes are so you need tobe really keen on tracking which Access applciatiosn use which code. its tricky to import classes from a single codebase and make sure the applications are usign the correct classes.

    that may change with future versions of Access but I've still not yet done much in A2007, and I don't think its moved on to a more OOP approach. its still more of a DLL or COm type of class that Access prefers

    It may be that you can use .net classes in 2007 or the 'next' fersion whenever or wherever that is
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I personally have my own set of modules (a lot of them from the Sybex books - others I created) which I then simply import from one mdb into the other. I keep them "generic" (ie. they don't reference specific forms) so that I can write the code in the forms to access the functions in those modules as I need to.
    Last edited by pkstormy; 05-29-09 at 14:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36
    I use few classes. For example I have one which works with memory and find it very usefull. When I start a given proces which loops I read the data only once from the database. Next time data comes from memory. This speeds the process from few percents to 250% sometimes. I called this class clsCache.

    The class is based on another class CHive which I found from Planet Source Code few years back.

    Thanks to clsCache I never use DLookUp like this DLookup("Field","Table","ID=" Me!ID)

    I use something like this:

    Private Function DBLookUp(ByRef FieldName As String, _
    ByRef TableName As String, _
    ByRef strWhere As String) As Variant

    ' Purpose: Database String Look Up
    Dim varValue As Variant

    Set conConnection = CurrentProject.Connection

    StrSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "] WHERE " & strWhere

    Set rst = New ADODB.Recordset
    rst.Open StrSQL, conConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rst.BOF Then
    rst.MoveFirst
    varValue = Nz(rst(0), "")
    Else
    varValue = Null
    End If

    DBLookUp = varValue

    rst.Close
    conConnection.Close
    Set rst = Nothing
    Set conConnection = Nothing

    End Function

    .... and there is another function that reads few fields:

    Private Function DBLookUps(ByRef Fields As String, _
    ByRef TableName As String, _
    ByRef strWhere As String) As Boolean
    ' Purpose: Get values for FieldName1, FieldName2, FieldName3, ... from Table: TableName
    On Error GoTo DBLookUps_Err

    ' Assume Failure
    DBLookUps = False

    intArgs = intCountWords(Fields): ReDim strgetOpenArg(intArgs)
    For idx = 1 To intArgs
    strgetOpenArg(idx) = getOpenArg(Fields, idx)
    Next idx

    ' Compile SQL string
    StrSQL = " SELECT [" & strgetOpenArg(1) & "]"
    If intArgs > 1 Then
    For idx = 2 To intArgs
    StrSQL = StrSQL & "," & "[" & strgetOpenArg(idx) & "]"
    Next idx
    End If
    StrSQL = StrSQL & " FROM [" & TableName & "]"
    StrSQL = StrSQL & " WHERE " & strWhere & ";"

    Set conConnection = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open StrSQL, conConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rst.BOF Then
    rst.MoveFirst
    mWords = CStr(Nz(rst(0), ""))
    For idx = 2 To intArgs
    mWords = mWords & "," & CStr(Nz(rst(idx - 1), ""))
    Next idx
    ' Success
    DBLookUps = True
    Else
    mWords = ""
    For idx = 2 To intArgs
    mWords = mWords & "," & ""
    Next idx
    End If

    WrapUp:
    rst.Close
    conConnection.Close

    Exit_DBLookUps:
    Set rst = Nothing
    Set conConnection = Nothing
    Exit Function

    DBLookUps_Err:
    If Not rst Is Nothing Then
    If rst.State = adStateOpen Then rst.Close
    End If
    If Not conConnection Is Nothing Then
    If conConnection.State = adStateOpen Then conConnection.Close
    End If
    MsgBox Err.Description
    Resume Exit_DBLookUps
    End Function


    ... and now we have another one which post the fields onto memory after reading from database:


    '--------------------------------------------------------------------------
    ' Project : clsCache
    ' Description : Returns string variable from cache or database.
    ' Calls : DBLookUp,CHive
    ' Accepts : Fields="MaterialName,AvgCost"
    ' TableName = "Materials"
    ' strWhere = "MaterialID = " & 22
    ' Returns : String value
    ' Written By : Dani Sapfirov
    ' Date Created : 20/04/2009 16:00:36 PM
    ' Rev. History :
    ' Comments :
    '--------------------------------------------------------------------------

    ' Get String data from memory or database
    Public Function ReadFields(ByRef Fields As String, _
    ByRef TableName As String, _
    ByRef strWhere As String) As Long
    On Error GoTo ReadFields_Err

    ' Assume failure
    ReadFields = 0
    mWords = ""

    ' Compile Key
    Dim vKey As String
    vKey = CStr(Fields) & ";" & CStr(TableName) & ";" & CStr(strWhere)

    ' Check if exists in memory
    If mDBLookUp.Exist(vKey) Then
    mCacheHits = mCacheHits + 1
    ' Get data
    mWords = mDBLookUp.Item(vKey)
    ReadFields = Cache.intCountWords(mWords)
    Exit Function
    End If

    ' Get From Database
    'If Len(vKey) < 128 Then
    mCacheMisses = mCacheMisses + 1
    ' Read from database
    If DBLookUps(Fields, TableName, strWhere) Then
    ' Add to collection for later use
    Call mDBLookUp.Add(mWords, vKey)
    ReadFields = Cache.intCountWords(mWords)
    Else
    'MsgBox "DBLookUps failed", vbCritical + vbOKOnly
    ReadFields = 0
    End If
    'Else
    ' MsgBox "ReadFields Assertion failed: vKey > 128", vbCritical + vbOKOnly
    ' ReadFields = 0
    'End If

    WrapUp:

    ReadFields_Exit:
    Exit Function

    ReadFields_Err:
    Call LogMsgError(Err.Number, Err.Description, ModuleName$, "ReadFields")
    Resume ReadFields_Exit
    End Function


    Next time when I need to lookup using the same search criteria the fields are taken from memory not from the database. An example code looks like this:


    ' Reset
    strRegNumber = "Noname": FuelConsumptionHighway = 0: FuelConsumptionCity = 0: FuelConsumptionHillyTerrain = 0
    FuelConsumptionMountainTerrain = 0: FuelConsumptionMiningTerrain = 0: IncreasedFuelConsumption = 0: sngOneCourse = 0
    DistanceNorm = 0: OilConsumption = 0: OilEconomySelection = 2: sngFuelTankCapacity = 0
    sngTow = 0: sngTowCargo = 0: TruckAmortNormKm = 0: Weight = 0

    ' Reading few fields from table using Cache
    intArgs = Cache.ReadFields("RegNumber,FuelConsumptionHighway ,FuelConsumptionCity,FuelConsumptionHillyTerrain," & _
    "FuelConsumptionMountainTerrain,FuelConsumptionMin ingTerrain,IncreasedFuelConsumption,OneCourse", _
    "Trucks techdata", _
    "ID = " & IRead.RegNumberID(rst(0), 1))

    ' Here we check for Failure/Success
    If CBool(intArgs) Then

    ' Cache Hit
    ReDim strgetOpenArg(intArgs)
    For idx = 1 To intArgs
    strgetOpenArg(idx) = getOpenArg(Cache.Words, idx)
    Next idx
    strRegNumber = strgetOpenArg(1)
    FuelConsumptionHighway = Format(CSng(strgetOpenArg(2)), "#0.00")
    FuelConsumptionCity = Format(CSng(strgetOpenArg(3)), "#0.00")
    FuelConsumptionHillyTerrain = Format(CSng(strgetOpenArg(4)), "#0.00")
    FuelConsumptionMountainTerrain = Format(CSng(strgetOpenArg(5)), "#0.00")
    FuelConsumptionMiningTerrain = Format(CSng(strgetOpenArg(6)), "#0.00")
    IncreasedFuelConsumption = CSng(strgetOpenArg(7))
    sngOneCourse = Format(CSng(strgetOpenArg(8)), "#0.00")

    End If

    I wrote all this because find it very good. If someone is interested in class for speeding up a loop process I can implement it so he/she can see the difference and how it is implemented.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •