none
SQLServer DSNレス接続(ODBC)のConnectionが切断される RRS feed

  • 質問

  • いつも参考にさせていただいております。

    現在AccessADPファイルの資産のMDB(accdb)化を進めております。
    (DB:SQLServer2012 ,ローカル:VBA(Access2003 or 2010))

    そこで、ひとつのコネクションで、1)temmpdb→2)デフォルトDB→3)tempdbと処理を実行(Execute)していったところ、
    2)の実行後、tempdbへ接続できない、という現象に出くわしております。

    具体的にはロジックサンプルは、

     ’----DSNレス接続
            CN.Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=SV;DATABASE=DB01;"

    '-----1)tempdbに一時テーブル作成
    CN.Execute ”CREATE TABLE [#TempTable] ・・・”

    '-----2)DB01のAテーブルのレコードセットを取得
    Set RS=CN.Execute("Select ・・・FROM Aテーブル WHERE ・・・")

    '-----3)一時テーブルの操作
    CN.Execute("INSERT INTO #TempTable ・・・・")

    3)にて実行時エラー
    「[Microsoft][ODBC SQL Server Driver][SQL Server]オブジェクト名’#TempTable’が無効です」が発生します。

    ・中身のロジックは、ADPにて現在正常稼働中のシステムであり、ConnectionがCurrentProject.Connectionであること以外は同一です
    ・Access2010VBA、Excel2010VBAでも同じエラーとなります
    ・2)実行後のDBのプロセスを確認してもセッションは1本のみです
    ・SQL文に明示的にDB名を付与しても同じ結果となります

    現在発見できた回避策は
    a)recordsetの接続には別Connectionを使用する
    b)recordsetを「RS.Open "SQL文", CN, adOpenStatic, adLockReadOnly」で開いた場合はOK
    (但、CursorTypeEnumがadOpenForwardOnlyはエラー)

    recordsetをExecuteで開いていることでストアド実行のexecと同じ扱いになってしまっているからなのか、
    (であればRS.OpenのadOpenForwardOnlyでエラーになるのはなぜか?)、
    そもそも、DSNレス接続の方法自体が間違っているのか、
    SQLServer側の何かしらの設定で回避できるのか、アドバイスをいただけたら幸いです。

    よろしくお願いいたします。

    2016年1月20日 4:47

回答

  • ご返信ありがとうございます。
    BeginTrans-CommitTransもうまくいきませんでした。

    しかしながら、テストと比較を繰り返したところ、求めていた一連の処理を実行する場合、
    クライアントのADODO.RecordsetはCursorLocationを「adUseClient」で開く必要があるようです。

    ADPの場合、Connection(CurrentProject)もRecordsetもすべて「adUseClient」のままですので、まったく意識する必要はないようですが、ODBC ADO接続の場合、ConnectionもRecordsetも、基本「adUseServer」を継承し、必要に応じて“内部的に”に切り替わる模様です。

    Set RS=CN.Execute(SQL文) -->「adUseServer」
    RS.Open SQL文,CN, -->指定するカーソルタイプによる
      adOpenForwardOnly(デフォルト)--> 「adUseServer」
      adOpenStatic -->「adUseClient」 但し、実行時デバックで取得した値は「adUseServer」

    <結局、このように修正しようと思います>
    '----DSNレス接続
    CN.Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=SV;DATABASE=DB01;"

    '-----1)tempdbに一時テーブル作成
    CN.Execute ”CREATE TABLE [#TempTable] ・・・”

    '-----2)DB01のAテーブルのレコードセットを取得
    RS.CursorLocation = adUseClient
    RS.Open "Select ・・・FROM Aテーブル WHERE ・・・",CN

    '-----3)一時テーブルの操作
    CN.Execute("INSERT INTO #TempTable ・・・・")

    当時の作成者も、MDB化作業をしているわたくしも、カーソルタイプなど、全く意識しておりませんでした。
    同じような書き方(RecordsetのLoop内で別テーブルをExecuteで更新など)をしている資産も多く、気を付けます。

    このように考えると、ADPは非常に"楽な"ツールで、なくなるのは残念です。
    貴重なお時間をいただきまして、ありがとうございました。

    • 回答としてマーク 星 睦美 2016年5月10日 1:33
    2016年1月21日 5:31

すべての返信

  • ADO ConnectionのExecuteメソッドを確認すると、次のような説明があります。

    行を返すクエリ以外のコマンドの場合は、プロバイダは閉じた Recordset オブジェクトを返します。

    なので、Executeした後、セッションとしてはクローズされているのだと思います。
    一時テーブルはリファレンスを確認すると、
    「そのセッションだけがテーブルを使用できます。」となっています。

    対応としては、BeginTransとCommitTransを使用すると回避できるのではないでしょうか。

    (手元で検証していませんが・・・。)

    2016年1月21日 3:23
  • ご返信ありがとうございます。
    BeginTrans-CommitTransもうまくいきませんでした。

    しかしながら、テストと比較を繰り返したところ、求めていた一連の処理を実行する場合、
    クライアントのADODO.RecordsetはCursorLocationを「adUseClient」で開く必要があるようです。

    ADPの場合、Connection(CurrentProject)もRecordsetもすべて「adUseClient」のままですので、まったく意識する必要はないようですが、ODBC ADO接続の場合、ConnectionもRecordsetも、基本「adUseServer」を継承し、必要に応じて“内部的に”に切り替わる模様です。

    Set RS=CN.Execute(SQL文) -->「adUseServer」
    RS.Open SQL文,CN, -->指定するカーソルタイプによる
      adOpenForwardOnly(デフォルト)--> 「adUseServer」
      adOpenStatic -->「adUseClient」 但し、実行時デバックで取得した値は「adUseServer」

    <結局、このように修正しようと思います>
    '----DSNレス接続
    CN.Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=SV;DATABASE=DB01;"

    '-----1)tempdbに一時テーブル作成
    CN.Execute ”CREATE TABLE [#TempTable] ・・・”

    '-----2)DB01のAテーブルのレコードセットを取得
    RS.CursorLocation = adUseClient
    RS.Open "Select ・・・FROM Aテーブル WHERE ・・・",CN

    '-----3)一時テーブルの操作
    CN.Execute("INSERT INTO #TempTable ・・・・")

    当時の作成者も、MDB化作業をしているわたくしも、カーソルタイプなど、全く意識しておりませんでした。
    同じような書き方(RecordsetのLoop内で別テーブルをExecuteで更新など)をしている資産も多く、気を付けます。

    このように考えると、ADPは非常に"楽な"ツールで、なくなるのは残念です。
    貴重なお時間をいただきまして、ありがとうございました。

    • 回答としてマーク 星 睦美 2016年5月10日 1:33
    2016年1月21日 5:31