質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.34%

  • VBA

    1909questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • SQL Server

    623questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

  • Access

    474questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

実行時エラー(オブジェクトが開いている場合は操作は許可されません)になる

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 3,023

GigaTail

score 1

 前提・実現したいこと

Access VBA からSQL Serverの[M_取引先マスタ]にテーブルロックを掛けて、フォームに入力があった内容を新規追加したく思っています。

 発生している問題・エラーメッセージ

下記ロジックを実行すると、2つ目のSQL文実行時(M_取引先マスタのロックSQL)に
【実行時エラー '3705' オブジェクトが開いている場合は、操作は許可されません。】
とエラーになります。

SELECT COUNTのSQL文をコメントアウトすると、INSERT文の行で同エラーになります。

別ロジックで[DELETE]-->[INSERT]している部分では問題無く動作しているため、コピペするかたちで作ったのですが、SQL文実行の都度クローズしなくてはいけないのでしょうか?

 該当のソースコード

Public CN_SQL As New ADODB.Connection
Public RS_SQL As New ADODB.Recordset
' -----------------------------------------------------------------------------
' Connectionオブジェクトを生成
' -----------------------------------------------------------------------------
Public Sub DB_Connect()

Dim ConnectionString As String

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

sDBSever = DLookup("[データ]", "M_システム", "[ID] = 'SvName'") ' SQL Serverが稼働しているサーバー名
sDBName = DLookup("[データ]", "M_システム", "[ID] = 'DbName'")  ' SQL ServerのDB名
sLoginID = DLookup("[データ]", "M_システム", "[ID] = 'Login'")  ' sa
sPassWD = DLookup("[データ]", "M_システム", "[ID] = 'Pass'")' saのパスワード

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

' 接続
CN_SQL.Open ConnectionString

End Sub

' -----------------------------------------------------------------------------
' データベースへの接続を解除する
' -----------------------------------------------------------------------------
Public Sub DB_DISCONNECT()

CN_SQL.Close
Set CN_SQL = Nothing

End Sub

' -----------------------------------------------------------------------------
' 引数のSQL文を実行し、ADODB.Recordsetを返す
' -----------------------------------------------------------------------------
Public Function DB_EXECUTE(s_SQL As String, CursorType, LockType, b_FLG As Boolean) As ADODB.Recordset

b_FLG = True

' タイムアウト設定 (15分)
CN_SQL.CommandTimeout = 60 * 15

' 処理された行数を示すメッセージが結果セットの一部として返されないようにする
CN_SQL.execute ("SET NOCOUNT ON")

' 警告メッセージが結果セットの一部として返されないようにする
CN_SQL.execute ("SET ANSI_WARNINGS OFF")

' オーバーフローおよび0除算時にはNULLを返す
CN_SQL.execute ("SET ARITHABORT OFF")


RS_SQL.CursorType = CursorType
RS_SQL.LockType = LockType

RS_SQL.Open s_SQL, CN_SQL
'RS_SQL.Open s_SQL, CN_SQL, CursorType, LockType

Do
' レコードの操作ができるオブジェクト若しくは次のRecordSetがとれず、コネクションが空になった場合終了
If RS_SQL.State = adStateOpen Or RS_SQL.ActiveConnection Is Nothing Then
Exit Do
End If
Set RS_SQL = RS_SQL.NextRecordset()
Loop

Set DB_EXECUTE = RS_SQL


' 設定OFF
CN_SQL.execute ("SET NOCOUNT OFF")
CN_SQL.execute ("SET ANSI_WARNINGS ON")
CN_SQL.execute ("SET ARITHABORT ON")

End Function

' -----------------------------------------------------------------------------
' トランザクションを開始する
' -----------------------------------------------------------------------------
Public Sub BeginTransaction()

CN_SQL.BeginTrans

End Sub

' -----------------------------------------------------------------------------
' トランザクションをコミットする
' -----------------------------------------------------------------------------
Public Sub CommitTransaction()

CN_SQL.CommitTrans

End Sub

' -----------------------------------------------------------------------------
' トランザクションをロールバックする
' -----------------------------------------------------------------------------
Public Sub RollbackTransaction()

CN_SQL.RollbackTrans

End Sub

' -----------------------------------------------------------------------------
' F_取引先登録のコード
' -----------------------------------------------------------------------------
'DB接続
Call DB_CONNECT

'SQL文生成
s_SQL = ""
s_SQL = "SELECT COUNT(*) AS REC_CNT "
s_SQL = s_SQL & "FROM M_取引先マスタ "
s_SQL = s_SQL & "WHERE 取引先CD LIKE '201804%';"

i_Seq = REC_CNT + 1

'SQL文実行
RS_SQL.Open s_SQL, CN_SQL, adOpenStatic, adLockReadOnly

'トランザクション開始
Call BEGINTRANSACTION

'SQL文生成
s_SQL = ""
s_SQL = "SELECT * FROM M_取引先マスタ WITH (TABLOCK, HOLDLOCK);"

'SQL文実行
'RS_SQL.Open s_SQL, CN_SQL, adOpenKeyset, adLockPessimistic

s_SQL = ""
s_SQL = "INSERT INTO M_取引先マスタ (取引先CD, 取引先名, 取引先住所, 更新日時) "
s_SQL = s_SQL & "VALUES ( "
s_SQL = s_SQL & "'" & s_SysDate & String(3 - Len(CStr(i_Seq)), "0") & CStr(i_Seq) & "', "
s_SQL = s_SQL & "N'" & Forms!F_取引先登録.txt_取引先名.Value & "', "
s_SQL = s_SQL & "N'" & Forms!F_取引先登録.txt_取引先住所.Value & "', "
s_SQL = s_SQL & "'" & Format(Now(), "yyyy/mm/dd hh:mm:ss") & "' "
s_SQL = s_SQL & ");"

'SQL文実行
RS_SQL.Open s_SQL, CN_SQL, adOpenKeyset, adLockPessimistic

'SQL文コミット
Call COMMITTRANSACTION

'接続を閉じる
Call DB_DISCONNECT



' -----------------------------------------------------------------------------
' 以下は[DELETE]-->[INSERT]しているロジックです
' -----------------------------------------------------------------------------

'----- DB接続 -----
Call DB_Connect

'----- トランザクション開始 -----
Call BeginTransaction

'該当データをDELETE --> INSERT
s_SQL = "DELETE FROM T_案件テーブル WHERE ID = '" & KEY & "';"

'SQL文実行
RS_SQL.Open s_SQL, CN_SQL, adOpenKeyset, adLockPessimistic

s_SQL = "INSERT INTO T_案件テーブル (ID, FLD1, FLD2, FLD3) "
s_SQL = s_SQL & "VALUES ( , , , );"

'SQL文実行
RS_SQL.Open s_SQL, CN_SQL, adOpenKeyset, adLockPessimistic

'----- DBコミット -----
Call CommitTransaction

'----- 接続を閉じる -----
Call DB_DISCONNECT

 試したこと

 補足情報(FW/ツールのバージョンなど)

Access2010
SQL Server2014

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • mts10806

    2018/04/19 15:37

    プログラムコード(およびエラーメッセージ)は```で囲ってください。(わからなければ質問編集画面でコード部分を選択し<code>ボタンを押してください)正しく反映されているかどうかは質問編集画面のプレビューを見ながら編集していってください。

    キャンセル

  • sazi

    2018/04/19 16:25

    RS_SQLの定義や作成部分も追記して下さい。

    キャンセル

回答 1

checkベストアンサー

0

RS_SQL.Openで毎回セッションを開いているんじゃないですか?
別セッションなら当然排他されますので。

追記

SELECT COUNTのSQL文をコメントアウトすると、INSERT文の行で同エラーになります。

insert って openじゃなくて、executeじゃなかった?
まあ、本題とは関係ないですけど。

追加されたコードを見るにセッションは引き継いでいるように見えますね。

Call BEGINTRANSACTION


の前でCOMMITするとどうなります?

やっぱり、openでの排他モードが関係しているような気がします。
selectでテーブル単位の排他を行っていますけど、openは基本カーソルなのでレコード単位までしかありません。
executeに変えてみてはどうでしょうか。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/04/19 16:59 編集

    コメントいただきありがとうございます。

    全SQLとも[RS_SQL.Open]としています。
    プロシージャ内で[RS1_SQL][RS2_SQL]と変えていった方がいいのでしょうか?

    キャンセル

  • 2018/04/19 17:08

    ご教授ありがとうございます。

    デバッグしてみると「アクティブなトランザクションがありません」となります。

    キャンセル

  • 2018/04/19 17:11

    あ、beginもセットじゃないと駄目です。
    でもそこではない気がするので、回答に追記したので確認ください。

    キャンセル

  • 2018/04/19 17:15

    実際に試せてはいないので、トライ&エラーになってしまいます。
    ですので、識者の回答をまちましょうか。

    キャンセル

  • 2018/04/19 17:19

    感覚的にはwith()はsqlserverでの排他で、openはadoでの排他になるのでレベルがそろってないのでは無いかという気がしますが。

    キャンセル

  • 2018/04/19 17:20

    因みに以下はADOでの排他です。
    http://www5f.biglobe.ne.jp/~f-lap/tips_adolock.htm

    キャンセル

  • 2018/04/19 17:24

    実は16日の午後から行き詰っております。
    Access <--> SQL Server というのは今回が初めてな上、社内は「データベースって何?」って感じなのでWEBからの寄せ集めで奮闘しています。

    キャンセル

  • 2018/04/19 17:53

    方法としては、
    1.取り敢えず排他は後回し
    2.DAOの排他に揃える
    3.with()とexecutの組合せ
    3は推測の域を出ないですが。

    キャンセル

  • 2018/04/23 11:29

    SQL文実行後に
    RS_SQL.Close
    を追記することでエラー表示なく、動作することができました。

    お忙しい中、ご教授いただきましてありがとうございます。

    キャンセル

同じタグがついた質問を見る

  • VBA

    1909questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • SQL Server

    623questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

  • Access

    474questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。