excelからAccessのDBを更新でエラー

質問

excel2016

excelでaccessへのデータ登録はできたのですが、登録したaccessデータの更新でエラーが発生します。

構成
MENUシートのD4に表示されたシート名でaccessへ登録を実施。
このシート名はaccessのテーブル名になっている。
accesssのファイル名は
Machine.accdbでサーバに登録している（サーバ名の実名は伏せて***にしてます）
accessファイルは単独で編集できない様にパスワード付きでexcelからのみ追加更新を可能にする。

excelの題目
A1:ref_serial
C1:result1
D1:result2
他にも題目ありますが、関係する部分とします。

データは
A2セルに数字4桁
C2セル整数2桁小数点3桁
D2セル整数2桁小数点3桁

accessで
ref_serial、オートナンバー、長整数型
result1,数値型、単精度浮動小数点型
result2,数値型、単精度浮動小数点型
としてます

addのマクロで追加した内容を、そのままrenewのマクロ実行すると
実行時エラー424
オブジェクトが必要です
とのポップアップエラーが発生します。

エラーが発生するのは
strSQL = _
"UPDATE '" & sheet_name & "' " & _
"SET " & _
ws.Worksheets(sheet_name).Range("C1").Value & "=" & ws.Worksheets(sheet_name).Range("C2").Value & "," & _
ws.Worksheets(sheet_name).Range("D1").Value & "=" & ws.RWorksheets(sheet_name).ange("D2").Value & "," & _
"WHERE ref_serial =" & ws.Worksheets(sheet_name).Range("A2").Value
の部分。
web等でも調べたのですが、何が悪いのかわからず、このエラーが出ない様に修正いただきたく、よろしくお願いします。

データ登録のマクロ
Sub add()

Dim strFileName As String
strFileName = "Machine.accdb"

Dim DBpath As String

DBpath = "***" 'accdbファイルパス

Dim password As String
password = "AAAA" 'InputBox("パスワードを入力してください")
If password = "" Then Exit Sub

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成

adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & "\" & strFileName & ";" & _
"Jet OLEDB:Database Password=" & password & ";" 'Accessファイルに接続

Dim adoRS As Object 'ADOレコードセットオブジェクト
Set adoRS = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

Dim day As String
day = Worksheets("MENU").Cells(1, 2)

Dim sheet_name As String
sheet_name = Worksheets("MENU").Cells(4, 4)

With adoRS
.Open sheet_name, adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く"

Dim adoCON As New ADODB.Connection

.AddNew

!ref_serial = Worksheets(sheet_name).Cells(2, 1)

!result1 = Worksheets(sheet_name).Cells(2, 3)
!result2 = Worksheets(sheet_name).Cells(2, 4)

!result_update = Worksheets("MENU").Cells(1, 2)

.update

.Close 'レコードセットのクローズ

End With

adoCn.Close 'コネクションのクローズ

Set writeSht = Nothing
Set adoRS = Nothing
Set adoCON = Nothing

End Sub

更新のマクロ
Sub renew()

Dim strFileName As String
strFileName = "Machine.accdb"

Dim DBpath As String

DBpath = "***" 'accdbファイルパス

Dim password As String
password = "AAAA" 'InputBox("パスワードを入力してください")
If password = "" Then Exit Sub

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成

adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & "\" & strFileName & ";" & _
"Jet OLEDB:Database Password=" & password & ";" 'Accessファイルに接続

Dim day As String

day = Worksheets("MENU").Cells(1, 2)

Dim sheet_name As String

sheet_name = Worksheets("MENU").Cells(4, 4)

Dim strSQL As String
strSQL = _
"UPDATE '" & sheet_name & "' " & _
"SET " & _
ws.Worksheets(sheet_name).Range("C1").Value & "=" & ws.Worksheets(sheet_name).Range("C2").Value & "," & _
ws.Worksheets(sheet_name).Range("D1").Value & "=" & ws.RWorksheets(sheet_name).ange("D2").Value & "," & _
"WHERE ref_serial =" & ws.Worksheets(sheet_name).Range("A2").Value

adoCn.Execute strSQL 'SQLを実行
adoCn.Close 'コネクションのクローズ
Set adoCn = Nothing 'オブジェクトの破棄

End Sub

回答No.1

m3_maki · ベストアンサー

"UPDATE '" & sheet_name & "' " & _

を

"UPDATE [" & sheet_name & "] " & _

にしてみたら？

お礼

Dim strSQL As String
strSQL = _
"UPDATE '" & sheet_name & "' " & _
"SET " & _
ws.Worksheets(sheet_name).Range("C1").Value & "=" & ws.Worksheets(sheet_name).Range("C2").Value & "," & _
ws.Worksheets(sheet_name).Range("D1").Value & "=" & ws.RWorksheets(sheet_name).ange("D2").Value & "," & _
"WHERE ref_serial =" & ws.Worksheets(sheet_name).Range("A2").Value

上記で変数設定の所を全て値にしたらエラー発生しなくなったので、
１つずつ変数に変更していきました。
なお、access側でIDがオートナンバーで主キーになっており、
この主キーをwhereで設定する必要があったので、構成を変更しました。
最終、下記でエラー無く動作できました。

Dim value1 As Single
Dim value2 As Single

Dim ID As Long
Dim ref_serial As String

ID = Worksheets("result").Range("A2").value

ref_serial = Worksheets("result").Range("B2").value

value1 = Worksheets("result").Range("C2").value
value2 = Worksheets("result").Range("D2").value

Dim strSQL As String
strSQL = "UPDATE [" & sheet_name & "] SET result1 ='" & value1 & "' ,result2 ='" & value2 & "' ,ref_serial ='" & ref_serial & "' WHERE ID = " & ID

ちなみにUPDATE [" & sheet_name & "]の所はUPDATE '" & sheet_name & "'とするとクエリエラーとなり、
SET以降は[でなくても動作するのが？でした。
[" & sheet_name & "] が大変参考になりました。
ありがとうございます。

補足

回答ありがとうございます。
やってみましたが、状況変わらずオブジェクトが必要です。
のエラー発生です。

回答No.2

piroin654

「エラーが発生するのは」の以下の7行目の

ws.RWorksheets(sheet_name).ange("D2").Value & "," & _

の

　　ange

は、

　　Range

では？

補足

ご指摘ありがとうございます。ws.RWorksheets(sheet_name).ange("D2").Value & "," & _
→
ws.RWorksheets(sheet_name).Range("D2").Value & "," & _
にしましたがエラー発生変わらずでした。