レコード数/最大値/最小値_取得 - へたれちっぷ。

レコード数/最大値/最小値 取得

[ADO]

Option Compare Database
Option Explicit
'==========================================================================================
'General Module
'==========================================================================================
'/ ConnectionString
Public Const CONNECTIONTEXT As String = "Provider=SQLOLEDB.1;" & _
                                       "Data Source=(Computer Name)\(Instance Name);" & _
                                       "Initial Catalog=(Dtabase Name);" & _
                                       "Intergrated Security=SSPI;" & _
                                       "USER ID=(User ID);" & _
                                       "Password=(Password);"
 

Public Function lngDCount(strFields As String, _
                          strTable As String, _
                          Optional strWhere As String = "") As Long
'概要: lngDCount:指定された条件のレコード件数を返します。
'引数: strField :"*"と指定
'      strTable :テーブル名を指定
'      strWhere :条件を指定
'返値: Long     :件数
'備考:
 
    '/ On Error は省略
 
    '/ 定義
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
    '/ ConnectionStringとRecordsetの指定
    cn.Open CONNECTIONTEXT
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly
    rs.ActiveConnection = cn
    rs.Source = "SELECT COUNT(*) FROM " & strTable
 
    '/ Where条件の存在
    If strWhere <> "" Then
        rs.Source = rs.Source & " WHERE " & strWhere
    End If
 
    '/ Recordsetを開く
    rs.Open
 
    '/ 値を返す
    lngDCount = CLng(rs.Fields(0))
 
    '/ RecordsetとConnectionを閉じる
    rs.Close
    cn.Close
 
    '/ 定義の開放
    Set rs = Nothing
    Set cn = Nothing
 
End Function
 

Public Function lngDMax(strFields As String, strTable As String, _
                                 Optional strWhere As String = "") As Long
'概要: lngDMax  :指定された条件の最大値を返します。
'引数: strField :フィールド名と指定
'      strTable :テーブル名を指定
'      strWhere :条件を指定
'返値: Long     :件数
'備考:
 
    '/ On Error は省略
 
    '/ 定義
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
    '/ ConnectionStringとRecordsetの指定
    cn.Open CONNECTIONTEXT
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly
    rs.ActiveConnection = cn
    rs.Source = "SELECT MAX([" & strFields & "]) FROM " & strTable
 
    '/ Where条件の存在
    If strWhere <> "" Then
        rs.Source = rs.Source & " WHERE " & strWhere
    End If
 
    '/ Recordsetを開く
    rs.Open
 
    '/ 値を返す
    lngDMax = CLng(rs.Fields(0))
 
    '/ RecordsetとConnectionを閉じる
    rs.Close
    cn.Close
 
    '/ 定義の開放
    Set rs = Nothing
    Set cn = Nothing
 
End Function
 

Public Function lngDMin(strFields As String, strTable As String, _
                                 Optional strWhere As String = "") As Long
'概要: lngDMin  :指定された条件の最小値を返します。
'引数: strField :フィールド名と指定
'      strTable :テーブル名を指定
'      strWhere :条件を指定
'返値: Long     :件数
'備考:
 
    '/ On Error は省略
 
    '/ 定義
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
    '/ ConnectionStringとRecordsetの指定
    cn.Open CONNECTIONTEXT
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly
    rs.ActiveConnection = cn
    rs.Source = "SELECT MIN([" & strFields & "]) FROM " & strTable
 
    '/ Where条件の存在
    If strWhere <> "" Then
        rs.Source = rs.Source & " WHERE " & strWhere
    End If
 
    '/ Recordsetを開く
    rs.Open
 
    '/ 値を返す
    lngDMin = CLng(rs.Fields(0))
 
    '/ RecordsetとConnectionを閉じる
    rs.Close
    cn.Close
 
    '/ 定義の開放
    Set rs = Nothing
    Set cn = Nothing
 
End Function
 

---

戻る

Edit