ExcelでAccessのテーブルの操作

質問

Excelで期間選択すると、Accessのクエリを使わずに直接テーブルを覗いて選択した期間のデータを抜き出す事は可能でしょうか？

操作したいAccessのテーブルはこのような形になっています。
名称.tblというテーブルをビューすると

日付 　　　　名前　　値
20130101 大田　　10000
20130201 鈴木　　13000
20130211 加藤　　52000
20130301 石黒　　40000　
20130314 田中　　9500
20130401 石井　　1000

日付と名前と値が入っています。
Excelで指定した期間と名前を変数に保持するか、Excelのシートに保存したいのです。

現状はExcel側で
期間を選択して、実行ボタンを押す。

実行ボタンをクリックするとAccessのmdbファイルに接続。

Option Explicit

Dim strAccess As String

Private Sub cmb実行_Click()

strAccess　= \\接続先\接続.mdb
dbCon.Open cnsADO_CONNECT1 & strAccess

これでAccessファイルに接続は出来ています。

日付のレコードに対して該当する期間があるかどうかをFor文辺りでループをかけて、該当する日付と名前と値を抜き出したいのです。
この処理は可能でしょうか？
手詰まりを起こしていますのでお知恵をお貸し下さい。

回答No.2

m3_maki · ベストアンサー

ADOで、ということでしたら、基本的にはこちらの方法で。

Excelユーザのデータベース活用法-ADOレコードセットからワークシートへ
http://www.moug.net/tech/exvba/0150068.html

SQL に 「Where条件」 を 付加することで、
該当期間のデータのみを簡単に取得することができます。

「日付」フィールドのデータ型によって記述が変わりますが、
数値型の場合ですと

MySQL = "SELECT * FROM テーブル名 WHERE 日付 BETWEEN 20130101 AND 20130131 ORDER BY 日付

などのように。

お礼

ありがとうございます、上のコードに下記構文を追加した事で。一応動くようにはなりました

strsql = "SELECT * FROM 接続先 WHERE 日付 "
dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly

Gyo = 1
Rows("2:65536").ClearContents
dbRes.MoveFirst
Do Until dbRes.EOF
Gyo = Gyo + 1
Set dbCols = dbRes.Fields
Cells(Gyo, 1).Value = dbCols("日付").Value
Cells(Gyo, 2).Value = dbCols("名前").Value
Cells(Gyo, 3).Value = dbCols("値").Value

dbRes.MoveNext
Loop
dbRes.Close
Set dbRes = Nothing
dbCon.Close
Set dbCon = Nothing

しかし、これだと期間の指定が出来ていません。

dim strSData as string
dim strEData as string

strSData = Worksheets("設定").Range("C2")'201301
strEData = Worksheets("設定").Range("C3")'201304

この状態で

strsql = "SELECT * FROM 接続先 WHERE 日付 BETWEEN strStartData AND strEndData ORDER BY 日付"

と、追加して上記構文を動かしてみたのですがエラーが出て動きません。
「1つ以上の必要なパラメーターの値が設定されていません。」
と、出てここの部分で止まってしまいます
dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly

変数の宣言の仕方が悪いのでしょうか？？

回答No.1

keithin

接続方法に応じて様々方策は考えられます。

とりあえずご質問の直接の回答として、今ＡＤＯを経由しているのでしたら
参照：
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_030.html
「 ADOでAccessデータベースからデータをシート上に展開する」のサンプルマクロで１フィールドずつ巡回をしていますので、「該当する日付と名前」ならシートに書きだすようにちょいと細工を追加すれば出来上がりです。

他にも「外部データの接続」でＳＱＬを使って絞り込みながら一気に抽出してしまうのも、簡単な方法の一つです。
「外部データの接続」から「新しいデータベースクエリ」「Driver do Microsoft Access(*.mdb)」…とステップを進めていき、絞り込み条件を記入して最後にワークシートにデータを返す、といった操作を新しいマクロの記録でマクロに録ってしまえば、あとは条件記入のマクロの部分だけ細工すれば完成です。

あるいは状況によりますがエクセル標準の「ピボットテーブルレポート」を使ってｍｄｂファイルに直接接続、とりあえず全データ吸い上げにはなりますが、レポートの絞り込み機能で必要な結果だけをシートに展開させるような事も簡単に実現できます。

お礼

ありがとうございます、上のコードに下記構文を追加した事で。一応動くようにはなりました

strsql = "SELECT * FROM 接続先 WHERE 日付 "
dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly

Gyo = 1
Rows("2:65536").ClearContents
dbRes.MoveFirst
Do Until dbRes.EOF
Gyo = Gyo + 1
Set dbCols = dbRes.Fields
Cells(Gyo, 1).Value = dbCols("日付").Value
Cells(Gyo, 2).Value = dbCols("名前").Value
Cells(Gyo, 3).Value = dbCols("値").Value

dbRes.MoveNext
Loop
dbRes.Close
Set dbRes = Nothing
dbCon.Close
Set dbCon = Nothing

しかし、これだと期間の指定が出来ていません。

dim strSData as string
dim strEData as string

strSData = Worksheets("設定").Range("C2")'201301
strEData = Worksheets("設定").Range("C3")'201304

この状態で

strsql = "SELECT * FROM 接続先 WHERE 日付 BETWEEN strStartData AND strEndData ORDER BY 日付"

と、追加して上記構文を動かしてみたのですがエラーが出て動きません。
「1つ以上の必要なパラメーターの値が設定されていません。」
と、出てここの部分で止まってしまいます
dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly

変数の宣言の仕方が悪いのでしょうか？？