Access VBA ノート(サンプルコード)

Hide Tamura の Access VBA ノート

Access-VBA [ADO] レコード検索

Sub test2()
    Dim CN As ADODB.Connection
    Dim RS As New ADODB.Recordset
    
    Set CN = CurrentProject.Connection
    
    RS.Open "tbl法人名簿", CN, adOpenStatic, adLockReadOnly
    
    Do
        RS.Find "住所1 Like '東京都*'"
        If Not RS.EOF Then
            Debug.Print RS("顧客名"), RS("住所1")
            RS.MoveNext
        Else
            Exit Do
        End If
    Loop
    
    RS.Clone: CN.Close
    Set RS = Nothing: Set CN = Nothing
    
End Sub

----------------------------------------------------
Null 値の検索

    Do
        RS.Find "住所1 = Null"
        If Not RS.EOF Then
            Debug.Print RS("顧客名")
            RS.MoveNext
        Else
            Exit Do
        End If
    Loop

----------------------------------------------------

カレントレコードは最初に見つかったレコード


ADO 2.5 API リファレンスより

Find (Criteria, SkipRows, SearchDirection, Start)

Criteria 
検索に使用する列の名前、比較演算子、および値を指定するステートメントを含む文字列型 (String) の値を指定します。 

SkipRows 
省略可能です。検索を開始する、カレント行または Start ブックマークからの行のオフセットを指定する長整数型 (Long) の値を指定します。既定値は 0 です。既定では、カレント行から検索が開始されます。 

SearchDirection 
省略可能です。検索方向に向かって、カレント行または使用可能な次の行のどちらから検索を開始するかを指定する SearchDirectionEnum 値を指定します。検索に成功しなかった場合、この値に adSearchForward が指定されていると、Recordset の終わりで停止します。また、検索に成功しなかった場合、この値に adSearchBackward が指定されていると、Recordset の先頭で停止します。 

Start 
省略可能です。検索の開始位置として使用するバリアント型 (Variant) のブックマークを指定します。 

 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ


 

Access-VBA [ADO] レコード削除

Option Compare Database
Option Explicit

Dim myADOcon As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset

Const Prv_Jet40 = "Provider=Microsoft.Jet.OLEDB.4.0;"

Sub Open_ADOdb()
    Set myADOcon = CurrentProject.Connection
End Sub

Sub Close_myRecSet()
    myRecSet.Close
    Set myRecSet = Nothing
End Sub

Sub Close_ADOdb()
    Set myADOcon = Nothing
End Sub

Sub レコードセット削除()
    Call Open_ADOdb
    Call RS_Delete
    Call Close_myRecSet
    Call Close_ADOdb
End Sub

Sub RS_Delete()
    Dim mySQL As String
    
    mySQL = "SELECT * FROM tbl個人顧客郵送先 WHERE 個人ID=5;"
    
    On Error GoTo ErrorCheck
    
    'トランザクション開始
    myADOcon.BeginTrans
    
    myRecSet.Open mySQL, myADOcon, adOpenKeyset, adLockOptimistic
        
    myRecSet.Delete

    myADOcon.CommitTrans

    Exit Sub

ErrorCheck:
    
    myADOcon.RollbackTrans
    MsgBox Err.Description
    
End Sub

-------------------------------------------

Sub 全レコードセット削除()
    Call Open_ADOdb
    Call RS_AllDelete
    Call Close_myRecSet
    Call Close_ADOdb
End Sub

Sub RS_AllDelete()
    Dim mySQL As String
    
    mySQL = "SELECT * FROM tbl個人顧客郵送先;"
    
    On Error GoTo ErrorCheck
    
    'トランザクション開始
    myADOcon.BeginTrans
    
    myRecSet.Open mySQL, myADOcon, adOpenKeyset, adLockOptimistic
    
    Do Until myRecSet.EOF
        myRecSet.Delete
        myRecSet.MoveNext
    Loop

    myADOcon.CommitTrans

    Exit Sub

ErrorCheck:
    
    myADOcon.RollbackTrans
    MsgBox Err.Description
    
End Sub 
 
-------------------------------------------
カレントレコードは移動せず、そのまま


 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ



Access-VBA [ADO] レコード追加

    Dim ArrField As Variant
    Dim ArrValue As Variant
    
    ArrField = Array("科目コード", "科目名")
    ArrValue = Array("1122", "売掛金")
    
    RS.AddNew ArrField, ArrValue

-----------------------------------------------------------    
    
    RS.AddNew
    RS("科目コード").Value = "1122"
    RS("科目名").Value = "売掛金"
    RS.Update

-----------------------------------------------------------    

カレントレコードは追加したレコード


 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ



Access-VBA [ADO] レコード更新

    RS.Update "科目コード", "1112"
    RS.Update "科目名", "当座預金"

---------------------------------------------------------------

    RS("科目コード").Value = "1112"
    RS("科目名").Value = "普通預金"
    RS.Update

---------------------------------------------------------------

RS("科目名").Value は、
RS![科目名].velue
RS.Fields("科目名").Value 、
RS("科目名")
RS(インデックス番号) でも大丈夫
インデックス番号は0から

カレントレコードは移動せず、そのまま

レコードセットのLockTypeプロパティは、既定のadLockReadOnlyでは、更新できずエラーになる

----------------------------------------------------------------

フリガナで並べ替えて、顧客IDに1番から番号を付け直す

Option Compare Database
Option Explicit

Dim myADOcon As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset

Const Prv_Jet40 = "Provider=Microsoft.Jet.OLEDB.4.0;"

Sub Open_ADOdb()
    Set myADOcon = CurrentProject.Connection
End Sub

Sub Close_myRecSet()
    myRecSet.Close
    Set myRecSet = Nothing
End Sub

Sub Close_ADOdb()
    Set myADOcon = Nothing
End Sub

Sub 個人確定申告管理_顧客ID付け直し()
    '顧客IDを1番から付け直す
    Call Open_ADOdb
    Call K_UPdate
    Call Close_myRecSet
    Call Close_ADOdb
End Sub

Sub K_UPdate()
    Dim i As Long
    Dim mySQL As String
    
    mySQL = "SELECT * FROM tbl個人確定申告管理 ORDER BY フリガナ;"
    
    On Error GoTo ErrorCheck
    
    myADOcon.BeginTrans
    
    myRecSet.Open mySQL, myADOcon, adOpenKeyset, adLockOptimistic
    
    i = 1
    
    Do Until myRecSet.EOF
    
        myRecSet(1).Value = i
        i = i + 1

        myRecSet.Update
        myRecSet.MoveNext
    
    Loop

    myADOcon.CommitTrans

    Exit Sub

ErrorCheck:
    
    myADOcon.RollbackTrans
    MsgBox Err.Description
    
End Sub


 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ



Access-VBA [ADO] カレントレコードの移動

使用例

RS.MoveFirst

Do Until RS.EOF
    Debug.Print RS.Fields(0), RS.Fields(1), RS.Fields(2)
    RS.MoveNext
Loop


MoveFirst 先頭へ

MoveLast 最後へ

MoveNext 次へ

MovePrevious 前へ

Move番号 指定した数を次へ


レコードセットにレコードがない時 BOF=True EOF=True

CursorTypeが既定の adOpenForwardOnly の時に、前方以外に移動するとエラー

BOF=True の時に MovePrevious するとエラー
EOF=True の時に MoveNext するとエラー


Do
    Debug.Print RS.Fields(0), RS.Fields(1), RS.Fields(2)
    RS.MoveNext
Loop Until RS.EOF ←後ろでやるとエラーの原因


 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ



Access-VBA [ADO] レコードセットオープンの引数 RS.Open

Source
 テーブル、クエリ、SQL

ActiveConnection

CursorType
 adOpenForwardOnly (既定)前方スクロールカーソル
 adOpenKeyset キーセットカーソル
 adOpenDynamic 動的カーソル
 adOpenStatic 静的カーソル

LockType
 adLockReadOnly (既定)読み取り専用
 adLockPessimistic レコード単位の排他的ロック
 adLockOptimistic レコード単位の共有的ロック
 adLockBatchOptimistic 共有的バッチ更新
 adLockUnspecified ロックタイプを指定しない

----------------------------------------------------------
Sub Test21()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    
    RS.Source = "tbl商品"
    RS.ActiveConnection = CN
    RS.CursorType = adOpenKeyset
    RS.LockType = adLockOptimistic
    RS.Open
    
    MsgBox RS.Fields.Count
    
    RS.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ



Access-VBA [ADO] レコードセットの取得 CN.Execute RS.Open

Sub Test1()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set CN = CurrentProject.Connection
    Set RS = CN.Execute("tbl名簿")
    
    
    
    RS.Close
    Set RS = Nothing: Set CN = Nothing
End Sub


Executeでは、テーブル、クエリ、SQLを指定できる

Executeでレコードセットを取得した場合は、
CursorType は adOpenForwardOnly(既定)
LockType は adLockReadOnly 読み取り専用(既定) になる。

--------------------------------------------------------

Sub test2()
    Dim CN As ADODB.Connection
    Dim RS As New ADODB.Recordset
'    -----------------
'    Dim RS As ADODB.Recordset
'    Set RS = New ADODB.Recordset
'    -----------------
    
    Set CN = CurrentProject.Connection
    
    With RS
        .Source = "tbl名簿"
        .ActiveConnection = CN
        .CursorType = adOpenStatic '静的カーソル
        .Open
    End With
    

    
    RS.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

RecordsetオブジェクトのOpenメソッドの引数


 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ



Access-VBA [ADO] データベース接続 CurrentProject.Connection CN.Open strProvider

Access2003以前では参照設定は必要ない。
Access2007 では、Microsoft ActiveX Data Objects X.X Library への参照設定が必要

下記、プログラム中のプロバイダー部分も、Prv_Jet40 から Prv_ACE120 に変更し、次のように定数を宣言
Public Const Prv_ACE120 As String = "Provider=Microsoft.ACE.OLEDB.12.0;"

-------------------------------------------------------------

Public Const Prv_Jet40 As String = "Provider=Microsoft.Jet.OLEDB.4.0;"

Sub Test1()
    Dim strDataSource As String
    Dim strProvider As String
    Dim strPath As String
    Dim strDB As String
    
    Dim cn As New ADODB.Connection
'    -------------------
'    Dim cn As ADODB.Connection
'    Set cn = New ADODB.Connection
'    -------------------
    
    strPath = CurrentProject.Path
    ChDrive strPath
    ChDir strPath
    
    strDB = "test.mdb"
    
    strDataSource = "Data Source=" & strDB
    strProvider = Prv_Jet40 & strDataSource

    With CN
        .ConnectionString = strProvider
        .Open
    End With
'    -------------------
'    CN.Open  strProvider
'    -------------------    
    
    
    CN.Close
    Set CN = Nothing
End Sub

-------------------------------------------------------------

カレントデータベースの場合

Sub Test2()
    Dim CN As ADODB.Connection
    Set CN = CurrentProject.Connection
        
    
    Set CN = Nothing
End Sub

 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ




Access-VBA [ADO] データベースへの接続状態 CN.State adStateOpen adStateClosed

ConnectionオブジェクトのStateプロパティ
CN.State

接続状態  接続中:adStateOpen  未接続:adStateClosed

Sub test()
    Dim CN As ADODB.Connection
    
    Set CN = CurrentProject.Connection
    
    If CN.State = adStateOpen Then MsgBox "Open"
    If CN.State = adStateClosed Then MsgBox "Closed"
    
    CN.Close

    If CN.State = adStateOpen Then MsgBox "Open"
    If CN.State = adStateClosed Then MsgBox "Closed"

    Set CN = Nothing

End Sub

 ↓ facebook のフォローで応援してください。お願いします。



Microsoft Office ブログランキングへ





記事検索
Access VBA 担当 Hide Tamura
■VBA Expert
VBA Expert Standard Crown
ExcelVBA Standard
Access VBA Standard
Excel2002 VBA Standard
■MICROSOFT OFFICE USER SPECIALIST
Microsoft Excel version2002 Expert
Microsoft Excel version2002


最新記事
Excelで作る経営計画
Excelで利益計画を立てましょう!会社にいくらの利益が必要で、その為の売上高は?

Excelで作る経営計画
Excel 関数 ノート
Excel関数を中心に、Excelの便利機能や、意外としらない使い方など書いていきます。

Excel 関数 ノート
Excel VBAノート
Hide Tamura の個人的なVBAノートです。

頻繁に使うVBAコードなどを記録しコピペして使う為に作りました。お役に立てるようでしたら、お使いください。

アクセスカウンター

    • ライブドアブログ