読者です 読者をやめる 読者になる 読者になる

bekkou68の日記

開発しているサービス, IT技術, 英語など。

ADO を用い、テーブルの列数を取得する - Get Number of Columns of Table by ADO

Access VBA

実装 - Implementation

Option Compare Database
Option Explicit

Dim mObjConnection As Object
Dim mObjRecordset As Object

' ===== Outline
' Previous process to use ADO.
'
' ===== Author
' bekkou68
Private Sub BeforeADOUse()
    Set mObjConnection = CreateObject("ADODB.Connection")
    Set mObjRecordset = CreateObject("ADODB.Recordset")
    Set mObjConnection = CurrentProject.Connection
End Sub

' ===== Outline
' Following process to use ADO.
'
' ===== Author
' bekkou68
Private Sub AfterADOUse()
    mObjRecordset.Close
    mObjConnection.Close
    Set mObjConnection = Nothing
    Set mObjRecordset = Nothing
End Sub

' ===== Outline
' Count number of columns of table.
'
' ===== Argument
' strTableName: Table name
'
' ===== Return Value
' Number of columns of table.
'
' ===== Author
' bekkou68
Public Function CountTableColumnNum(ByVal strTableName As String) As Integer
    Call BeforeADOUse
    mObjRecordset.Open strTableName, mObjConnection, adOpenKeyset, adLockOptimistic
    CountTableColumnNum = mObjRecordset.Fields.Count
    Call AfterADOUse
End Function

テスト - Test

Option Compare Database
Option Explicit

Dim mObjConnection As Object
Dim mObjRecordset As Object

' ===== Outline
' Previous process to use ADO.
'
' ===== Author
' bekkou68
Private Sub BeforeADOUse()
    Set mObjConnection = CreateObject("ADODB.Connection")
    Set mObjRecordset = CreateObject("ADODB.Recordset")
    Set mObjConnection = CurrentProject.Connection
End Sub

' ===== Outline
' Following process to use ADO.
'
' ===== Author
' bekkou68
Private Sub AfterADOUse()
    mObjRecordset.Close
    mObjConnection.Close
    Set mObjConnection = Nothing
    Set mObjRecordset = Nothing
End Sub

' ===== Outline
' Count number of columns of table.
'
' ===== Argument
' strTableName: Table name
'
' ===== Return Value
' Number of columns of table.
'
' ===== Author
' bekkou68
Public Function CountTableColumnNum(ByVal strTableName As String) As Integer
    Call BeforeADOUse
    mObjRecordset.Open strTableName, mObjConnection, adOpenKeyset, adLockOptimistic
    CountTableColumnNum = mObjRecordset.Fields.Count
    Call AfterADOUse
End Function

' ===== Outline
' Test for CountTableColumnNum.
'
' ===== Author
' bekkou68
Public Sub TestCountTableColumnNum()
    Debug.Print CountTableColumnNum("住所録テーブル")
End Sub


以下、イミディエイトウィンドウの出力。
The following is output of immediate window.


もし、住所録テーブルの列数が 9 ならば……。
If number of columns of "住所録テーブル" (Address table) is 9 ...

9


うん、おk。
Now, good.


−ω−