none
エクセルVBAでアクセスにテーブル作成したい RRS feed

  • 質問

  • Office 2010

    エクセルVBAでアクセスの操作をしています。

    素人が忘年会用にデータをいじっています。
    詳しくないのでお手柔らかにお願いします。

    『忘年会用.mdb』というmdbファイルの中に
    下記のようなテーブル『名簿』があります。

    社員番号,氏名,部署
    A001,鈴木 一郎,MOS
    A002,田中 次郎,EOS
    A003,高橋 三郎,ZOW
    A004,田中 良子,EOS
    A005,佐藤 一郎,EOS
    A006,田中 花子,EOS

    氏名で 一 がある人物を抽出して
    新規テーブルに抜き出して新規テーブルを作成したいのですが
    エクセルVBAからだとうまくできません。

    アクセスのデザインビュー、SQLビューではエクセルVBAで
    作成したクエリでテーブルは作成され、データも反映されます。

    何か根本的に間違っているのでしょうか?

    下記がコードになります。

        Dim WP        As String   '指定ファイルのパス等

        Dim objTbl       As New ADOX.Table
        Dim objCat       As New ADOX.Catalog     'CreateObject("ADOX.Catalog")
        Dim Con          As New ADODB.Connection
        Dim Rst          As New ADODB.Recordset
        Dim Cmd          As New ADODB.Command

        WP = ThisWorkbook.Path & "\忘年会用.mdb"

        Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & WP

        Con.Open

        objCat.ActiveConnection = Con

        Cmd.CommandText = "SELECT 名簿.社員番号, 名簿.氏名, 名簿.部署 INTO 一 FROM 名簿 " & _
            "WHERE ((([名簿].氏名) Like " & """" & "*" & "一" & "*" & """" & "));"
        objCat.Views.Append "一検索", Cmd
        Set Cmd = Nothing
        Rst.Open "一検索", Con, adOpenKeyset, adLockOptimistic
        Set Cmd = Nothing


    『一検索』のクエリでテーブルが作成されるのですが
    作成されたテーブルには選択されたデータが反映されていません。

    何が原因なのでしょうか。

    よろしくお願いします。

    2014年12月19日 8:40

すべての返信

  • 「一検索」というViewを追加されているのが気になりました。2回目、3回目など既に「一検索」というViewが存在していたらまずい気がします。
    ご質問の件に関し、原因は調査していませんが、Viewを使うのではなく、以下のようにコマンドで処理されるのがスマートだと思います。

    Excel-VBA-からAccessの操作を行うマクロ
    http://eprostation.jpn.org/code/vba/vba0001.html


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年12月19日 9:26
    モデレータ
  • お早い返信ありがとうございます。

    クエリに関しては使用後に

        objCat.Views.Delete "一検索"

    といった感じのコードを入れて削除しています。

    基本的にクエリは

    追加、実行、削除

    という工程を繰り返しています。

    上記方法では具合が悪いのでしょうか?


    2014年12月19日 9:39
  • ようやく時間が取れました。遅くなり申し訳ありません。

    さて、試してみたところ再現しました。原因は、ADODB経由だとLikeの*は%を使うということでした。ADODBで用いるSQLはAccessのSQLとは完全に同じではありません。よって、以下のようにするとうまく行くと思います。

    Cmd.CommandText = "SELECT 名簿.社員番号, 名簿.氏名, 名簿.部署 INTO 一 FROM 名簿 " & _
             "WHERE ((([名簿].氏名) Like " & """" & "%" & "一" & "%" & """" & "));"

    ところで、「一」というテーブルを作成するだけであれば、わざわざView経由にする必要はなく、直接以下のようにSQLを発行すれば事足ります。

    Dim WP           As String   '指定ファイルのパス等
    
    Dim objTbl       As New ADOX.Table
    Dim objCat       As New ADOX.Catalog     'CreateObject("ADOX.Catalog")
    Dim Con          As New ADODB.Connection
    Dim Rst          As New ADODB.Recordset
    Dim Cmd          As New ADODB.Command
    
    WP = ThisWorkbook.Path & "\忘年会用.mdb"
    
    Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & WP
    
    Con.Open
    
    objCat.ActiveConnection = Con
    
    Cmd.CommandText = "SELECT 名簿.社員番号, 名簿.氏名, 名簿.部署 INTO 一 FROM 名簿 " & _
         "WHERE ((([名簿].氏名) Like " & """" & "%" & "一" & "%" & """" & "));"
    '   objCat.Views.Append "一検索", Cmd
    '   Rst.Open "一検索", Con, adOpenKeyset, adLockOptimistic
    Cmd.ActiveConnection = Con
    Cmd.Execute
    Set Cmd = Nothing



    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年12月20日 15:08
    モデレータ