RE: [Excel ADO]未登録データの抽出法

質問

以前質問した内容↓で、一旦解決したかに思えたのですが
http://okwave.jp/qa/q9185441.html

Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties='Excel 8.0;HDR=YES'"
CN.CursorLocation = adUseClient
Sql = "SELECT * FROM T_訪問者 WHERE NOT EXISTS " & _
"( SELECT * FROM T_名簿 WHERE T_名簿.登録者名 = T_訪問者.訪問者名 )"

Set RS = CN.Execute(Sql)
Debug.Print RS.RecordCount

以上のようなコード（疑似です）で、
未登録データがないにもかかわらず、RS.RecordCountが1を返す場合があります

Sql = "SELECT COUNT(*) FROM T_訪問者 WHERE NOT EXISTS " & _
"( SELECT * FROM T_名簿 WHERE T_名簿.登録者名 = T_訪問者.訪問者名 )"

として、件数を出した場合も同様です。
RS.fields(0)などはNULLのままで何も入っていません

未登録データがある場合の抽出は全く問題ないのですが、
抽出数が0（登録済みデータだけ）なら通常処理、といきたいのに
うまくいかず困っています。

何が悪いのでしょうか。

回答No.5

piroin654 · ベストアンサー

以下で問題があるようならば、もう一度確認してみます。

ADOを使用してExcelのデータを操作する場合は
いろいろ制約がありますね。
Accessならばほとんど問題はないのですが。
一応、補足の

T_登録者名
No,登録者名
1,佐藤
2,鈴木

T_訪問者
No,日付,摘要,訪問者名
1,0601,訪問者,鈴木
1,0601,利用料,300

の場合に動く方法は以下のようです。
すべてを確認していないのでその点はご容赦を。
なお、レジストリをいじくる方法もあるかもしれませんが、
確認はしていません。
DAOは何の制約も無くデータを取り出すようです。

(1) ADOでExcuteを使用する場合。
IMEX=1をExtended Propertiesに追加。

Sub test1()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset

strSQL = "SELECT * FROM [T_訪問者$] WHERE (NOT EXISTS " & _
"( SELECT * FROM [T_名簿$] WHERE [T_名簿$].登録者名 = [T_訪問者$].訪問者名) " & _
" AND [T_訪問者$].摘要='訪問者');"

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
cn.CursorLocation = adUseClient
Set rs = cn.Execute(strSQL)

MsgBox rs.RecordCount

rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

End Sub

(2) DAOを使用する場合。
IMEX=1をExtended Propertiesに追加。

Sub test4()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM [T_訪問者$] WHERE (NOT EXISTS " & _
"( SELECT * FROM [T_名簿$] WHERE [T_名簿$].登録者名 = [T_訪問者$].訪問者名) " & _
" AND [T_訪問者$].摘要='訪問者');"

Set db = OpenDatabase(ThisWorkbook.FullName, False, False, "Excel 8.0;HDR=YES;IMEX=1")
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
End If
MsgBox rs.RecordCount

rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

お礼

うまくいきました！IMEX=1の呪文が効いてます
ADOでもバッチリです

ありがとうございました

回答No.4

piroin654

補足の、

SELECT * FROM T_訪問者 WHERE 摘要='訪問者'
WHERE NOT EXISTS ( SELECT * FROM T_名簿
WHERE T_名簿.登録者名 = T_訪問者.訪問者名 );

というSQL文は、

SELECT *
FROM T_訪問者
WHERE (NOT EXISTS ( SELECT * FROM T_名簿
WHERE T_名簿.登録者名 = T_訪問者.訪問者名) AND T_訪問者.摘要='訪問者');

のように書き換えるとどうなりますか？
Excelに対してならば、

strSQL = "SELECT * FROM [T_訪問者$] WHERE (NOT EXISTS " & _
"( SELECT * FROM [T_名簿$] WHERE [T_名簿$].登録者名 = [T_訪問者$].訪問者名 )" & _
" AND [T_訪問者$].摘要='訪問者');"

のような書き方になると思いますが。補足のSQL文ではエラーが出ませんか？

Sub test()
　　Dim cn As ADODB.Connection
　　Dim strSQL As String

　　strSQL = "SELECT * FROM [T_訪問者$] WHERE (NOT EXISTS " & _
　　　　　　　"( SELECT * FROM [T_名簿$] WHERE [T_名簿$].登録者名 = [T_訪問者$].訪問者名 )" & _
　　　　　　　" AND [T_訪問者$].摘要='訪問者');"

　　Set cn = New ADODB.Connection
　　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
　　"Data Source=" & ThisWorkbook.FullName & ";" & _
　　"Extended Properties='Excel 8.0;HDR=YES'"
　　cn.CursorLocation = adUseClient

　　With cn
　　　　With New ADODB.Recordset
　　　　　　.Open strSQL, cn, adOpenKeyset
　　　　　　MsgBox "レコード数は " & .RecordCount
　　　　　　.Close
　　　　End With
　　　　.Close
　　End With

　　Set cn = Nothing
End Sub

上記コードをExcel上で実行すると、未登録件数が0のときは0.
未登録件数が1のときは1を返します。

お礼

引き続きのご回答ありがとうございます

シート上のデータを初めから摘要='訪問者'のレコードだけに絞っておけば
問題ないといえばないのですが、それは最後の手段にしたいです

何か他に手はないでしょうか

補足

いただいたコードで試してみたのですが、結果は変わりませんでした。
具体的にいいますと、

T_登録者名
No,登録者名
1,佐藤
2,鈴木

T_訪問者
No,日付,摘要,訪問者名
1,0601,訪問者,鈴木
1,0601,利用料,300

となっている場合に、
RecordSetが1を返すのです。

利用料300となっているところに文字列を入れれば0が返るので
やはり、訪問者名フィールドを整数型と誤認識された結果ではないかと推察します。

piroin654さんの環境ではそうはならないのでしょうか？
Excelのバージョンあるいはライブラリのバージョン等に依存するのでしょうか？

回答No.3

piroin654

再度、失礼しました。
質問文をよく見ていなかったので。

質問文には、

Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties='Excel 8.0;HDR=YES'"
CN.CursorLocation = adUseClient

とあるので、回答の

　Set cn = New ADODB.Connection
　　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
　　　　　　& "Data Source=" & ThisWorkbook.Path & "\" & "test.mdb"
　　cn.CursorLocation = adUseClient

はすっ飛ばして、

　　With cn
　　　　With New ADODB.Recordset
　　　　　　.Open strSQL, cn, adOpenKeyset
　　　　　　MsgBox "レコード数は " & .RecordCount
　　　　　　.Close
　　　　End With
　　　　.Close
　　End With

の、adOpenKeysetとするところだけとします。あとの（その2）の
回答はそのままです。No2のCSVファイルうんぬんは無しに。

T_名簿とか、T_訪問者はこの場合はシート名ですかね。
ThisWorkbook.FullNameとなっているので、Bookのシート名が
T_名簿とか、T_訪問者になっているのならば、

strSQL = "SELECT * FROM [T_訪問者$] WHERE NOT EXISTS " & _
"( SELECT * FROM [T_名簿$] WHERE [T_名簿$].登録者名 = [T_訪問者$].訪問者名 )"
のように、します。CSVファイルを直接取り扱っているわけではないのですかね。

お礼

ご回答ありがとうございます。

Set RS = CN.Execute(Sql)の代わりに

Set RS = New ADODB.Recordset
RS.Open Sql, CN, adOpenKeyset

としてみたのですが、結果は変わりませんでした
（未登録データ数が0なのに1）

実は、元の質問に挙げたテーブル内容は簡単化したもので、
実際は別のアプリで生成される、より複雑なものなのです

T_訪問者
No,日付,摘要,訪問者名
1,0601,訪問者,鈴木
1,0601,利用料,300
2,0601,訪問者,佐藤
3,0602,訪問者,山田
3,0602,利用料,300

これに対して以下のようなSQL文を投げています

SELECT * FROM T_訪問者 WHERE 摘要='訪問者'
WHERE NOT EXISTS ( SELECT * FROM T_名簿
WHERE T_名簿.登録者名 = T_訪問者.訪問者名 );

いろいろ試してみましたところ、
以上のテーブル例のようにフィールド値が文字列と数値が入っているレコードがあり、
かつ登録済データが1つだけのときこの結果になる傾向があります

少し検索してみると、
テーブルのフィールド型の指定ができないため
勝手に推測して型を決めるらしいことがわかりました
このフィールド値が誤って数値（整数）型だと認識されてしまった際に
この現象が起こるのではないでしょうか

仮にそうだとして、何か対策はありますでしょうか

補足

すみません、ご指摘通り、今回はCSVではなくシートが対象でした
シートのデータへのSQL操作自体は問題なくできています

回答No.2

piroin654

失礼しました。mdbではなくCSVファイルでしたので、

　　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
　　　　　　& "Data Source=" & ThisWorkbook.Path & "\" & "test.mdb"

は、

　　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
　　　　　　& "Data Source=" & ThisWorkbook.Path & "\" & "test.csv"

ですね。拡張子がちがっていました。これも実際に合わせて変更
してください。

補足

すみません、No.3のお礼内のSQL文がおかしかったです

SELECT * FROM T_訪問者 WHERE 摘要='訪問者'
AND NOT EXISTS ( SELECT * FROM T_名簿
WHERE T_名簿.登録者名 = T_訪問者.訪問者名 );

ですね

回答No.1

piroin654

コードを以下のようにしてみてください。

Sub test1()
　　Dim cn As ADODB.Connection
　　Dim strSQL As String

　　strSQL = "SELECT * FROM T_訪問者 WHERE NOT EXISTS " & _
　　　　　　"( SELECT * FROM T_名簿 WHERE T_名簿.登録者名 = T_訪問者.訪問者名 )"

　　Set cn = New ADODB.Connection
　　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
　　　　　　& "Data Source=" & ThisWorkbook.Path & "\" & "test.mdb"
　　cn.CursorLocation = adUseClient

　　With cn
　　　　With New ADODB.Recordset
　　　　　　.Open strSQL, cn, adOpenKeyset
　　　　　　MsgBox "レコード数は " & .RecordCount
　　　　　　.Close
　　　　End With
　　　　.Close
　　End With

　　Set cn = Nothing
End Sub

その1
上記は、レコードセットのカーソルタイプがadOpenKeysetにしてあります。
レコード数を調べるときはadOpenKeysetかadOpenStaticにする必要があります。
なお、
　　cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
　　　　　　& "Data Source=" & ThisWorkbook.Path & "\" & "test.mdb"
の部分は実際に合わせて変更してください。Jet.OLEDB.4.0　とかなど。

その2
Sql = "SELECT COUNT(*) FROM T_訪問者 WHERE NOT EXISTS " & _
"( SELECT * FROM T_名簿 WHERE T_名簿.登録者名 = T_訪問者.訪問者名 )
というSQLはCOUNT(*)という結果がレコードの有無にかかかわらず存在
するのでCOUNT(*)で得られる数が0でも10000でもこのSQLをExecuteして得られる
RcordCountは1です。言っている意味はわかりますか？

補足

COUNT(*)の場合は、RS.RecordsetCountの値ではなく、
RS.Fields(0)の値が0となるべきところが1になる、という意味です
記述を端折ってしまって申し訳ありませんでした