Visual Basic (VB and VBA)

Copyright 1999-2001 Christopher Greaves. All rights reserved. Home Page and email to [email protected]

If in doubt, record a macro and inspect the entrails!

Here is an INDEX to all the procedures.

You will probably need one copy of my GLOBAL DECLARATIONS.

Public Function lngGetTableRows(strPath As String, strFile As String, strTable As String, strKey As String, strKeyValue As String, strAr() As String) As Long
' Procedure :   lngGetTableRows
' Description:  Returns the rows that match a key in an Acess97 database table.
' Copyright:    Chris Greaves
' Inputs:       Database path, file and table name, field name and value, all as strings.
' Returns:      Rows of the table.
' Assumes:      None.
' Side Effects: None.
' Tested:       By the calls shown below.
    ReDim strAr(0)
    Dim wrk As Workspace
    Set wrk = wrkCreateWorkspace("", "admin", "")
    Dim dbs As Database
    Set dbs = dbsOpenDatabase(wrk, strPath, strFile) ' given path and file
    Dim rst As Recordset
    Set rst = rstOpenRecordset(dbs, "SELECT * from " & strTable) ' given table
    Dim lngRows As Long
    rst.MoveLast:     rst.MoveFirst
    lngRows = rst.RecordCount ' this many records/rows.
    Dim strArray
    strArray = rst.GetRows(lngRows)
    ' Now find the strKey in the field list, then examine that column for the key value
    Dim intFld As Integer
    For intFld = 0 To rst.Fields.Count - 1
        If rst.Fields(intFld).Name = strKey Then
            ' Now find the key value in that column
            For lngRows = 0 To rst.RecordCount - 1
                If strArray(intFld, lngRows) = strKeyValue Then
                    ReDim Preserve strAr(UBound(strAr) + 1)
                    strAr(UBound(strAr)) = strArray(0, lngRows) & strArray(1, lngRows) & strArray(2, lngRows)
                Else
                End If
            Next lngRows
        Else
        End If
    Next intFld
    Call CloseRecordset(rst)
    Call CloseDatabase(dbs)
    Call wrkCloseWorkspace(wrk)
    lngGetTableRows = UBound(strAr)
'Sub TESTlngGetTableRows()
'    Dim strar() As String
'    MsgBox lngGetTableRows(strTemplatePath(), "utilities.mdb", "Utilities", "Module", "DAOAccess", strar)
'    MsgBox lngGetTableRows(strTemplatePath(), "Utilities.mdb", "Utilities", "Module", "MathStats", strar)
'End Sub
End Function

We all knew nothing when we started …

Home Page and Contact Information Send email to [email protected]. This page was last updated Thursday, November 15, 2001

Visual Basic (VB and VBA)

Copyright 1999-2001 Christopher Greaves. All rights reserved. Home Page and email to [email protected]

If in doubt, record a macro and inspect the entrails!

Here is an INDEX to all the procedures.

You will probably need one copy of my GLOBAL DECLARATIONS.

Public Function lngGetTableRows(strPath As String, strFile As String, strTable As String, strKey As String, strKeyValue As String, strAr() As String) As Long
' Procedure :   lngGetTableRows
' Description:  Returns the rows that match a key in an Acess97 database table.
' Copyright:    Chris Greaves
' Inputs:       Database path, file and table name, field name and value, all as strings.
' Returns:      Rows of the table.
' Assumes:      None.
' Side Effects: None.
' Tested:       By the calls shown below.
    ReDim strAr(0)
    Dim wrk As Workspace
    Set wrk = wrkCreateWorkspace("", "admin", "")
    Dim dbs As Database
    Set dbs = dbsOpenDatabase(wrk, strPath, strFile) ' given path and file
    Dim rst As Recordset
    Set rst = rstOpenRecordset(dbs, "SELECT * from " & strTable) ' given table
    Dim lngRows As Long
    rst.MoveLast:     rst.MoveFirst
    lngRows = rst.RecordCount ' this many records/rows.
    Dim strArray
    strArray = rst.GetRows(lngRows)
    ' Now find the strKey in the field list, then examine that column for the key value
    Dim intFld As Integer
    For intFld = 0 To rst.Fields.Count - 1
        If rst.Fields(intFld).Name = strKey Then
            ' Now find the key value in that column
            For lngRows = 0 To rst.RecordCount - 1
                If strArray(intFld, lngRows) = strKeyValue Then
                    ReDim Preserve strAr(UBound(strAr) + 1)
                    strAr(UBound(strAr)) = strArray(0, lngRows) & strArray(1, lngRows) & strArray(2, lngRows)
                Else
                End If
            Next lngRows
        Else
        End If
    Next intFld
    Call CloseRecordset(rst)
    Call CloseDatabase(dbs)
    Call wrkCloseWorkspace(wrk)
    lngGetTableRows = UBound(strAr)
'Sub TESTlngGetTableRows()
'    Dim strar() As String
'    MsgBox lngGetTableRows(strTemplatePath(), "utilities.mdb", "Utilities", "Module", "DAOAccess", strar)
'    MsgBox lngGetTableRows(strTemplatePath(), "Utilities.mdb", "Utilities", "Module", "MathStats", strar)
'End Sub
End Function

We all knew nothing when we started …

Home Page and Contact Information Send email to [email protected]. This page was last updated Thursday, November 15, 2001