こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

【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

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

投稿日時 - 2019-02-07 23:18:57

QNo.9585654

困ってます

質問者が選んだベストアンサー

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

投稿日時 - 2019-02-08 08:12:02

お礼

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

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

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

 ここ過程で、

DBLookup()

cnn.Provider = "MSDASQL"
‥‥

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

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

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

投稿日時 - 2019-02-08 09:29:17

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

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

投稿日時 - 2019-02-08 09:10:41

お礼

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

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

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

投稿日時 - 2019-02-08 09:46:26

あなたにオススメの質問