エクセルからSQLサーバー接続 日付範囲Where

質問

エクセルから、SQLServer2012へ接続しデータを抽出したいのですが、「'>'付近に不適切な構文があります。」のエラーでうまく抽出できません。

テーブル：IncRevDat
カラム
・利用日
・申込先名
・利用数
・計上人員
・計上料金

実行したいSQL文
select
利用日,
利用数,
計上人員,
計上料金
From IncRevDat
Where 利用日 >= セルB3の値　And セルD3の値
Order By 利用日

Public Sub GetData()

Dim con As New ADODB.Connection
Dim connectionString As String

Dim sDBSever As String
Dim sDBName As String
Dim sLoginID As String
Dim sPassWD As String

sDBSever = "サーバーホスト名"
sDBName = "DB名"
sLoginID = "ユーザー名"
sPassWD = "パスワード"

'接続文字列
connectionString = "Provider=Sqloledb;Data Source=" & sDBSever _
& ";Initial Catalog=" & sDBName _
& ";Connect Timeout=15" _
& ";user id=" & sLoginID _
& ";password=" & sPassWD _
& ""

On Error GoTo Err_DBConnectOpen

'接続
con.Open connectionString

'SQL文を実行（RecordSETで受け取ります）
Dim sql As String

'SQL文
sql = ""
sql = sql & " SELECT 利用日,,利用数,計上人員,計上室料" & vbCrLf
sql = sql & " FROM IncRevDat" & vbCrLf
sql = sql & " WHERE 宿泊日 >= " & Cells(3, 2) & ""
sql = sql & " AND <= " & Cells(3, 4) & "" & vbCrLf
sql = sql & " ORDER BY 利用日" & vbCrLf
Set rs = con.Execute(sql)
Debug.Print sql

'セルをクリア
Range(Range("A4"), ActiveSheet.Cells.SpecialCells(xlLastCell).Address).Select
Selection.ClearContents

'結果をセルに書き込む
Dim i As Long
Dim j As Long

'フィールド名をセルに書き込む
If rs.EOF Then
Cells(5, 1) = "情報が有りません。"
Else
rs.MoveFirst
i = 5
For j = 1 To rs.Fields.Count
Cells(i, j) = rs.Fields(j - 1).Name
Next
i = i + 1

Do Until rs.EOF = True
For j = 1 To rs.Fields.Count
Cells(i, j) = rs.Fields(j - 1).Value
Next
rs.MoveNext
i = i + 1
Loop
End If
rs.Close: Set rs = Nothing

'接続を閉じる
con.Close: Set con = Nothing

Exit Sub

'エラー処理
Err_DBConnectOpen:
MsgBox Err.Description
Debug.Print sql

'Connectionの状態を確認し、クローズ
If con.State <> ADODB.adStateClosed Then
con.Close
End If
Set con = Nothing
End Sub

Where句の部分をコメントアウトし、全出力すると正常にデータを読取ってくれます。

Debug.Printの内容
SELECT 利用日,利用数,計上人員,計上料金
FROM IncRevDat
WHERE 宿泊日 >= 2012/08/01 AND <= 2012/08/31
ORDER BY 宿泊日

私だけが使うのならば、AccessクエリをVBAでたたくのですが、他のスタッフも使いAccessがイントールされていないため、エクセルで処理出来ればと考えております。

Where句のコーディング方法が間違っているとは思うのですが、どこをどう直せばよいかが分からず困っています。
間違いをお教え頂けましたら幸いです。

※内容に間違いが有りましたので、数回質問を削除し、再質問しております。
前の質問を見て頂いた方には、ご迷惑をお掛けしてすいませんでした。

回答No.3

NotFound404 · ベストアンサー

ごめん。
sql = ""
sql = sql & " SELECT 利用日,利用数,計上人員,計上室料" & vbCrLf
sql = sql & " FROM IncRevDat" & vbCrLf
sql = sql & " WHERE 宿泊日 >= '" & Cells(3, 2) & "'"
sql = sql & " AND 宿泊日<= '" & Cells(3, 4) & "'" & vbCrLf
sql = sql & " ORDER BY 利用日;"
rs.open sql,con
Debug.Print sql
だと・・・？

>ユーザー名とパスワードを埋め込まなくてよい方法が有るのでしょうか？
これをやれば鉄壁の防御・・・というのがあればこちらが知りたいです！
小手先ですが、
Excel2010では、xlsmファイルのBookにもパスワードを設定すると
ファイルの拡張子を ZIP に変えて中身の確認・・・が出来なくなります。
どこまで有効かはわかりませんが『出来心で』という人には役立つかと思います。

お礼

度々の回答ありがとうございます。
お教え頂いた、コーディングで目的が達成できました。
原因については、時間のある時に見比べて今後tの参考にしたいと思います。

あわせて、セキュリティ設定についてもお教え頂きありがとうございました。

回答No.2

NotFound404

'SQL文
sql = ""
sql = sql & " SELECT 利用日,利用数,計上人員,計上室料" & vbCrLf
sql = sql & " FROM IncRevDat" & vbCrLf
sql = sql & " WHERE 宿泊日 >= #" & Cells(3, 2) & "#"
sql = sql & " AND 宿泊日<= #" & Cells(3, 4) & "#" & vbCrLf
sql = sql & " ORDER BY 利用日;"
rs.open sql,con
Debug.Print sql
では。

あと今回は問題はならないだろうけど
Do Until rs.EOF = True
For j = 1 To rs.Fields.Count
Cells(i, j) = rs.Fields(j - 1).Value
Next
rs.MoveNext
i = i + 1
Loop
だと電話番号などでは前ゼロがクリアされてしまうので
cells(i,1).copyfromrecordset rs
とした方がデータ型通りに入るので良いかも。

さらに余計なお世話かもしれませんが
ユーザー名・パスワードを埋め込んでいるのは不安です。
たとえVBAProjectの保護を行う予定であっても。

お礼

#1のお礼にも記載しましたが、質問内容に間違いが有りました。申し訳ありません。
あわせて、回答頂きありがとうございます。

>だと電話番号などでは前ゼロがクリアされてしまうので
>cells(i,1).copyfromrecordset rs
>とした方がデータ型通りに入るので良いかも。
全く意識してませんでした、今後の参考にさせて頂きます。

お教え頂いたとおり、Where句の部分を下記の通り書いた所
sql = sql & " WHERE 宿泊日 >= #" & Cells(3, 2) & "#" & vbCrLf
sql = sql & " AND 宿泊日<= #" & Cells(3, 4) & "#" & vbCrLf

エラーメッセージに、「'#'付近に不適切な構文があります。」
と表示され、SQL NGになります。
確かに、Accessでは、日付型にするためには"#"でくくる必要が有りますが、エクセルでも同じと考えてよろしいでしょうか？
また、その場合の「'」、「"」のくくり方だと思うのですが、どう書けばうまく抽出出来ますでしょうか？

ちなみに、debug.print sqlの値は
SELECT i通番,宿泊日, 泊数,泊目,申込先名,総室数,計上人員,計上室料
FROM IncRebDat
WHERE 宿泊日 >= 2012/08/01 AND 宿泊日<= 2012/12/31
ORDER BY 宿泊日
となっております、「#」でくくられてません。
多分初歩的な「'」や「"」の意味合いを理解していないのだと思いますが、VBAは難しいです。

余談ですが、ユーザー名とパスワードを埋め込まなくてよい方法が有るのでしょうか？
今回は、SQLSERVER側に接続用の閲覧ユーザーにしてますので、問題は無いと思いますが、書込み権限を付与した場合には大きな問題になりそうなので、あつかましいお願いですがお教え頂けたら幸いです。

また、VBAProjectの保護についても、全く知りませんでした。
VBAは奥が深いですね。。。
上記については、時間のある時に調べてみます。
アドバイス頂きありがとうございました。

補足

すいません、また内容が間違ってました。

debug.print sqlの値は、
SELECT i通番,宿泊日, 泊数,泊目,申込先名,総室数,計上人員,計上室料
FROM IncRebDat
WHERE 宿泊日 >= #2012/08/01# AND 宿泊日<= #2012/12/31#
ORDER BY 宿泊日
で、ちゃんと「#」でくくられてました。

回答No.1

maiko0318

構文が違います。
Where 利用日 >= セルB3の値　And セルD3の値

間を取りたいなら
Where 利用日 between セルB3の値　And セルD3の値

別の条件なら
Where 利用日 >= セルB3の値　And 比較したいカラム = セルD3の値

です。

お礼

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

すいません、質問内容に誤りがありました。
もう、旅行業界がバレバレですので正確に記載します。

実行したいSQLのWhere句が
where 宿泊日 >= セルB3の値 And <= セルD4の値
これをVBA的に書く方法が良くわかりません。
お教え頂いた内容を書くと
sql = sql & " WHERE 宿泊日 between " & Cells(3, 2) & ""
sql = sql & " AND " & Cells(3, 4) & "" & vbCrLf

debug.print sqlが
SELECT i通番,宿泊日, 泊数,泊目,申込先名,総室数,計上人員,計上室料
FROM IncRebDat
WHERE 宿泊日 between 2012/08/01 AND 2012/12/31
ORDER BY 宿泊日
SQLとしては通っていると思うのですが、シートには、「情報が有りません」とRecoresetにデータが無いとされます。

または、
sql = sql & " WHERE 宿泊日 >= " & Cells(3, 2) & ""
sql = sql & " AND 宿泊日<= " & Cells(3, 4) & "" & vbCrLf
と書くと結果は同じで、debug.print sql の値は、
SELECT i通番,宿泊日, 泊数,泊目,申込先名,総室数,計上人員,計上室料
FROM IncRebDat
WHERE 宿泊日 >= 2012/08/01 AND 宿泊日<= 2012/12/31
ORDER BY 宿泊日

やはり、シートには、何も書かれません。

エクセルのVBAは難しいです。