Excel:SQL実行関数のエラー原因を知るには？

質問

年甲斐もなく昨日からSQLでExcelデータを参照、更新、削除する二つの関数の作成に挑戦しています。が、

＞[Microsoft][ODBC Excel Driver] パラメーターが少なすぎます。
＞1を指定してください。

とのエラーで立ち往生中。

　作成しているのは、SELECT文を実行するDBLookup()とUPDATE文、INSERT文、DELETE文を実行するCnnExecute()の二つ。どちらも、添付図のようにTESTに成功。で、一旦、Excel を閉じて、もう再テスト。すると、前述のエラーに遭遇。一度だけDBLookup()が正常に動いたのでCnnExecute()も再テスト。すると、前述のエラーが発生。同時に、DBLookup()も動かなくなりました。で、その後、５時間の試行錯誤中。だが、手詰まり感強く質問することに。このようなバグ取りで他者に質問と言う形で助けを求めるのは実に情けないことです。そこは、大目に見て下さい。

【質問】どういうアプローチをすべきと思いますか？

【DBLookup()のコード】

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　 Optional xlFileName As String = "", _
　　　　　　　　　　　　 Optional returnValue As String = "") As Variant
On Error GoTo Err_DBLookup
　　'
　　' 【要参照設定】
　　'
　　' Micrsoft ActiveX Data Objects 2.8 Library
　　'
　　Dim DataValue
　　Dim cnn As ADODB.Connection
　　Dim rst As ADODB.Recordset

　　Set cnn = New ADODB.Connection
　　Set rst = New ADODB.Recordset
　　'
　　' ThisWorkbook.FullName の指定
　　'
　　If Not Len(xlFileName) Then
　　　　 xlFileName = ThisWorkbook.FullName
　　End If
　　'
　　' 接続設定
　　'
　　With cnn
　　　　.Provider = "MSDASQL"
　　　　'
　　　　' 32bit
　　　　'
　　　　'cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
　　　　'　　　　　　　　　　　"DBQ=" & xlFileName & ";" & _
　　　　'　　　　　　　　　　　"ReadOnly=False;"
　　　　'
　　　　' 64bit
　　　　'
　　　　.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
　　　　　　　　　　　　　　"DBQ=" & xlFileName & "; ReadOnly=False;"
　　　　.Open
　　　　'
　　　　' 列の読み込み
　　　　'
　　　　With rst
　　　　　　.Open strQuerySQL, cnn, adOpenStatic
　　　　　　If Not .BOF Then
　　　　　　　　.MoveFirst
　　　　　　　　DataValue = .Fields(0) & ""
　　　　　　End If
　　　　End With
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　cnn.Close
　　Set rst = Nothing
　　Set cnn = Nothing
　　DBLookup = IIf(Len(DataValue), DataValue, returnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

【CnnExecute()のコード】

Public Function CnnExecute(ByVal strSQL As String, _
　　　　　　　　　　　　　 Optional xlFileName As String = "") As Boolean
On Error GoTo Err_CnnExecute
　　'
　　' 【要参照設定】
　　'
　　' Micrsoft ActiveX Data Objects 2.8 Library
　　'
　　Dim isOK As Boolean
　　Dim DataValue
　　Dim cnn As ADODB.Connection

　　isOK = True　
　　Set cnn = New ADODB.Connection
　　'
　　' ThisWorkbook.FullName の指定
　　'
　　If Not Len(xlFileName) Then
　　　　 xlFileName = ThisWorkbook.FullName
　　End If
　　'
　　' 接続設定
　　'
　　With cnn
　　　　.Provider = "MSDASQL"
　　　　'
　　　　' 32bit
　　　　'
　　　　'cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
　　　　'　　　　　　　　　　　"DBQ=" & xlFileName & ";" & _
　　　　'　　　　　　　　　　　"ReadOnly=False;"
　　　　'
　　　　' 64bit
　　　　'
　　　　cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
　　　　　　　　　　　　　 "DBQ=" & xlFileName & ";" & _
　　　　　　　　　　　　　 "ReadOnly=False;"
　　　　.Open
　　　　.Execute strSQL
　　End With
Exit_CnnExecute:
On Error Resume Next
　　cnn.Close
　　Set cnn = Nothing
　　CnnExecute = isOK
　　Exit Function
Err_CnnExecute:
　　isOK = False
　　MsgBox "SQL 文の実行時にエラーが発生しました。(CnnExecute)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strSQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_CnnExecute
End Function

　「あっ、これは、多分、こういうことだな！」という思われた方は、気軽にご回答ください。

回答No.1

mabyos · ベストアンサー

このような事例がありました。
https://ssl.monozukuri.org/mzplatform/faq/faq/faq_technical/tutorial/excel/excel_a1.html

お礼

回答ありがとうございます。昨晩の23時頃に、私も紹介のサイトを発見。で、全く、同じエクセルを再現してテスト。すると、OK。だが、再テスト用のエクセルではエラー発生。つまり、同記事の言う通りでした。

対策１、接続拡張プロパティも指示する。
対策２、SQL文からミスを追放する。

・ヘッダーの有無の指示・・・・・・・・・・・DBLookup()に１行追加
・テーブル構造のデータの位置と範囲の指定・・SQL文を適正化

　ここ過程で、

DBLookup()

cnn.Provider = "MSDASQL"
‥‥

BLookup()
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
‥‥

と、DLookup()も作成。で、どちらともに、同じ現象であることも確認。もって、接続設定ではなくて紹介サイトの指摘通りとの確信を深めつつあります。

　なお、質問は、午後２０時に削除を試みたのですが、いわゆる《設定》ボタンを見つけられずに放置した次第です。ご迷惑をおかけしました。

回答No.2

HohoPapa

MsgBox (DBLookup("select 読み from [Sheet1$B2:Z123] where 顧客名='鈴木　一郎'"))
とするか、

Sheet1のA列と1行目を削除することで改善しませんでしょうか？

お礼

回答ありがとうございます。先のお礼に書いた通りです。

HohoPapaさんの指摘の通り。原因は、２つでした。拡張プロパティでヘッダの有無をちゃんと宣言し、かつ、 [Sheet1$B2:Z123]とデータ領域も指定することで解決するようです。

しかし、20年振りのVBA関数への挑戦。70歳なのに、ちょっと胸が躍っています。なお、ベストアンサーは、トップの方にしますが悪しからず・・・。