ＡＤＯでアクセスからエクセルシートの件数を取得した

質問

ＡＤＯでアクセスからエクセルシートの件数を取得したいのですが、うまく行きません。

アクセスの標準モジュールに
Sub ADO_TEST1()

Const StrFileName = "D:\My Documents\検索.xls"

Dim dbCon As ADODB.Connection
Dim dbRes As ADODB.Recordset
Dim i As Long
Dim strSQL As String

' Connection生成
Set dbCon = New ADODB.Connection
With dbCon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open StrFileName
End With

' SQL文作成
strSQL = "SELECT * FROM [Access接続用$];"

Set dbRes = New ADODB.Recordset

dbRes.Open strSQL, dbCon, adOpenDynamic, adLockOptimistic, adCmdText

i = dbRes.RecordCount
MsgBox "エクセルのAccess接続用シートの最終行は、" & i & "行です。"

dbRes.Close: Set dbRes = Nothing
dbCon.Close: Set dbCon = Nothing
End Sub
*************************************************************
を貼り付けて実行すると、エラーにはならないのですが-1が返ってきます。

実際の行は、200行あります。

ここで、エクセルの最終行を取得して、いづれエクセルのデータをアクセスのテーブルに転記したいのですが
まずここで躓いてしまったので、

・dbRes.RecordCountで、-1が返ってくる原因
・エクセルの該当のシートの最終行の取得の仕方

をご教授ください。

ご回答よろしくお願いします。

回答No.3

30246kiku · ベストアンサー

cn.CursorLocation = adUseClient

を追加されて解決されたようですね。

以下は評価外の参考程度で

レコードセットをオープンする際のカーソルの種類を　adOpenDynamic　から
adOpenStatic　もしくは　adOpenKeyset　に変更してみてはどうでしょうか。

また、Excel 側を更新できなかったと思うので、ロックの種類は
adLockReadOnly　としてはどうでしょうか。

以下、私の環境下 2007 での確認結果
（参考になるかどうか）

Private Const sPath As String = "E:\Excel\qa\et1.xls" ' Excel フルパス
Private Const sTbl As String = "TBL$" ' シート名 + $

Public Sub test1()
　　Dim cn As New ADODB.Connection
　　Dim rs As New ADODB.Recordset

　　On Error Resume Next
　　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";" _
　　　　　　& "Extended Properties='Excel 8.0;HDR=Yes'"

　　rs.Source = "SELECT * FROM [" & sTbl & "];"
　　rs.Open , cn, adOpenForwardOnly, adLockReadOnly
　　rs.MoveLast
　　Debug.Print "1 Rec Count = " & rs.RecordCount
　　Debug.Print "1 Last Pos = " & rs.AbsolutePosition
　　rs.Close

　　rs.Open , cn, adOpenStatic, adLockReadOnly
　　rs.MoveLast
　　Debug.Print "2 Rec Count = " & rs.RecordCount
　　Debug.Print "2 Last Pos = " & rs.AbsolutePosition
　　rs.Close

　　rs.Open , cn, adOpenKeyset, adLockReadOnly
　　rs.MoveLast
　　Debug.Print "3 Rec Count = " & rs.RecordCount
　　Debug.Print "3 Last Pos = " & rs.AbsolutePosition
　　rs.Close

　　rs.Open , cn, adOpenDynamic, adLockReadOnly
　　rs.MoveLast
　　Debug.Print "4 Rec Count = " & rs.RecordCount
　　Debug.Print "4 Last Pos = " & rs.AbsolutePosition
　　rs.Close
　　cn.Close
End Sub

イミディエイトウィンドウへの出力結果は
（-1 かどうかだけを見て頂ければ）

1 Rec Count = -1
1 Last Pos = -1
2 Rec Count = 12
2 Last Pos = 12
3 Rec Count = 12
3 Last Pos = 12
4 Rec Count = -1
4 Last Pos = -1

なお、Excel の参照の仕方を変えた以下でも結果は同じでした。

Public Sub test2()
　　Dim cn As ADODB.Connection
　　Dim rs As New ADODB.Recordset

　　On Error Resume Next
　　Set cn = CurrentProject.Connection

　　rs.Source = "SELECT * FROM [TBL$] IN 'E:\Excel\qa\et1.xls'[Excel 8.0;HDR=YES;];"
　　rs.Open , cn, adOpenForwardOnly, adLockReadOnly
　　rs.MoveLast
　　Debug.Print "1 Rec Count = " & rs.RecordCount
　　Debug.Print "1 Last Pos = " & rs.AbsolutePosition
　　rs.Close
　　・・・・
　　・・・・

後の処理で転記をするのなら、この書き方の方が便利かも・・・
他テーブルと結合できたと思うし・・・・（ INNER JOIN とかとか ）
※ 他テーブルと結合したい場合、CursorLocation を変更してよいのか・・・
　　・・・私にはわかりませんけど

例）　テーブル「T1」に「ID」「名称」のフィールドがあり、
Excelシートにある「ID」とテーブル「T1」の「ID」を結んで、「名称」も表示したい

rs.Source = "SELECT T1.名称, Q1.* FROM T1 INNER JOIN " _
　　& "(SELECT * FROM [TBL$] IN 'E:\Excel\qa\et1.xls'[Excel 8.0;HDR=YES;]) AS Q1 " _
　　& "ON T1.ID = Q1.ID;"

> MsgBox rs.RecordCount
> で取得される値は実際の行数-1なのですが、なぜでしょうか？
>
> 勝手に「フィールドはレコードに含めない」と認識されるのでしょうか？

HDR=YES を指定すれば、１行目をフィールド名と解釈します。
なので、レコード数は 行数 - 1 になります。
HDR=NO を指定すれば、１行目もデータと解釈します。
なので、レコード数は行数と一致します。
また、フィールド名は「F1」「F2」・・・のように振られます。

お礼

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

回答No.2

piroin654

"Extended Properties='Excel 8.0;Header=Yes';" & _

のところを以下のようにしてください。

"Extended Properties='Excel 8.0;HDR=NO';" & _

Header=Yes　を　HDR=NO　にしています。
一行目をフィールド名としている場合で、一行目をカウント
しない場合は、

HDR=YES

にします。一行目からデータの場合は

HDR=NO

にして、一行目からデータとして扱います。
なお、Headerでは認識しないようなのでHeaderもHDRに
変更しました。

お礼

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

回答No.1

piroin654

レコードセットの取り方が間違っているのでは？

変数名が質問とは変わっていますが、以下では？

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties='Excel 8.0;Header=Yes';" & _
"Data Source=D:\My Documents\検索.xls"

Set rs = cn.Execute("SELECT * FROM [Access接続用$]")
MsgBox rs.RecordCount
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

Excelに接続してデータを取得する方法はいくつかありますが、
検索すればいくらでもあると思います。

お礼

ご教示いただいた通りにやってみたら出来ました。

だた一つ疑問なのは、
MsgBox rs.RecordCount
で取得される値は実際の行数-1なのですが、なぜでしょうか？

勝手に「フィールドはレコードに含めない」と認識されるのでしょうか？