LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@Q11Q

Access VBA 2つのテーブルのインデックス等が同じレコード同士のフィールドを比較し、違う値のフィールドをみつける

Last updated at Posted at 2022-06-19

1.条件

1-1.インデックス

インデックス、キーとか言われる。
アクセスではテーブルの列をフィールドという。このうち、基準となるフィールドをインデックス、キーという。メインになるものを主キー(Primary Key)という。
自働でつけてくれる主キーが有名。主キー（またはインデックス）は固有かつフィールドが長整数型で空白がない場合、テーブルデザインで設定することができる。（ただしVBAとSQLではテーブル作成時のみが可能。あとから変更することはVBAもSQLも困難。）

1-2.インデックスは比較するテーブル、比較対象となるテーブルとも必ず一意

一意の別名:ユニーク、固有
FindFirstは複数の場合は最初だが、一意の場合、特定の値のインデックスを検索できる。
キーを設定している場合、dbOpenTableだとFindfirstではなくSeekが使える。
口座番号をキーとする。dbOpentableのときはテーブルデザインからキーを設定する。
口座番号は固有（＝ユニーク、一意、重複を許さない）かつ空白はない。

1-3.比較元のテーブル

これを Source とか言う。比較元、基準となるテーブル。
比較元の対象にあるすべてのインデックスを比較しようとする。
しかし、次の比較対象テーブルにはキーが存在しない場合がある。
今回はそうしたキーを除外する。
そうしたものは不一致クエリで探すことができる。
テーブル名をT2022とする。

口座番号	金額	面積	注釈	注釈ST
2	¥4,500	12.45	平成5年度購入　金額は推定	短い改行2
3	¥5,000	6.223	3桁の数	
4	¥8,000	10000.2		
5	¥60,000	122	新規の	新規の２

CREATE TABLE T2022 (口座番号 Long, 金額 Currency, 面積 DOUBLE, 注釈 LongChar, 注釈ST Char);

1-4.比較対象のテーブル

これをDestination Dest といいます。比較先、比較対象のテーブル。
比較対象となるテーブルにあって、比較元にない値のインデックスは考慮されない。
そうしたものは1-3の不一致クエリをひっくり返した不一致クエリで発見が可能となる。
仮にテーブル名をT2021とする。

口座番号	金額	面積	注釈	注釈ST
1	¥25,000	1233.45	考慮されない	考慮されない
2	¥4,500	12.45	平成5年度購入　金額は推定	短い改行
3	¥5,000	6.223	3桁の数	
4	¥8,000	10000.2	この改行

口座番号5はT2021にはない。これは比較する必要がない。
口座番号1はT2022にはない。しかし比較元にないため無視される。
しかし、このようにあったりなかったりすることは通常。
このため不一致クエリは必ずひっくり返したものを作る必要がある。
また、フィールドを比較する前に、比較するフィールドがあるか確認する。

T2025として作ってIndexをつけます
アクションクエリAQ_CreateT2021として保存します。

AQ_CreateT2021
CREATE TABLE T2021 ([口座番号] Long, [金額] Money, [面積] DOUBLE, [注釈] MEMO, [注釈ST] Char);
AQ_ADDIdxT2021
CREATE INDEX Newindex ON T2021 ([口座番号]);

Sub AddNewIndexWithAdox()
' ADOX 系はコレクション（複数形のオブジェクト）以外はすべてNew句をつける
Dim Cat As New ADOX.Catalog
Dim cTbl As New ADOX.Table
Dim cIDX As New ADOX.Index
Dim cIDXs As ADOX.Indexes ' これはNewは不要
Dim CN As New ADODB.Connection
Set CN = CurrentProject.Connection ' CurrentDBと接続するにはADODB.Connectionを使う
Set Cat.ActiveConnection = CN
Set cTbl = Cat.Tables("T2021")
cIDX.Name = "Newindex"
cIDX.Columns.Append ("口座番号")
Set cIDXs = cTbl.Indexes
cIDXs.Append cIDX
 'Clean up
 Set Cat.ActiveConnection = Nothing
 Set cTbl = Nothing
 Set cIDX = Nothing
End Sub

Sub AddNewIndexWithDocmd()
On Error Resume Next
DoCmd.DeleteObject acTable, "T2021"
On Error GoTo 0
DoCmd.SetWarnings False
CurrentDb.QueryDefs("AQ_CreateT2025").Execute: DoEvents 'SQLはクエリに書いていてもVBAから実行できる。こちらの方がVBAの負担が減る。
DoCmd.RunSQL "CREATE INDEX Newindex ON T2021 ([口座番号]);" '動的な変数が入るならこちら。ただしDoCmd.RunSQLは32,768
DoCmd.SetWarnings True
End Sub

1-5.フィールド名とデータ型(DAO)

フィールド名 データ型

データ型定数

(DAO.DataType.Enum)
口座番号 4 dbLong
金額 5 dbCurrency
面積 7 dbDouble
注釈 12 dbMemo
注釈ST 10 dbText

1-6.そのほか

ファイル形式はaccdb
Access 2013以降を基本とする。

1-6-1 参照設定

参照設定は使っていないが、大体はこういう感じ。Scripting はすぐ使えるように必ず参照設定する。

Name GUID Fullapth
VBA {000204EF-0000-0000-C000-000000000046} C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
Access {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07} C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB
stdole {00020430-0000-0000-C000-000000000046} C:\Windows\System32\stdole2.tlb
DAO {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
ADODB {B691E011-1797-432E-907A-4D8C69339129} C:\Program Files\Common Files\System\ado\msado15.dll
ADOR {00000300-0000-0010-8000-00AA006D2EA4} C:\Program Files\Common Files\System\ado\msador15.dll
ADOX {00000600-0000-0010-8000-00AA006D2EA4} C:\Program Files\Common Files\System\ado\msadox.dll
Office {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52} C:\Program Files\Common Files\Microsoft
MsoEuro {76F6F3F5-9937-11D2-93BB-00105A994D2C} C:\Program Files\Common Files\Microsoft Shared\EURO\MSOEURO.DLL
Scripting {420B2830-E718-11CF-893D-00A0C9054228} C:\Windows\System32\scrrun.dll
Excel {00020813-0000-0000-C000-000000000046} C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE
Shell32 {50A7E9B0-70EF-11D1-B75A-00A0C90564FE} C:\Windows\SysWOW64\shell32.dll
VBScript_RegExp_55 {3F4DACA7-160D-11D2-A8E9-00104B365C9F} C:\Windows\System32\vbscript.dll\3
VBIDE {0002E157-0000-0000-C000-000000000046} C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
TTSEngineLib {EB2114C0-CB02-467A-AE4D-2ED171F05E6A} C:\Windows\System32\speech\engines\tts\MSTTSEngine.dll
SpeechLib {C866CA3A-32F7-11D2-9602-00C04F8EE628} C:\WINDOWS\System32\Speech\Common\sapi.dll
WIA {94A0E92D-43C0-494E-AC29-FD45948A5221} C:\WINDOWS\System32\wiaaut.dll
WinHttp {662901FC-6951-4854-9EB2-D9A2570F2B2E} C:\WINDOWS\system32\winhttpcom.dll
WbemScripting {565783C6-CB41-11D1-8B02-00600806D9B6} C:\Windows\System32\wbem\wbemdisp.TLB
Word {00020905-0000-0000-C000-000000000046} C:\Program Files\Microsoft Office\root\Office16\MSWORD.OLB
MSXML2 {F5078F18-C551-11D3-89B9-0000F81FE221} C:\Windows\System32\msxml6.dll
System_Drawing {D37E2A3E-8545-3A39-9F4F-31827C9124AB} C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Drawing.tlb
IWshRuntimeLibrary {F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} C:\Windows\System32\wshom.ocx
WSHControllerLibrary {563DC060-B09A-11D2-A24D-00104BD35090} C:\Windows\System32\wshcon.dll
JRO {AC3B8B4C-B6CA-11D1-9F31-00C04FC29D52} C:\Program Files (x86)\Common Files\System\ado\msjro.dll
MSForms {0D452EE1-E08F-101A-852E-02608C4D0BB4} C:\WINDOWS\system32\FM20.DLL

1-7.結果

この場合口座番号2 注釈ST列（フィールド）短い改行2 短い改行
という一か所だけが求める結果となる。
以下のコードを実行するとイミディエイトウィンドウに次のように表示される。

ソース（参照元）にあって参照先にない
5   60000   122 新規の  新規の２    

参照先にあってソース（参照元）にない
7   25000   1233.45 考慮されない    考慮されない    

2         フィールド名:=注釈ST    短い改行2   短い改行

2 フィールド名:=注釈ST 短い改行2 短い改行
この結果を出す。

2. コード

2-1.クエリを使う

Sub testComparefield1() ' For Access VBA
' 2つの同じテーブルの同じ口座番号のレコードを比較する
' 直接クエリを使う
Dim cDB As DAO.Database: Set cDB = CurrentDb
Dim Tdf As TableDef, Tdf1 As TableDef, Tdfs As TableDefs: Set Tdfs = cDB.TableDefs
Dim Q As QueryDef, Q1 As QueryDef, Qs As QueryDefs: Set Qs = cDB.QueryDefs
Dim fld As DAO.Field, fld1 As DAO.Field, flds As Fields, flds1 As Fields
Dim buf As String
Dim i As Long
Dim dRS As DAO.Recordset, dRS1 As DAO.Recordset
Dim RTN As Variant
'Dim fso As New Scripting.FileSystemObject, TS As TextStream, oFile As File, oFolder As Folder
' 今回は省略しているが、必ずここで今開いているテーブルとクエリを閉じる
Set Tdf = cDB.TableDefs("T2022")  ' Source ソース 比較する基準となるテーブル
Set Tdf1 = cDB.TableDefs("T2021")  ' Destination  比較する対象となるテーブル

' 不一致クエリ
Set dRS = cDB.OpenRecordset("SELECT T2022.* FROM T2022 LEFT JOIN t2021 ON T2022.[口座番号] = T2021.[口座番号] WHERE (((T2021.口座番号) Is Null)) Order By T2022.[口座番号];", dbOpenForwardOnly)
If dRS.EOF = False Then
buf = ""
buf = "ソース（参照元）にあって参照先にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
End If
dRS.Close
Debug.Print buf
'TS.WriteLine buf

Set dRS = cDB.OpenRecordset("SELECT T2021.* FROM T2021 LEFT JOIN T2022 ON T2021.[口座番号] = T2022.[口座番号] WHERE (((T2022.口座番号) Is Null)) Order By T2022.[口座番号];", dbOpenForwardOnly)
If dRS.EOF = False Then
buf = ""
buf = "参照先にあってソース（参照元）にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
End If
dRS.Close
Debug.Print buf
'TS.WriteLine buf
' 不一致クエリ終わり

Set dRS = Tdf.OpenRecordset(dbOpenForwardOnly)
Set dRS1 = Tdf.OpenRecordset(dbOpenSnapshot)
dRS1.Sort = "[口座番号] ASC"
Set dRS1 = dRS1.OpenRecordset
'TS = fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForWriting, True, TristateTrue)
Do Until dRS.EOF = True
  Set flds = dRS.Fields
  Set dRS1 = cDB.OpenRecordset("SELECT T2021.[口座番号], T2021.[金額], T2021.[面積], T2021.[注釈], T2021.[注釈ST] FROM T2021 WHERE (((T2021.[口座番号])=" & flds("口座番号").Value & ")) Orderby T2021.[口座番号];", dbOpenDynaset)
' テキスト型はシングルクォーテーションで囲む、日付は#記号
'
    If dRS1.RecordCount > 0 Then
    Set flds1 = dRS1.Fields
        For i = 0 To flds.Count - 1
          If dRS.Fields(i).Value <> dRS1.Fields(i).Value Then
            Debug.Print flds("口座番号").Value, "フィールド名:=" & flds(i).Name & vbTab; flds(i).Value & vbTab; flds1(i).Value
'            TS.WriteLine flds("口座番号").Value & vbTab & "フィールド名:=" & flds(i).Name & vbTab & flds(i).Value & vbTab & flds1(i).Value
          End If
        Next
'    Else
'    dRS1.Close '使ったレコードセットを閉じる
    End If
dRS.MoveNext ' Do dRS.EOF = True は必ずMoveNext をしないと終わらなくなる
Loop
dRS.Close
dRS1.Close
'TS.Close : Set TS = Nothing
End Sub

2-2 フィルターを使ってみる

Sub testComparefield2_2()
' For Access VBA
' 2つの同じテーブルの同じ口座番号のレコードを比較する
' Filterを使用してみた
Const temparyQuery = "Q_Temp"
Dim cDb As DAO.Database: Set cDb = CurrentDb
Dim Tdf As TableDef, Tdf1 As TableDef, Tdfs As TableDefs: Set Tdfs = cDb.TableDefs
Dim Q As QueryDef, Q1 As QueryDef, Qs As QueryDefs: Set Qs = cDb.QueryDefs
Dim fld As DAO.Field, fld1 As DAO.Field, flds As Fields, flds1 As Fields
Dim buf As String, buf1 As String
Dim i As Long

Dim dRS As DAO.Recordset, dRS1 As DAO.Recordset, rstFiltered As DAO.Recordset 'rstFiltered  を加える
'Dim fso As New Scripting.FileSystemObject, TS As TextStream, oFile As File, oFolder As Folder
'Dim aRS As ADODB.Recordset, aRS1 As ADODB.Recordset
' 今回は省略しているが、必ずここでテーブルとクエリをクローズする
Set Tdf = cDb.TableDefs("T2022")  ' Source ソース 比較する基準となるテーブル クエリでもよい
Set Tdf1 = cDb.TableDefs("T2021")  ' Destiny  比較する対象となるテーブル クエリでもよい

' 不一致クエリ
Set dRS = cDb.OpenRecordset("SELECT T2022.口座番号, T2022.金額, T2022.面積, T2022.注釈, T2022.注釈ST FROM T2022 LEFT JOIN t2021 ON T2022.[口座番号] = T2021.[口座番号] WHERE (((T2021.口座番号) Is Null)) Order By T2022.[口座番号];", dbOpenForwardOnly)
buf = ""
buf = "ソース（参照元）にあって参照先にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
dRS.Close
Debug.Print buf
'TS.WriteLine buf
Set dRS = cDb.OpenRecordset("SELECT T2021.口座番号, T2021.金額, T2021.面積, T2021.注釈, T2021.注釈ST FROM T2021 LEFT JOIN T2022 ON T2021.[口座番号] = T2022.[口座番号] WHERE (((T2022.口座番号) Is Null)) Order By T2022.[口座番号];", dbOpenForwardOnly)
buf = ""
buf = "参照先にあってソース（参照元）にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
dRS.Close
Debug.Print buf
'TS.WriteLine buf

' 不一致クエリ終わり
Set dRS = Tdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
Set dRS1 = Tdf1.OpenRecordset(dbOpenSnapshot, dbDenyWrite)
'TS = fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForWriting, True, TristateTrue)
Do Until dRS.EOF = True
Set flds = dRS.Fields
      'Retrieve the name of the first city in the selected rows
       dRS1.Filter = "[口座番号]=" & dRS!口座番号
       Set rstFiltered = dRS1.OpenRecordset(dbOpenForwardOnly, dbReadOnly) ' Filterをかけたdrs1をOpenRecordset
       'rstFiltered.Movelast ' レコードがないとエラー
       If rstFiltered.RecordCount > 0 Then
       Do While rstFiltered.EOF = False ' 数がわからないのでDo Loopに持ち込む
       Set flds1 = rstFiltered.Fields ' 比較対象 フィールドは rstFilteredのフィールド
        For i = 0 To flds.Count - 1
          If dRS.Fields(i).Value <> rstFiltered.Fields(i).Value Then ' 比較対象RecordsetはFilterをかけたrstFiltered
            Debug.Print flds("口座番号").Value, "フィールド名:=" & flds(i).Name & vbTab; flds(i).Value & vbTab; flds1(i).Value
'            TS.WriteLine flds("口座番号").Value & vbTab & "フィールド名:=" & flds(i).Name & vbTab & flds(i).Value & vbTab & flds1(i).Value
          End If
        Next
        rstFiltered.MoveNext ' Do dRS.EOF = True は必ずMoveNext 終わらなくなる
        Loop
        Set rstFiltered = Nothing
        End If
dRS.MoveNext ' Do dRS.EOF = True は必ずMoveNext 終わらなくなる
Loop
'On Error Resume Next
dRS.Close
dRS1.Close
'TS.Close
End Sub

最初はうまくいかなかったので、あとから追加することとなったものの、大量のデータを比較する場合、このように複数のアルゴリズムで確認すると、バグが見つかることがあるということが分かった。
フィルターが他と最も異なるのはプロパティであることと、フィルターをかけたあとに別のRecordsetとして開くこと。
Fieldもフィルターをかけたあとのフィールド'Set flds1 = rstFiltered.Fields'となっている。
NoMatchはFindかSeekにしか使えない。このためFilterではクエリと同じRecordCountで該当するレコードがあるかを判定する。RecordCountはOpenRecordSetの直後はアクセスしている数1かそもそもレコードが存在しない0となる。そして0の場合はMoveLastをかけるとエラーになる。
連続検索、単独検索の別をせずDo Loopで回す。このためフィルターをかける場合ForwardOnlyにすると、最初から検索するということになる。
単独、複数のレコードが対応するか関係なく、フィルターのほうがDo loopで回すので、比較的わかりやすいコードになる。
このフィルターは本当はこうした２つのテーブルでは使う必要がないかもしれない。
これは２つのテーブル以外（例えばコードテーブル）など、別のものを参照するときに使えると思われる。

2-3 Seekを使ってみる例

dbOpenTableで開くにはキーを設定する必要がある。しかも昇順で整理する。
口座番号にIndexを使い、dbOpentableで開くと、Seekが使える。
Seekのほうが早いのは最初からこういう仕掛けがしてあるから。

Sub testComparefield2_3()
' For Access VBA
' 2つの同じテーブルの同じ口座番号のレコードを比較する
' Seekを使ってみた
Const temparyQuery = "Q_Temp"
Dim cDb As dao.Database: Set cDb = CurrentDb
Dim Tdf As TableDef, Tdf1 As TableDef, Tdfs As TableDefs: Set Tdfs = cDb.TableDefs
Dim Q As QueryDef, Q1 As QueryDef, Qs As QueryDefs: Set Qs = cDb.QueryDefs
Dim fld As dao.Field, fld1 As dao.Field, flds As Fields, flds1 As Fields
Dim buf As String, buf1 As String
Dim var As Variant, var1 As Variant
Dim i As Long
Dim RTN As Variant
Dim dRS As dao.Recordset, dRS1 As dao.Recordset, varBM As Variant
'Dim fso As New Scripting.FileSystemObject, TS As TextStream, oFile As File, oFolder As Folder
'Dim aRS As ADODB.Recordset, aRS1 As ADODB.Recordset
' 今回は省略しているが、必ずここでテーブルとクエリをクローズする
Set Tdf = cDb.TableDefs("T2022")  ' Source ソース 比較する基準となるテーブル クエリでもよい
Set Tdf1 = cDb.TableDefs("T2021")  ' Destiny  比較する対象となるテーブル クエリでもよい

' 不一致クエリ
Set dRS = cDb.OpenRecordset("SELECT T2022.口座番号, T2022.金額, T2022.面積, T2022.注釈, T2022.注釈ST FROM T2022 LEFT JOIN t2021 ON T2022.[口座番号] = T2021.[口座番号] WHERE (((T2021.口座番号) Is Null)) Order By T2022.[口座番号];", dbOpenForwardOnly)
buf = ""
buf = "ソース（参照元）にあって参照先にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
dRS.Close
Debug.Print buf
'TS.WriteLine buf

Set dRS = cDb.OpenRecordset("SELECT T2021.口座番号, T2021.金額, T2021.面積, T2021.注釈, T2021.注釈ST FROM T2021 LEFT JOIN T2022 ON T2021.[口座番号] = T2022.[口座番号] WHERE (((T2022.口座番号) Is Null)) Order By T2022.[口座番号];", dbOpenForwardOnly)
buf = ""
buf = "参照先にあってソース（参照元）にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
dRS.Close
Debug.Print buf
'TS.WriteLine buf

' 不一致クエリ終わり


Set dRS = Tdf.OpenRecordset(dbOpenForwardOnly)
Set dRS1 = Tdf1.OpenRecordset(dbOpenTable) ' テーブル形式で開く
Set flds1 = dRS1.Fields
dRS1.Index = "口座番号2001" ' Indexを設定していても必ずここでカレントインデックスを指定する。また、今回は同じに設定しているが、インデックス名はフィールド名と違う設定ができる。また、テーブルに設定したフィールドと違うフィールドをキーにできる
varBM = dRS1.Bookmark ' NoMatchのとき、復帰できるようにここでカレントレコードをブックマークする
'TS = fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForWriting, True, TristateTrue)
Do Until dRS.EOF = True
Set flds = dRS.Fields
dRS1.Seek "=", flds("口座番号").Value
       If dRS1.NoMatch = False Then
        For i = 0 To flds.Count - 1
          If dRS.Fields(i).Value <> dRS1.Fields(i).Value Then
            Debug.Print flds("口座番号").Value, "フィールド名:=" & flds(i).Name & vbTab; flds(i).Value & vbTab; flds1(i).Value
'            TS.WriteLine flds("口座番号").Value & vbTab & "フィールド名:=" & flds(i).Name & vbTab & flds(i).Value & vbTab & flds1(i).Value
          End If
        Next
    Else
        dRS1.Bookmark = varBM
    End If
dRS.MoveNext ' Do dRS.EOF = True は必ずMoveNext 終わらなくなる
Loop
dRS.Close
dRS1.Close
'TS.Close
End Sub

Recordset オブジェクトの Index プロパティの設定で指定された現在のインデックスのフィールドに対応する 1 つ以上の値です。引数 key は最大 13 個まで使用できます。
Seek を使用する前に、Index プロパティでCurrentのインデックスを設定しておく必要があります。インデックスが一意でないキー フィールドを指している場合、Seek は抽出条件を満たす最初のレコードを返します。
比較が、等しい (=)、以上 (>=)、またはより大きい (>)である場合、Seek はインデックスの先頭から始まり、末尾に向かって検索を進めます。（つまりKeyは昇順に設定していたほうがよい）
引数 key1 のフィールドのデータ型は、現在のインデックスの対応するフィールドのデータ型と同じである必要があります。

Seek を使用するときは、カレント レコードが設定されている必要はありません。

Indexes コレクションを使用すると、既存のインデックスを列挙できます。
つまりテーブルに設定していたKeyが使える？

リンク テーブルはテーブル タイプの Recordset オブジェクトとして開くことができないため、 Seek メソッドはリンク テーブルに対して使用できません。しかし、 OpenDatabase メソッドを使用してインストール可能な ISAM (ODBC でない) データベースを直接開いた場合は、そのデータベース内のテーブルに対して Seek を使用できます。

dRS1.Seek "=", flds("口座番号").Value
等号などが先にきて、その値が後ろに来る。コンマが必要。

2-4 フィールドの比較にDLookUpを使う例

これも大量なら遅いと思われる

' For Access VBA
' 2つの同じテーブルの同じ口座番号のレコードを比較する
' DlookUpを使った例
Const temparyQuery = "Q_Temp"
Dim cDb As DAO.Database: Set cDb = CurrentDb
Dim Tdf As TableDef, Tdf1 As TableDef, Tdfs As TableDefs: Set Tdfs = cDb.TableDefs
Dim Q As QueryDef, Q1 As QueryDef, Qs As QueryDefs: Set Qs = cDb.QueryDefs
Dim fld As DAO.Field, fld1 As DAO.Field, flds As Fields, flds1 As Fields
Dim buf As String, buf1 As String
Dim var As Variant, var1 As Variant
Dim i As Long
Dim RTN As Variant
Dim dRS As DAO.Recordset, dRS1 As DAO.Recordset
Dim varX As Variant
'Dim fso As New Scripting.FileSystemObject, TS As TextStream, oFile As File, oFolder As Folder
'Dim aRS As ADODB.Recordset, aRS1 As ADODB.Recordset
' 今回は省略しているが、必ずここでテーブルとクエリをクローズする
Set Tdf = cDb.TableDefs("T2022")  ' Source ソース 比較する基準となるテーブル クエリでもよい
Set Tdf1 = cDb.TableDefs("T2021")  ' Destiny  比較する対象となるテーブル クエリでもよい

' 不一致クエリ
Set dRS = cDb.OpenRecordset("SELECT T2022.* FROM T2022 LEFT JOIN t2021 ON T2022.[口座番号] = T2021.[口座番号] WHERE (((T2021.口座番号) Is Null));", dbOpenDynaset)
buf = ""
buf = "ソース（参照元）にあって参照先にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
dRS.Close
Debug.Print buf
'TS.WriteLine buf

Set dRS = cDb.OpenRecordset("SELECT T2021.口座番号, T2021.金額, T2021.面積, T2021.注釈, T2021.注釈ST FROM T2021 LEFT JOIN T2022 ON T2021.[口座番号] = T2022.[口座番号] WHERE (((T2022.口座番号) Is Null));", dbOpenDynaset)
buf = ""
buf = "参照先にあってソース（参照元）にない" & vbCrLf
Do Until dRS.EOF = True
Set flds = dRS.Fields
  For i = 0 To flds.Count - 1
  buf = buf & flds(i).Value & vbTab
  Next
buf = buf & vbCrLf
dRS.MoveNext
Loop
dRS.Close
Debug.Print buf
'TS.WriteLine buf

' 不一致クエリ終わり


Set dRS = Tdf.OpenRecordset(dbOpenForwardOnly)
'TS = fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForWriting, True, TristateTrue)
Do Until dRS.EOF = True
Set flds = dRS.Fields
For i = 0 To flds.Count - 1
varX = DLookup(flds(i).Name, Tdf1.Name, "[口座番号]=" & dRS!口座番号 & "&") 'テキスト型　"[口座番号]=’" & dRS!口座番号 & "'"
If IsNull(varX) = False Then
If flds(i).Value <> varX Then
  Debug.Print flds("口座番号").Value, "フィールド名:=" & flds(i).Name & vbTab; flds(i).Value & vbTab; varX
End If
End If
Next
dRS.MoveNext
Loop
dRS.Close
'TS.Close
End Sub

DlookUp関数
基本的に1対1の関係の時しか使えない。
本音は主キー。遅いからSort、Selecctしたクエリを使うほうが良いとのこと。

複数のフィールドが criteria を満たす場合、DLookup 関数は最初に条件を満たしたものを返します。 DLookup 関数が返すフィールド値が一意となる条件を指定してください。 DLookup 関数が確実に一意の値を返すように、次の例の [EmployeeID] のように、条件に 主キー 値を使用することをお勧めします。
ヒント
DLookup 関数を使って外部テーブルのフィールドから抽出した値を表示することはできますが、両方のテーブルから値を抽出する必要があるフィールドを含むクエリを作成してから、そのクエリに基づいてフォームやレポートを作成する方が効率的な場合があります。
また、ルックアップ ウィザードを使って、外部テーブルの値を検索することもできます。

2-5 Find系を使ってみる例

Sub testComparefield2_5()
' For Access VBA
' 2つの同じテーブルの同じ口座番号のレコードを比較する
' Find MoveFirstを使ってみた
Const temparyQuery = "Q_Temp"
Dim cDb As dao.Database: Set cDb = CurrentDb
Dim Tdf As TableDef, Tdf1 As TableDef, Tdfs As TableDefs: Set Tdfs = cDb.TableDefs
Dim Q As QueryDef, Q1 As QueryDef, Qs As QueryDefs: Set Qs = cDb.QueryDefs
Dim fld As dao.Field, fld1 As dao.Field, flds As Fields, flds1 As Fields
Dim buf As String, buf1 As String
Dim var As Variant, var1 As Variant
Dim i As Long
Dim RTN As Variant
Dim dRS As dao.Recordset, dRS1 As dao.Recordset
'Dim fso As New Scripting.FileSystemObject, TS As TextStream, oFile As File, oFolder As Folder
'Dim aRS As ADODB.Recordset, aRS1 As ADODB.Recordset
' 今回は省略しているが、必ずここでテーブルとクエリをクローズする
Set Tdf = cDb.TableDefs("T2022")  ' Source ソース 比較する基準となるテーブル クエリでもよい
Set Tdf1 = cDb.TableDefs("T2021") ' Destiny  比較する対象となるテーブル クエリでもよい

Set dRS = Tdf.OpenRecordset(dbOpenForwardOnly)
Set dRS1 = Tdf1.OpenRecordset(dbOpenSnapshot)
Set flds1 = dRS1.Fields
'TS = fso.OpenTextFile(CurrentProject.Path & "\test.txt", ForWriting, True, TristateTrue)
Do Until dRS.EOF = True
Set flds = dRS.Fields　' レコードのフィールドコレクションをセット
dRS1.FindFirst "[口座番号]=" & flds("口座番号").Value ' フィールド名は角カッコで囲むほうが安全。イコールに空白を入れない。数値型は後ろにダブルクォーテーションをいれない。
       If dRS1.NoMatch = False Then ' この IF Nomatch False Else Do Loop の流れ
        For i = 0 To flds.Count - 1
          If dRS.Fields(i).Value <> dRS1.Fields(i).Value Then
            Debug.Print flds("口座番号").Value, "フィールド名:=" & flds(i).Name & vbTab; flds(i).Value & vbTab; flds1(i).Value
'           TS.WriteLine flds("口座番号").Value & vbTab & "フィールド名:=" & flds(i).Name & vbTab & flds(i).Value & vbTab & flds1(i).Value
          End If
        Next
    Else
       ' マッチしない場合はDo whileでマッチするところに戻す。
       ' MoveFirstではないのか？
        Do While Not dRS1.NoMatch
           dRS1.FindNext "[口座番号]=" & flds("口座番号").Value
        Loop
      ' 複数のレコードがある場合は以下を追加する
      '     dRS1.FindNext "[口座番号]=" & flds("口座番号").Value
    End If
dRS.MoveNext ' Do dRS.EOF = True は必ずMoveNext 終わらなくなる
Loop
dRS.Close
dRS1.Close
'TS.Close
End Sub

2-5-1 ポイント1 OpendRecordSet

dbOpenTabeleは使えない。

2-5-2 ポイント2 事前のKeyの設定

つまるところKeyとか設定して昇順で並べ替えて置くことが早いと言っている。

2-5-3 ポイント3 検索条件の書き方

dRS1.FindFirst "[口座番号]=" & flds("口座番号").Value '数値
dRS1.FindFirst "[口座番号]='" & flds("口座番号").Value & "'" 'テキスト
dRS1.FindFirst "HireDate > #" _ 
        & Format(mydate, 'm-d-yy' ) & "#"  '日付

データ型に合わせる。日本では小数点がアメリカと同じピリオドなので問題ないが、日付はアメリカ式に変更している。

2-5-4 NoMatchの使用 Elseからの戻し方

NoMatch プロパティの値を必ず確認して、Find 操作が成功したかどうか調べてください。検索が成功した場合、 NoMatch は False です。失敗した場合、 NoMatch は True で、カレント レコードは未定義となります。この場合は、カレント レコードを参照するポインターを有効なレコードに戻す必要があります。

2-5-5 Find の使い分け

Find メソッド 検索開始位置 検索方向
FindFirst レコードセットの先頭 レコードセットの末尾
FindLast レコードセットの末尾 レコードセットの先頭
FindNext 現在のレコード レコードセットの末尾
FindPrevious カレント現在のレコード レコードセットの先頭

通常はFindFirstのみ覚えておけば良いがNomatchがFalseになったときに何を使うか。

2-xメモ型（長いテキスト型）について

従来のメモ型と名前を変更しているが、単純な変更ではない。
Accessの文字列型とメモ型の違い～メモ型を利用する場合の注意点
確かに主キーは付けられない。しかし、Indexは付けられ、3000文字程度はIndexになるらしい。
また、2010までは最初の255文字で並べ替えていたと思われる。
テキスト型とメモ型 ACCESS Sugi Blog

メモ型フィールドは、255 文字を超えるデータを格納する場合に使用します。このフィールドには 65,536 文字までのデータを格納できます。書式が設定されたテキストや長い文書を格納する場合は、メモ型フィールドではなく OLE オブジェクト型フィールドを作成します。

テキスト型とメモ型のフィールドには、入力した文字だけが格納されます。通常以外の位置にあるスペースは格納されません。

テキスト型またはメモ型のフィールドでは、値を並べ替えたりグループ化したりできます。ただし、メモ型フィールドについては、最初の 255 文字だけを使用して、並べ替えまたはグループ化が行われます。

しかし2013以降の長いテキスト型は異なるかもしれない。この記述が見つからない。
今回は単純に比較しているが、基本的に長いテキスト型の比較は正確ではない可能性があると認識したほうがよい。

3.参考

ｖｂａにおいてセミコロン；はどういう意味ですか？ -OKWeb Debug.Printは,はタブ区切りセミコロン;は改行しない

DAOのRecordSetの開き方 - ひとりでAccess
:ping_pong: OpenSnapShot
【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する- いつも隣にITのお仕事
:ping_pong:Filterの使用例

3-2 以下公式

3-2-1 不一致クエリ

不一致クエリの解説公式

3-2-2 メモ型

長いテキスト フィールドを作成または削除する
最大 65,536 文字の英数字を格納
改行を含む場合はリッチテキストがよい。
Access でインデックスを含むメモ型フィールドに大量のテキストを入力するとエラー メッセージ "実行時エラー '3709'" が表示される

回避策
テキスト型フィールドで並べ替えを実行する場合、この問題は発生しません。
次のいずれかの方法で、メモ型フィールドをテキスト型フィールドに変更後、並べ替えを実行してください。

  • クエリの元となるテーブルのメモ型フィールドをテキスト型フィールドに変更する
  • クエリのメモ型フィールドの先頭に [''+ (単一引用符 2 つと加算記号）] を追加する

    • 注 : メモ型フィールドのフィールド名先頭に [''+] を含む場合、Access はそのフィールドをテキスト型フィールドであると判断します。
    たとえば、[社員] テーブルにある [プロフィール] メモ型フィールドで並べ替えを行う場合、デザイン ビューを開き、フィールド名を次のように変更します。
    フィールド : ''+[プロフィール]

    Access で 2 つのテーブルを結合し、メモ型フィールドでレコードの並べ替えを実行すると、明示的に設定した並べ替え順序が正しく反映されない場合がある Access2010?
    Database.OpenRecordset メソッド (DAO)

    Parameter
    Name 新しい Recordset のレコードの取得元です。テーブル名、クエリ名、またはレコードを返す SQL ステートメントを指定できます。Microsoft Office Access データベース エンジンのデータベースに含まれるテーブル タイプの Recordset オブジェクトの場合は、テーブル名でのみ指定できます。(別のファイルはテーブルのみということ）

    ...これは、連結の際に、数値はシステムのデフォルトの 10 進文字を使って文字列に変換され、SQL は米国内の 10 進文字しか受け付けないからです。
    :ping_pong:AccessのSQL内では日付(#MM/DD/YYYY#)、数字(これは日本と同じ100,000.4なので心配は少ない。）は米国型にしないとエラーになる。

    現在 "長いテキスト" に名前が変更されたメモ データ型

    デスクトップ データベース (.accdb) のテキスト フィールド
    長いテキスト
    .accdb ファイルでは、長いテキスト フィールドは古いメモフィールドと同じように機能します。 つまり、フォームやレポートのコントロールに表示できる文字は最初の 64,000 文字までですが、最大で 1 GB のテキストを格納できます。 [長いテキスト] フィールドを設定してリッチ テキストを表示できます。これには、太字や下線のような書式設定が含まれます。
    短いテキスト
    .accdb ファイルの場合、[短いテキスト] フィールドは以前のバージョンのテキスト フィールドと同じように機能します。 最大 255 文字を格納します。

    3-2-3 日付とクエリ、フィルター、Seek、Find

    Access クエリで抽出条件として日付を使用する例
    とこのように書いている。この日付リテラル #2021/12/31#も有効。（ただし入力したあと#12/31/2021#に自動的に変わる）
    これはVBA、VBSでは一般的な日付リテラルである。
    しかし、公式サイトではFilterとSeekについてはハイフン形式で書くか、Formatでハイフンにしている。通常であれば日付リテラルCDATEでも良さそうだが。この意図がわからない。これにさらに時間が加わればどうなるのかもよくわからない。ともあれ、FindとSeekはハイフンで日付に、それ以外は日付リテラルをつかってもよいと思われる。

    3-3 関係オブジェクトメソッド、プロパティ

    3-3-1

    OpenRecordsetメソッドについて

    魔界の仮面弁士 2003-10-31 04:56:34 No: 109606による解説
    ≪dbOpenTable:テーブルタイプ≫
    単一のテーブルへの参照を示します。現レコードのデータのみを保持します。
    <利点>
    ・インデックスが使用可能であり、検索時には、Findより高速なSeekメソッドを使うことができます。
    ・取得後に、Sortプロパティを使って並び替えを行う事ができます。
    ・ローカルテーブルに対する編集(AddNewなど)を行う場合には、このタイプが最速です。
    <欠点>
    ・Jetワークスペース専用です。ODBCDirectでは使用できません。
    ・JOIN操作されたSELECTクエリやユニオンクエリに対しては使用できません。

    Recordset.OpenRecordset メソッド (DAO)
    :ping_pong:レコードセットからさらにレコードセットが開ける。上記のDatabeseとRecordSetの違い。
    また、Nameでクエリが使えるのも共通。つまりクエリ自体がレコードセット。なのでQueryDef.OpenRecordset メソッド (DAO)が存在する。（インデックスをつけていればdbOpenTable、リンクテーブルなどはDynaset）

    Recordset.Clone メソッド (DAO)

    複数のカレント レコードを必要とする Recordset に操作を実行する場合は、Clone メソッドを使用します。この方法は、2 つ目の Recordset を開くより高速で効率的です。Clone メソッドを使用して Recordset を作成すると、最初はカレント レコードがありません。Recordset のクローンを使用する前にカレント レコードを指定するには、Bookmark プロパティを設定するか、または Move メソッドの 1 つ、Find メソッドの 1 つ、または Seek メソッドを使用する必要があります。
    元のオブジェクトまたは複製されたオブジェクトのいずれかに Close メソッドを使用しても、他方のオブジェクトに影響はありません。たとえば、元の Recordset オブジェクトに Close メソッドを使用しても、クローンは閉じません。

    Microsoft Access ワークスペースでテーブル タイプの Recordset オブジェクトのクローンを作成する場合、レコードセットの新しいコピーに Index プロパティの設定は複製されません。Index プロパティの設定は手動でコピーする必要があります。

    Recordset.FindFirst メソッド (DAO)
    Recordset.Filter プロパティ (DAO)
    Recordset.Index プロパティ (DAO)

    テーブル型の Recordset オブジェクト内の現在の Index オブジェクトの名前を示す値を設定または返します (Microsoft Access ワークスペースのみ)。
    注釈
    指定する Index オブジェクトは前もって定義しておく必要があります。存在していない Index オブジェクトに Index プロパティを設定するか、または Seek メソッドを使用するときに Index プロパティが設定されていない場合、トラップ可能なエラーが発生します。
    テーブル タイプの Recordset オブジェクトは、基になる TableDef オブジェクトにインデックスを定義した場合にのみ、レコードを必要な順序で取得できます。別の順序でレコードを並べ替えるには、SQL ステートメントで ORDER BY 句を使用して、ダイナセット タイプ、スナップショット タイプ、または前方スクロール タイプの Recordset オブジェクトを開きます。

    注意

    • テーブルにインデックスを作成するのは必須ではありません。大きなテーブルではインデックスを持たないと、特定のレコードへのアクセスまたは Recordset オブジェクトの作成に長い時間がかかる場合があります。一方、インデックスを多く作成しすぎると、すべてのインデックスが自動的に更新されるため、更新、追加、および削除を実行する速度が低下します。
    • インデックスを持たないテーブルから返されるレコードの順序は特定できません。
    • Index オブジェクト内にある各 Field オブジェクトの Attributes プロパティによってレコードの順序が決まり、その結果、そのインデックスに対して使用するアクセス方法が決まります。
    • 一意のインデックスを使用すると、最適な方法でレコードを検索できます。
    • インデックスを作成してもベース テーブルの物理的な順序は変更されません。インデックスによって変わるのは、特定のインデックスを選択するか、または Recordset オブジェクトを開いた場合に、テーブル タイプの Recordset オブジェクトでレコードにアクセスする方法のみです。

    Recordset.CopyQueryDef メソッド (DAO)

    注釈
    CopyQueryDef メソッドを使用すると、 Recordset の作成に使用された QueryDef の複製である新しい QueryDef を作成できます。
    QueryDef を使用してこの Recordset が作成されていない場合は、エラーが発生します。 CopyQueryDef メソッドを使用する前に、まず OpenRecordset メソッドを使用して Recordset を開く必要があります。
    このメソッドは、 Recordset オブジェクトを QueryDef から作成し、その Recordset を関数に渡し、その関数でレコードセットを変更するクエリなどを表す SQL を再作成する必要がある場合に便利です。

    Recordset.NoMatch プロパティ (DAO)
    Recordset.Seek メソッド (DAO)

    3-3-2 DoCmd

    DoCmd.RunSQL

    The maximum length of the SQLStatement argument is 32,768 characters (unlike the SQLStatement action argument in the Macro window, whose maximum length is 256 characters).

    3-3-3 定数

    RecordsetTypeEnum 列挙 (DAO)
    RecordsetOptionEnum 列挙 (DAO)
    LockTypeEnum 列挙 (DAO)
    DataTypeEnum 列挙 (DAO)
    :ping_pong: 各フィールドのデータ型

    3-3-4 SQL

    SELECT ステートメント (Microsoft Access SQL)
    CREATE TABLE ステートメント (Microsoft Access SQL)
    CREATE INDEX ステートメント (Microsoft Access SQL)

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?