Microsoft Access 掲示板

Excelから、ACCESSの更新について

20 コメント
views
4 フォロー

Excelから、ACCESSへ更新するコードです

更新合成キーを作成して、ACCESSを更新しようと思っています。

更新合成キーは、00-1000-08-202410⇒このような文字列です。

仕入を更新したいのです。

仕入は、数値型です。

下記のコードを利用すると、更新されるところと、更新されないところがあります。

更新合成キーが異なるのかと思い、VLOOKUP関数で、Excel上で検証しましたが、VLOOKUP関数では反応します。

ExcelのデータをACCESSに貼り付けて検証しました。

更新合成キーで結合しましたが、反応します。

下記のコードで、おかしい所はあるでしょうか?

お知恵をお貸しください。

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


Sub 単価転送()
    
    Dim DBpath As String
    Dim adoCn As Object
    Dim strSQL As String
    Dim henDB As String
    Dim i As Long
    Dim ws_2 As Worksheet
    Set ws_2 = Worksheets("転送用シート")
    henDB = Worksheets("Sheet1").Range("D1")
    
    Set adoCn = CreateObject("ADODB.Connection")
    DBpath = ThisWorkbook.Path & henDB  ' パスを確認
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";"
    
    With ws_2
     
     i = 2

     Do Until .Cells(i, "K") = ""
         strSQL = "UPDATE Q_単価更新用 SET 仕入=" & .Cells(i, "I") & " WHERE  更新合成キー ='" & .Cells(i, "K").Value & "'"
         

         adoCn.Execute strSQL
         i = i + 1
    Loop
    End With
    adoCn.Close
    Set adoCn = Nothing

    
    
End Sub

しん
作成: 2024/10/28 (月) 21:57:07
通報 ...
1

下記のコードを利用すると、更新されるところと、更新されないところがあります。

    Do Until .Cells(i, "K") = ""
         strSQL = "UPDATE Q_単価更新用 SET 仕入=" & .Cells(i, "I") & " WHERE  更新合成キー ='" & .Cells(i, "K").Value & "'"

         adoCn.Execute strSQL
         i = i + 1
    Loop

  1. 途中のデータ行の K 列の値が Empty 値 / 空文字列 であるため、全てのデータ行を網羅し切る前にループ処理が終了している。

  2. 途中のデータ行の I 列の値が数値(もしくは数値データに変換可能な文字列)ではないため、SET 句の右辺が抜けて構文エラーが発生している。

  3. 単純に、フィールドの[更新合成キー]の値が K 列の値と一致するレコードが[Q_単価更新用]側に存在していない。

以前ご質問された件を踏まえると、恐らく 3 である可能性の方が高いのではないかと推察します。

Sub UpdatePrices()
    
    Dim wsSource As Worksheet
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    
    Set wsSource = Worksheets("転送用シート")
    
    With wsSource
        lngFirstRow = 2
        lngLastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        If lngFirstRow > lngLastRow Then
            Debug.Print "ワークシート[" & wsSource & "]にデータ行がありません。"
            Set wsSource = Nothing
            Exit Sub
        End If
    End With
    
    Dim adoCn As Object     'ADODB.Connection
    Dim strDbName As String
    Dim strTargetPath As String
    
    Set adoCn = CreateObject("ADODB.Connection")
    
    strDbName = Worksheets("Sheet1").Range("D1")
    strTargetPath = ThisWorkbook.Path & strDbName
    Debug.Print strTargetPath
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strTargetPath & ";"
    
    Dim adoCmd As Object    'ADODB.Command
    Dim strSQL As String
    
    Set adoCmd = CreateObject("ADODB.Command")
        
    With adoCmd
        Set .ActiveConnection = adoCn
        .CommandType = 1    'adCmdText
        strSQL = "PARAMETERS [SearchKey] TEXT(255), [UpdateValue] INT;" & vbCrLf & _
                 "UPDATE [Q_単価更新用] AS q1" & _
                 " SET q1.[仕入]=[UpdateValue]" & _
                 " WHERE q1.[更新合成キー]=[SearchKey];"
        Debug.Print strSQL
        .CommandText = strSQL
        .Parameters.Append .CreateParameter("SearchKey", 202, 1, 255)   'adVarWChar, adParamInput
        .Parameters.Append .CreateParameter("UpdateValue", 3, 1)        'adInteger, adParamInput
    End With
        
    Dim lngRow As Long
    Dim lngRecordAffected As Long
    Dim lngAffectedTotal As Long
    Dim rngKeyCell As Range
    Dim rngValueCell As Range
    
    lngAffectedTotal = 0
    
    For lngRow = lngFirstRow To lngLastRow
        
        Set rngKeyCell = wsSource.Cells(lngRow, "K")
        Set rngValueCell = wsSource.Cells(lngRow, "I")
        lngRecordAffected = 0
        
        If (rngKeyCell.Value <> "") And (IsNumeric(rngValueCell.Value) = True) Then
            adoCmd.Parameters("SearchKey").Value = rngKeyCell.Value
            adoCmd.Parameters("UpdateValue").Value = CLng(rngValueCell.Value)
            adoCmd.Execute lngRecordAffected
        End If
        
        If lngRecordAffected = 0 Then
            Debug.Print lngRow & "行目のデータは更新対象になりませんでした。"
            Debug.Print vbTab & rngKeyCell.Address(False, False) & "セルの値: " & rngKeyCell.Value
            Debug.Print vbTab & rngValueCell.Address(False, False) & "セルの値: " & rngValueCell.Value
        ElseIf lngRecordAffected > 1 Then
            Debug.Print lngRow & "行目の更新合成キー(" & rngKeyCell.Value & ")に該当するレコードが " & _
                        lngRecordAffected & " 件更新されました。"
        End If
        
        lngAffectedTotal = lngAffectedTotal + lngRecordAffected
        
        Set rngKeyCell = Nothing
        Set rngValueCell = Nothing
    
    Next
    
    Set adoCmd = Nothing
    adoCn.Close
    Set adoCn = Nothing
    
    Debug.Print "全部で " & lngAffectedTotal & " 件のレコードが更新されました。"
    
End Sub

とりあえず、以上のマクロを実行してみて
イミディエイトウィンドウに出力された結果を
確認してみて下さい。

2
しん 2024/10/29 (火) 17:53:37 f87c1@9fff4

sk様 いつもありがとうございます。

更新結果は、0件でした。

更新合成キーが存在しないんですね。

3
しん 2024/10/29 (火) 18:11:04 f87c1@9fff4

sk様
更新結果は、イミディエイトウィンドウでは、0件でしたが、実際は、3件更新されました

一例ですが、

ACCESS上の更新合成キー 126-1200-30-202410
Excel上の更新合成キー  126-1200-30-202410

目検で同じように見えますし、EXACT関数で確認しても、Trueとなり、同じように見えます。

これがなぜ更新できないのかが、わからないでです。

4
しん 2024/10/29 (火) 18:16:51 f87c1@9fff4

一応、ACCESSのクエリ上で確認しました。


正誤:iif([Q_単価更新用].[更新合成キー]=[転送用シート].[更新合成キー],"〇","×")

〇の判定は、184個あり、私の認識では、184個更新されると思うのですが、そんな単純な話ではないのでしょうか?

5
しん 2024/10/29 (火) 18:33:16 f87c1@9fff4

>>途中のデータ行の K 列の値が Empty 値 / 空文字列 であるため、全てのデータ行を網羅し切る前にループ処理が終了している。

For~Nextで検証しましたが、更新されませんでした。

I列は、

Sub 単価()
Dim ws_2 As Worksheet
Set ws_2 = Worksheets("転送用シート")
maxR = Cells(Rows.Count, "A").End(xlUp).Row
maxR2 = ws_2.Cells(Rows.Count, "A").End(xlUp).Row

hajimeGyo = ActiveCell.Row
hajimeRetu = ActiveCell.Column

Range(Cells(hajimeGyo + 1, hajimeRetu), Cells(maxR, ActiveCell.Column)).Copy
ws_2.Cells(2, "I").PasteSpecial xlPasteValues

For i = 2 To maxR2


If ws_2.Cells(i, "I") = "" Then
ws_2.Cells(i, "I") = 0

End If
Next i

End Sub

このようになコードです

2024/10/1を202410に変更しています。

6
しん 2024/10/29 (火) 19:35:08 f87c1@9fff4

すいません
間違えました。
I列は、少数点の数値です。
152.2などです
×2024/10/1を202410に変更しています⇒間違えました

7
hiroton 2024/10/30 (水) 09:07:55 1ba09@f966d

実際に実行されたSQLを確認してみては?

Execute メソッド (ADO Connection)

Dim RecordsAffected As Long
adoCn.Execute strSQL, RecordsAffected
If RecordsAffected = 0 Then
    Debug.Print strSQL
    Stop
End If
8
しん 2024/10/30 (水) 10:04:35 f87c1@9fff4

hiroton様

コード提示ありがとうございます。

私、ヘルプも読んだのですが、理解ができません。

このコードは、どこに差し込むのでしょうか?

また独立で、作動させるのでしょうか?

お手すきの時に回答いただけたら幸いです。
よろしくお願いいたします。

10
hiroton 2024/10/30 (水) 10:40:44 1ba09@f966d >> 8

SQLを実行している部分を書き換えます

Sub 単価転送()
    
    Dim DBpath As String
    Dim adoCn As Object
    Dim strSQL As String
    Dim henDB As String
    Dim i As Long
    Dim ws_2 As Worksheet
    Set ws_2 = Worksheets("転送用シート")
    henDB = Worksheets("Sheet1").Range("D1")
    
    Set adoCn = CreateObject("ADODB.Connection")
    DBpath = ThisWorkbook.Path & henDB  ' パスを確認
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";"
    
    With ws_2
     
     i = 2

     Do Until .Cells(i, "K") = ""
         strSQL = "UPDATE Q_単価更新用 SET 仕入=" & .Cells(i, "I") & " WHERE  更新合成キー ='" & .Cells(i, "K").Value & "'"
         
'// ここを調査用のコードに書き換える
'         adoCn.Execute strSQL

Dim RecordsAffected As Long
adoCn.Execute strSQL, RecordsAffected
If RecordsAffected = 0 Then
    Debug.Print strSQL
    Stop
End If
'//ここまで

         i = i + 1
    Loop
    End With
    adoCn.Close
    Set adoCn = Nothing
End Sub

RecordsAffected

省略可能です。 この操作の影響を受けたレコード数をプロバイダーが返す長整数型 ( Long ) の値です。

ヘルプによればRecordsAffectedにUPDATEで更新されたレコード数が入るはずなので、期待通り更新されれば「1」、更新されなければ「0」となっているはずなので、「0」の時だけイミディエイトウィンドウにstrSQLの値を書き出し、つづくStop行でVBAの実行が中断されます

または、Stop行は記述せずに、iの値と合わせてすべて処理させてから確認してもいいでしょう

Dim RecordsAffected As Long
adoCn.Execute strSQL, RecordsAffected
If RecordsAffected = 0 Then
    Debug.Print i; strSQL
End If

イミディエイトウィンドウの表示は100行までなので、「うまく更新されないデータ」が100件を超える場合は注意してください

9

更新結果は、イミディエイトウィンドウでは、0件でしたが、実際は、3件更新されました

それは私の例示したマクロをそっくりそのままコピーして実行したのではなく、ご自身が作成されたマクロを部分的に修正されたものを実行されたからではないでしょうか。

例えば、Execute メソッドの呼び出し時に引数 RecordAffected に Long 型の変数( lngRecordAffected )を渡していない、RecordAffected を介して取得した更新件数を別の Long 型の変数( lngAffectedTotal )に累計加算する処理を記述していない等。

更新合成キーが異なるのかと思い、VLOOKUP関数で、Excel上で検証しましたが、VLOOKUP関数では反応します。

目検で同じように見えますし、EXACT関数で確認しても、Trueとなり、同じように見えます。

比較/更新対象となるのはあくまで Access 側のテーブル/フィールドですので、Excel ワークシート側のデータのみを検証しても正確なことは判らないでしょう。

正誤:iif([Q_単価更新用].[更新合成キー]=[転送用シート].[更新合成キー],"〇","×")

その部分だけを示されても、検証用のクエリが適切に作られているかどうか評価のしようがありません。

また[Q_単価更新用]という名前から推察した限り、UPDATE 文において呼び出されているのはテーブルではなく選択クエリのように見えますが、もし[Q_単価更新用]が選択クエリであるならば、それが具体的にどのようなクエリであるか、実際に更新対象となるのはどのテーブルであるか、そのテーブルの各フィールド(特に[更新合成キー])はどのように定義されているのか、といったことも検討すべき問題となります。

[Q_単価更新用]がテーブルではなく選択クエリであるならば、とりあえずそのクエリの SQL ビューの内容をそのまま明示して下さい。

仕入は、数値型です。

I列は、少数点の数値です。

Access 側のテーブルにおけるフィールド[仕入]のフィールドサイズ(十進型である場合は[精度]および[小数点以下保持桁数]プロパティ)はどのように設定されているのでしょうか。

11
hiroton 2024/10/30 (水) 10:43:56 1ba09@f966d >> 9

もともと「Sub 単価転送()」とプロシージャを設定しているのにそっくりそのままコピーして実行したのではないからだというのはちょっと酷だと思いますよ

12

単価転送とは別のデバッグ用マクロとして示したものですので、そっくりそのままコピーして実行していただかないとむしろ困ります。

少なくとも、実際に更新されたレコード件数が本当に 3 件であるならば、イミディエイトウィンドウにもそのように示されるはずであり、挙動としてあまりに不自然です。

14

単価転送とは別のデバッグ用マクロとして示したものですので、そっくりそのままコピーして実行していただかないとむしろ困ります。

ならば、そのように実行できるように手順を示すべきでしょう
自分の思うとおりに動いてくれなかったことの責任を相手に求めるのは良くないですよ
これは質問者であるとか回答者であるとかに関係することではありません

続く文に関しては特にhirotonが指摘している件とは無関係なのでノーコメントです

13
しん 2024/10/30 (水) 10:59:50 f87c1@9fff4

更新を実行したいクエリのSQL文は、下記の通りです。

SELECT ID, 
仕入コード,
仕入先,
油種コード,
単価_ランク, 
単価_ランク_コード, 
直近3ヶ月, 
仕入, 
Left(Format([直近3ヶ月],"yyyymmdd"),6) AS 日付コード,
 [仕入コード] & "-" & [油種コード] & "-" & [単価_ランク_コード]& "-" &[日付コード] AS 更新合成キー,
FROM MT_検索テーブル;

仕入のフィールドの型は、
単精度浮動小数点型です。

昨日からのアドヴァイスをいただき、検証しました。

実際に利用している単価更新キーは

00-0000-00-202410

このような文字列です。

SQL文に記載のある

Left(Format([直近3ヶ月],"yyyymmdd"),6)

この値をつけると更新できませんが、この値をつけなければ更新できます。

00-0000-00 ⇒更新できます。

sk様のコードでも確認して、更新件数は合致します。

Excel上のマクロは下記の通りです。

.Range(.Cells(2, "J"), .Cells(maxR, "J")) = Format(ActiveCell.Value, "yyyymm")

疑問なのですが、

Left(Format([直近3ヶ月],"yyyymmdd"),6)
.Range(.Cells(2, "J"), .Cells(maxR, "J")) = Format(ActiveCell.Value, "yyyymm")

このコードを実行すると、202410になりますが、ExcelとACCESSでは、異なる値と判断されるのでしょうか?

15
Left(Format([直近3ヶ月],"yyyymmdd"),6) AS 日付コード

Access側のテーブル[MT_検索テーブル]のフィールド[直近3ヶ月]のデータ型は日付/時刻型なのでしょうか。

.Range(.Cells(2, "J"), .Cells(maxR, "J")) = Format(ActiveCell.Value, "yyyymm")

Excel 側のワークシート[転送用シート]において、K 列の各セルは J 列を含む他の列のセルを参照する数式セルなのでしょうか。

[仕入コード] & "-" & [油種コード] & "-" & [単価_ランク_コード]& "-" &[日付コード] AS 更新合成キー

また、[仕入コード]、[油種コード]および[単価_ランク_コード]に相当する列は、Excel 側のワークシート[転送用シート]に存在していないのでしょうか。

16
しん 2024/10/30 (水) 12:07:52 f87c1@9fff4

sk様返信ありがとうございます。

>>Access側のテーブル[MT_検索テーブル]のフィールド[直近3ヶ月]のデータ型は日付/時刻型なのでしょうか。

日付/時刻型です。

>>Excel 側のワークシート[転送用シート]において、K 列の各セルは J 列を含む他の列のセルを参照する数式セルなのでしょうか。

.Cells(i, "K") = .Cells(i, "A") & "-" & .Cells(i, "E") & "-" & .Cells(i, "G") & "-" & .Cells(i, "J")

>>
また、[仕入コード]、[油種コード]および[単価_ランク_コード]に相当する列は、Excel 側のワークシート[転送用シート]に存在していないのでしょうか。

存在しています。
Cells(i, "A")⇒仕入コード
Cells(i, "E")⇒油種コード
Cells(i, "G")⇒単価ランクコード
Cells(i, "J")⇒日付コード

です

20

[MT_検索テーブル]のフィールド[直近3ヶ月]のデータ型

日付/時刻型です。

Cells(i, "A")⇒仕入コード
Cells(i, "E")⇒油種コード
Cells(i, "G")⇒単価ランクコード
Cells(i, "J")⇒日付コード

.Range(.Cells(2, "J"), .Cells(maxR, "J")) = Format(ActiveCell.Value, "yyyymm")

つまり、

画像1

Access 側の[MT_検索テーブル]の各レコードの更新前の状態がこうであるのに対し、

画像2

Excel 側の[転送用シート](恐らく実際は別のワークシート)の状態がこうだとして、

画像3

そのブックのいずれかのワークシートの「任意の日付が入力されているセル」が
選択されている(アクティブセルになっている)状態でマクロを実行したら

画像4

Access 側の[MT_検索テーブル]の各レコードがこうなればよい、ということですか。

17
しん 2024/10/30 (水) 22:56:24 f87c1@9fff4

クエリでは、更新できませんでしたが、テーブルに変更すると、更新ができました。
理由は不明です。

sk様 いつもありがとうございます。私の不手際で、気分を害されたら、お詫びします。
また、検証用のコードありがとうございました。
このようなコードで、検証できるように、精進します。

hiroton様

いつも回答ありがとうございます。
hiroton様の検証用のコードも理解できませんでしたので、理解できるように精進します。

お騒がせして申し訳ありません。

ありがとうございました。

18
hiroton 2024/10/31 (木) 09:59:51 573a8@f966d

hirotonの回答は「実際に実行されたSQLを表示する」だけです
続く条件判定により「『更新されない』時だけSQLが表示される」ことを期待しています

結果を見てできることを質問者さんが考えてもいいですし、そのまま結果を提示してもらえれば問題解決に一歩近づくだろうという検証をするための情報を生み出すコードです

このコードの実行結果はいくつか想定されます。たとえば

  • 特に変化が見られず、処理が終了した
  • Stop行が実行され(処理が中断し)、SQLが表示された

大きく分ければこの二つしかありませんが、SQLが表示されれば

  • SQLが表示されたが、EXCELの内容から期待したSQLになっていなかった
  • SQLが表示され、EXCELの内容から期待したSQLが表示された

のように、内容を深堀することもできます。当然、「SQLが表示されることなく処理が終了した」の結果ならその先の想定も無意味なものになるので、hirotonの最初のアプローチとしては「実際に実行されたSQLを確認してみては?」で止まっているわけです
回答で想定されているパターンを網羅するのは質問に対して不要な情報であふれることになりトピックの質が落ちますし、1つの正解以外は徒労というのもhirotonはやりたくありません


自己解決した内容をみるに、実行されるSQLは期待した文字列だったものと思われます。SQLが正しく実行されるかどうかのほうに問題があり、つまり、EXCELからの読み込み部分は問題ないのだと確認が取れるでしょう

ならば、EXCELのデータやその取り込み部分をいくら深堀したところで問題は解決しません

問題解決のために何が必要か?複数のアプローチを検討・実施することが問題解決の近道になります

19
しん 2024/10/31 (木) 11:09:44 f87c1@9fff4

hiroton様

貴重なご意見ありがとうございました。

>>問題解決のために何が必要か?複数のアプローチを検討・実施することが問題解決の近道になります

その通りだと思います。
今後に向けて精進します。