[[20140813070717]] 『EXCELからACCESSテーブル追加、更新、削除』(MARU) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『EXCELからACCESSテーブル追加、更新、削除』(MARU)

 A列   B列   C列   D列   E列   F列   G列   H列   I列   J列・・・DV列
 001   TEST1  Parts1  1      2      3      4      5      6      7
 001    TEST1  Parts1  8      9     10     11     12     13     14
 001    TEST1  Parts1 15     16     17     18     19     20     21
 002    TEST2  Parts2  1      2      3      4      5      6      7 
 002    TEST2  Parts2  8      9     10     11     12     13     14
 003    TEST3  Parts3  1      2      3      4      5      6      7

 以上のようなExcelデータがあります。
 EXCELと同じ内容のACCESSテーブル(フィールド名、フィールド数同じ)
 を更新したい。
    
 C列の条件(Parts1)があえば、ACCESSのParts1の
 データを置き換えたい。(削除して追加)
 同じPartsナンバーが存在しなければ、追加する。
 (C列の最終行まで存在チェック) 

 宜しくお願いします。 

< 使用 Excel:Excel2010、使用 OS:Windows7 >


・MDBのファイル名、テーブル名は何?
・主キーは何? C列のようですが、重複していますが?
(ご提示の例の場合、Access側は3レコードということ?)

とりあえず、「Excel VBA ADO」等をキーに検索して頂ければ、必要な命令は判るはず。
あと、条件が合えばレコード削除してから追加する、と書いてますが、普通は更新するかと。
(???) 2014/08/13(水) 08:48


 スペック管理.mdb
 T_スペック

 主キーの設定は行っておりません。
 >(ご提示の例の場合、Access側は3レコードということ?) 
 同じ3レコードではなく、異なります。
  
 C列(品番)のスペック情報がD〜DV列にセットされています。

あああああ
  
(MARU) 2014/08/13(水) 21:08


 Accessではなく、Excel内で処理するほうが
 よさそうなので、もう少し考え直します。
 ありがとうございました。

(MARU) 2014/08/14(木) 09:35


拡張子がmdbということは、古いAccessで作成されたものを更新ですかね?
とりあえず登録例を書いておきますので、参考まで。

 Sub test()
    Dim CN As Object
    Dim RS As Object
    Dim strSQL As String
    Dim cMDB As String
    Dim i As Long
    Dim j As Long
    Dim jMax As Long

    jMax = Cells(1, Columns.Count).End(xlToLeft).Column

    cMDB = "C:\tmp\スペック管理.mdb"
    Set CN = CreateObject("ADODB.Connection")
    CN.Open "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cMDB & ";"
    Set RS = CreateObject("ADODB.Recordset")

    For i = 2 To Cells(Rows.Count, "C").End(xlUp).Row
        strSQL = "SELECT * FROM T_スペック WHERE C列='" & Cells(i, "C").Text & "';"
        Set RS = CN.Execute(strSQL)

        If RS.EOF = True Then
            strSQL = "INSERT INTO T_スペック VALUES( """ & Cells(i, "A").Text & """"
            For j = 2 To jMax
                strSQL = strSQL & ", """ & Cells(i, j).Text & """"
            Next j
            strSQL = strSQL & ");"
            Set RS = CN.Execute(strSQL)
        Else
            RS.Close
            RS.Open strSQL, CN, 1, 3
            For j = 1 To jMax
                RS.Fields(j - 1).Value = Cells(i, j).Text
            Next j
            RS.Update
            RS.Close
        End If
    Next i

    Set RS = Nothing
    CN.Close
    Set CN = Nothing
End Sub
(???) 2014/08/14(木) 11:09

 ありがとうございました。
 office2003で作成したAccessです。
 確認しましたが、下記エラーとなりました。
 Set RS = CN.Execute(strSQL)
 実行時エラー2147217913 抽出条件でデータ型が一致しません
 今回のExcelにシート(db)追加し、そのシートに
 Accessテーブルのデータをコピーして、Excel内で完結したほうが
 処理がスムーズに進められるかと思ったり・・・。

 Excelで、wk(シート)とdb(シート)のC列を比べ、
 等しければdbのデータ(行)を削除して、wkのデータをdbへ行追加する
 等しくなければ、wkのデータをdbへ行追加(削除処理は無し)
 下記に行追加の処理を加える為には、どのように記述すればよいでしょうか?
 最初の質問から、変更になり申し訳ありません。
 宜しくお願いします。
  

 Dim ws1 As Worksheet
 Dim ws2 As Worksheet
 Dim lastRow1 As Long
 Dim r As Long

 Set ws1 = Sheets("db")
 Set ws2 = Sheets("wk")
 lastRow1 = ws1.Range("C" & Rows.Count).End(xlUp).Row
 For r = lastRow1 To 2 Step -1
 If WorksheetFunction.CountIf(ws2.Columns("C"), ws1.Range("C" & r)) > 0 Then
 ws1.Rows(r).Delete
 End If
 Next

  
(MARU) 2014/08/14(木) 23:39


 作業列を作って
   =COUNTIF(wk!C:C,C1)
 こんな式を入れて 0以外の行を削除。
 後ろに wkシートのデータを貼り付け。

 って作りにしてみてはどうですか?
  
(HANA) 2014/08/18(月) 11:00

DBの個々のフィールド設定が不明のため、サンプルソースではすべてテキスト型にして扱いました。
具体的には、VALUESを指定する際に、すべて前後にダブルクォートを付けています。
もし、数値型や日付型が混在しているならば、そこはダブルクォートを付けない工夫が必要です。

DBは一切使わない変更案ですが、既存を削除ではなく、全部削除してから、有効なものを追記してはいかが?

(1)結果シートの過去データを、タイトル行を除いてすべて削除。
(2)元シートを下から処理。同じ項目が無い場合のみ、1行コピペ。
(3)全データ処理後、必要であれば並べ替え。
(???) 2014/08/18(月) 13:54


 >作業列を作って〜〜
 って書きましたが、書いておられるコードで
 新しいデータと重複するデータは削除されるので
 すべて終わった後に、新しいデータをコピペすれば良さそうですが。

 既存のコードに
  「後ろに wkシートのデータを貼り付け。」
 を追加。
  
(HANA) 2014/08/18(月) 14:47

 アドバイスをいただき、その通り行ってみました。
 EXCELでは、うまくいきました。ありがとうございます。
 ACCESSは利用しない運用で考えておりましたが、
 技術習得の為に、教えていただけませんでしょうか?
 >具体的には、VALUESを指定する際に、すべて前後にダブルクォートを付けています。 
 >もし、数値型や日付型が混在しているならば、そこはダブルクォートを付けない工夫が必要です。 
 修正は複雑でしょうか?
 
 
(MARU) 2014/08/18(月) 16:02

横方向はDV列まであるという事なので、1フィールドずつ固定でSQL文連結…、というのは、やってられませんよね。
複雑ではないですが、量が問題です。なので、簡単に修正できるかどうかは、フィールド構成次第です。

たとえば、特定の列が少しだけ文字列以外ならば、j のループ中にIf文でダブルクォートを付けないようにする。
多数の列で、様々な型が入り乱れている場合、フィールド名に規則性、例えば、数値型はiで始まる等、
一定の法則があれば、これを判定してダブルクォートを付ければ良いでしょう。
(???) 2014/08/18(月) 16:27


 数値型は、5つ
 日付型は、2つです。
 他は、すべてテキスト型です。 
 一度試してみます。
 この度は、ありがとうございました。
 
(MARU) 2014/08/18(月) 21:46

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.