• ベストアンサー
  • すぐに回答を!

エクセルでアクセスデータベースを操作方法

見積提出表をエクセルで作成しており、データの数が多く最近重くなってきました。 そこでこれらのデータをアクセスに写し、エクセル編集できないかと試行錯誤しております。 エクセルVBAでアクセスデータベースの抽出し、エクセルで編集また新規入力。 そしてまた、エクセルVBAでアクセスデータベースを編集また追加をしたいのですが・・・ 抽出まではうまくいきました。(下記VBAで) Private Sub CommandButton1_Click()  Dim rcs As ADODB.Recordset Dim cnStr As String, sqlStr As String    namae = TextBox1.Text '一覧のクリア、始点へ移動 Range("B3:S100").Select Selection.ClearContents Range("K3:K100") = "=ROUNDUP(J3/1.05,0)" Range("M3:M100") = "=K3-L3" Range("N3:N100") = "=IF(L3="""","""",ROUNDDOWN(M3/K3,4))" Range("B3").Select 'データベースの保存場所 cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DB案\見積提出状況.mdb" '検索条件 sqlStr = " SELECT * FROM 状況表 WHERE お客様名 LIKE '%" & namae & "%' " 'データベースの読込、コピー、閉じる Set rcs = New ADODB.Recordset rcs.Open Source:=sqlStr, ActiveConnection:=cnStr, CursorType:=adOpenStatic ActiveCell.CopyFromRecordset rcs rcs.Close Set rcs = Nothing Range("G3").Activate '検索フォームを閉じる Unload UserForm2 End Sub この後、エクセルで編集(データを一部変更)また、新規入力しアクセルのデータベースを変更しようと、 Private Sub CommandButton2_Click() Dim rcs As ADODB.Recordset Dim cnStr As String, sqlStr As String namae = TextBox1.Text Range("B3").Select 'データベースの保存場所 cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DB案\見積提出状況.mdb" '検索条件 sqlStr = " SELECT * FROM 状況表 WHERE お客様名 LIKE '%" & namae & "%' " 'データベースの読込、編集、登録、閉じる Set rcs = New ADODB.Recordset rcs.Open Source:=sqlStr, ActiveConnection:=cnStr, CursorType:=adOpenStatic, LockType:=adLockPessimistic  Do Until rcs.EOF rcs("チェック日付入力") = Range("C3") rcs("追加契約日") = Range("D3") rcs("状況") = Range("E3") rcs("日付") = Range("F3") rcs("お客様名") = Range("G3") rcs("工事名") = Range("H3") rcs("種類") = Range("I3") rcs("販売金額(税込)") = Range("J3") rcs("販売金額(税抜)") = Range("K3") rcs("実行金額(税抜)") = Range("L3") rcs("利益金額(税抜)") = Range("M3") rcs("利益率") = Range("N3") rcs("営業担当") = Range("O3") rcs("支店・営業所") = Range("P3") rcs("設計担当(見積担当)") = Range("Q3") rcs("部署") = Range("R3") rcs("備考") = Range("S3") rcs.Update rcs.MoveNext Loop rcs.Close Set rcs = Nothing '一覧のクリア、始点へ移動 Range("B3:S100").Select Selection.ClearContents Range("K3:K100") = "=ROUNDUP(J3/1.05,0)" Range("M3:M100") = "=K3-L3" Range("N3:N100") = "=IF(L3="""","""",ROUNDDOWN(M3/K3,4))" Range("B3").Select End Sub と考えていたのですが、これだと再度フォームを開き抽出条件を入力しなくてはならないですよね。 フォームを開かず、抽出条件入力もせず、アクセスデータベースを編集するには、どのようにしたら良いのでしょうか?

共感・応援の気持ちを伝えよう!

  • 回答数1
  • 閲覧数820
  • ありがとう数1

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1

Accessで全て処理するのが一番だと思います。 以前、Accessを入れていないPCでExcelで行っていた時には シート上五行位をコマンドボタン・一時テキスト置場・説明文などを置いといて それから下はレコード表示。 主キーのレコード有無でAccessに無ければ追加クエリかレコードセットで処理。 既存のレコードをAccessからExcelに展開して修正を加えた場合は 修正を加えていないものも含めて更新クエリかレコードセットで処理してました。 無駄を含む処理なのですが、どうせ変更の有無確認処理をどこかでやらねばならないのなら まとめて更新処理をした方が楽だったので・・。 Accessで全てまかなえませんかね~。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご返事遅くなり申し訳ありません。 やはり全てをアクセスで行なえばいいんですよね。 私の会社にはエクセルは使えても、アクセスを使える人が少なく、 またCPにはアクセスがほとんど入っていません。 なので、エクセルを使いなんとかならないかと考えた次第です。 私自身も現在勉強中で、本を片手にやっとこさここまで仕上げました。 主キーのレコード有無、追加クエリ、更新クエリ、レコードセット。 これらのキーワードで検索し、もっと勉強したいと思います。 ありがとうございました。

関連するQ&A

  • エクセルVBAでアクセスデータベースを抽出して読み込む方法

    エクセルVBAでアクセスデータベースを抽出して読み込む方法 アクセスで見積の提出情報のデータベースを作成しています。 このデータベースをエクセルのフォームで日付指定し抽出したいと考えております。 データベースの全てを読み込む事には成功したのですが、 いざフォームを作成し日付を入力。 実行したのですが、日付の構文エラーとなってしまいました。 抽出条件を表すSQLステートメントがおかしいのかもしれません。 どこを訂正したらよいのでしょうか? Private Sub CommandButton1_Click()  Dim rcs As ADODB.Recordset  Dim cnStr As String, sqlStr As String  Dim sday As Date  sday = TextBox1.Text '一覧のクリア、始点へ移動  Range("A5:N300").Select  Selection.ClearContents  Range("A5").Select 'データベースの保存場所  cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Accexl\見積予定.mdb" '検索条件  sqlStr = " SELECT * FROM 予定表 WHERE 作成日 = # sday #" 'データベースの読込、コピー、閉じる  Set rcs = New ADODB.Recordset  rcs.Open Source:=sqlStr, ActiveConnection:=cnStr, CursorType:=adOpenStatic  ActiveCell.CopyFromRecordset rcs  rcs.Close  Set rcs = Nothing   Range("C:C,E:E").Select   Selection.NumberFormatLocal = "h:mm;@"   Range("B:B,D:D").Select   Selection.NumberFormatLocal = "m/d;@"  Range("A3").Activate End Sub フォームの中にテキストボックス(日付を入力)、コマンドボタン(検索実行)を配置しています。 テキストボックスには日付表示するようにしています。 Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Dim ret As Long   ret = 0   If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then     With TextBox1       .Text = Replace(.Text, "/", "")       If IsNumeric(.Text) Then        If IsDate(Format(.Text, "0000""/""00""/""00")) Then         .Text = Format(.Text, "0000""/""00""/""00")         Else          ret = 1         End If       Else        ret = 1       End If      If ret = 1 Then       MsgBox "日付指定です"       KeyCode = 0     End If     End With    End If End Sub

  • エクセル側からアクセスへデータ転送

    エクセル側からアクセスにデータ転送する時に Sub Data_Add() Dim db As New ADODB.Connection Dim Rs As New ADODB.Recordset db.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Users\Owner\Desktop\1.mdb;" アクセス2003にデータを転送する場合はこれでうまくいきましたが、 アクセス2007にデータを転送しようと、 \1.accdb;"とすると、データベースの形式を認識できません。となってしまいます。 どうしたらいいでしょうか? だれか教えて頂けませんか?

  • 異なるデータベースでのSELECT INTO

    昨日と同じところでつまずいているのですが、 みなさまご助言お願いいたします。 タイトルのようにSELECT INTO構文を 2種類のデータベースにまたがって使用したいと思っています。 Dim adoConA As ADODB.Connection 'データベースA Dim adoConB As ADODB.Connection 'データベースB Dim rs As ADODB.Recordset Dim sSQL As String sSQL = "SELECT * FROM 顧客情報" ' データベースAからテーブル取得 rs.Open sSQL, adoConA ' 取得したテーブルをデータベースBに新しいテーブルとして保存 ????? 実際には、SELECT INTOは無理かなと思い、 上記のように、一度レコードセットに取得して それをテーブルBに保存すると言う方法を行おうと 思っているのですが、やり方がわかりません。 もちろん、別の方法でも可能ならば 別の方法でも構いませんので、 おわかりになるのであれば教えてください。

  • エクセル2000マクロ操作

    ネッワーク上でつながっているクライアントパソコンのファイルを よみに行く操作を変数を使って処理しようとしていますがうまくいきません ロジックは下記の通りです。 Dim namae As String  Range("k3").Select 'k3にはパソコン名pcs17694が入っています。 namae = ActiveCell.Value ActiveCell.Value = namae Workbooks.Open(Filename:="\\namae\c\機器管理\masuta.xls").RunAutoMacros Which:= _ xlAutoOpen 教えてください。 以上

  • Access2000でVBAからパラメータクエリを実行

    Access2000でフォームからの値を参照して実行するクエリを作りました。 SQLで表すと、以下のようなものです。 クエリ名:test SELECT TEST.HAKKODTE FROM TEST WHERE (((TEST.HAKKODTE)=Format([Forms]![F_メニュー]![txtNyukinDate],"yyyymmdd"))); このクエリをダブルクリックで実行すると問題なく実行できます。 これを、VBAからレコードを参照したいと思い、 以下のように記述しました。 Dim cn As ADODB.Connection Set cn = CurrentProject.Connection Dim rs1 As ADODB.Recordset Set rs1 = New ADODB.Recordset Dim sqlstr As String sqlstr = "SELECT * FROM test;" rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic しかし、Open処理で、「1つ以上の必要なパラメータの値が設定されていません」 というエラーになってしまいます。 VBAを実行するときに、[Forms]![F_メニュー]![txtNyukinDate]に きちんと値は入力されています。 これを実行する方法はあるでしょうか?

  • Excel-VBAでMySQのレコード件数を得たい

    Excel-VBAでレンタルサーバーにあるMySQLのテーブルのレコード件数を得たいのですが、うまくいきません。 色々と調べて下記のようなコードにしたのですが、結果「-1」と表示されます。 レコードは1500件ほどあります。 Excel-VBAからテーブルにレコードを挿入したり、Excel-VBAにレコードをもってきたりはできますので、アクセス自体は問題ないと思います。 どうにも分からず困ってます、どなたか教えてください。 環境としては、Excel2000 WindowsXP MySQL5 ver5.xx です。 Sub testxx() Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim connectionString As String Dim kensu As Integer connectionString = "Driver={MySQL ODBC 5.1 DRIVER};" _ & " SERVER=xxxx.xxxx.jp;" _ & " DATABASE=xxxDB;" _ & " USER=xxxuser;" _ & " PASSWORD=xxxpass;" Set con = New ADODB.Connection con.Open connectionString sqlStr = "select * from LinkTable" Set rs = con.Execute(sqlStr) 'kensu:件数----------- kensu = rs.RecordCount MsgBox (kensu) 'kensu:件数----------- con.Close End Sub

  • エクセルVBAの処理速度アップについて

    以下のエクセルVBAを組んでいるのですが、処理の速度をあげたいです。 無駄な記述があると思うのですが、調べても試行錯誤しても、上手くいきません。 処理速度をあげる記述をご教示願います。よろしくお願い申し上げます。 ※別ファイルのAccessファイルをデータベースとして、「今年」テーブルと、「前年」テーブルそれぞれから条件抽出して、受注数量を合計させるものです。 ※SQLのVBAです。なお、実際ファイルのVBAは、以下記述の5倍量あります(内容は、セル範囲が違うだけで同じ。現在速度:約30秒)。 ※「配列」というのを活用すれば速くなるようなのですが、理解できませんでした。。。 ーーーー【以下、VBA】&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293; Sub DSUM集計() Application.ScreenUpdating = False Dim db As ADODB.Connection Dim rs As ADODB.Recordset Dim mySQL As String Dim cmd As ADODB.Command Dim AA As Variant AA = "AND 営業箇所" & Range("C13") & " AND 拒 IS NULL AND 販売伝票 <" & Range("D13") & " AND 品名 " & Range("E13") & " AND 得意先名 " & Range("F13") & " AND 請求先名 " & Range("G13") & " AND 出荷先名 " & Range("H13") Set db = New ADODB.Connection db.Provider = "Microsoft.Ace.OLEDB.12.0" db.Open "\\▲▲▲\ACCESS.accdb" With Worksheets("補助計算") mySQL = " SELECT SUM(受注数量) FROM 今年 " mySQL = mySQL & "WHERE 納入期日=" & Range("B13") & AA Set rs = New ADODB.Recordset Set cmd = New ADODB.Command Set cmd.ActiveConnection = db cmd.CommandText = mySQL Set rs = cmd.Execute Range("I13").CopyFromRecordset rs mySQL = " SELECT SUM(受注数量) FROM 今年 " mySQL = mySQL & "WHERE 納入期日=" & Range("B14") & AA Set rs = New ADODB.Recordset Set cmd = New ADODB.Command Set cmd.ActiveConnection = db cmd.CommandText = mySQL Set rs = cmd.Execute Range("I14").CopyFromRecordset rs mySQL = " SELECT SUM(受注数量) FROM 前年 " mySQL = mySQL & "WHERE 納入期日=" & Range("K14") & AA Set rs = New ADODB.Recordset Set cmd = New ADODB.Command Set cmd.ActiveConnection = db cmd.CommandText = mySQL Set rs = cmd.Execute Range("M14").CopyFromRecordset rs    mySQL = " SELECT SUM(受注数量) FROM 前年 " mySQL = mySQL & "WHERE 納入期日=" & Range("K15") & AA Set rs = New ADODB.Recordset Set cmd = New ADODB.Command Set cmd.ActiveConnection = db cmd.CommandText = mySQL Set rs = cmd.Execute Range("M15").CopyFromRecordset rs rs.Close Set rs = Nothing Set db = Nothing Application.ScreenUpdating = True End With End Sub ーーーーーーーーーーーーーーーーーーーー 以上

  • コンボボックスのリストのテーブルからの直接取得の方法

    下記の件に関しご教授願います。 Excelで、コンボボックスのデータ取得を、ワークシートに書き出してからそれをListFillRange等で行っていましたが、レコードセットしたテーブルのデータを直接取得することは出来ないでしょうか。 RowSourceType = "Field List"等試してみましたがわかりませんでした。もしExcelで出来るようでしたらお教えください。 Dim objcon As ADODB.Connection Dim objrs As ADODB.Recordset Dim i As Integer Set objcon = New ADODB.Connection Set objrs = New ADODB.Recordset objcon.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & ThisWorkbook.Path & "\process.mdb" Dim code As Variant Dim mmcode As Variant mmcode = Worksheets("入力sheet").Range("d100").Text code = "'" & mmcode & "%'" ' With objrs .ActiveConnection = objcon .Source = "select 顧客名 from 002顧客名 where 県名 like " & code & ";" .Open End With ここから、ワークシートに書き出してましたが、コンボボックスのリストに直接指定したいのです。 下記の方法で試しましたが、浅い知識ではうまくいきませんでした。 ComboBox4.RowSourceType = "field list" ComboBox4.RowSource = objrs 何か方法があればお教えください。

  • SQL文の最後に「;」はいらないのでしょうか?

    VBAのADOについて質問です。 SQL文の最後には「;」を付けなくちゃいけないものだと思っているのですが ・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・ Private Sub test_Click() Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Set CN = CurrentProject.Connection Set RS = New ADODB.Recordset SQL = "SELECT * FROM Tテーブル" ・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・ でも問題なくコードが動きました。 SQL文の最後に「;」はいらないのでしょうか? ちなみに SQL = "SELECT * FROM Tテーブル;" でもエラーになりませんでした。

  • データベースを切り替えて使う方法

    初心者です。 環境:WindowsXP,VB6,Access2000 Access2000のデータベースを切り替えて使いたいのですが、うまく動作しません。 ADO使用しています。 普通に考えて、いったんつないでいたDBを mRs.Close mCn.Close Set mRs = Nothing Set mCn = Nothing で接続をいったん切断して、 Call ConnectType で再度DBに接続しましたが、接続されているのは前のDBです。 1 原因がわかりません。 2 DBを切り替えて使う方法がわかりません。 もちろん切り替えの処理以外は現在正常に動作しています。 ご教授よろしくお願いいたします。 <プログラム> Private mCn As ADODB.Connection Private mRs As ADODB.Recordset Private DBfile As String Private Const DEF_CONNECT As String     = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\word.mdb" Private Const DEF_SQL As String = "Select * From tango_tbl" Private Sub ConnectType() Set mCn = New ADODB.Connection mCn.ConnectionString = DEF_CONNECT mCn.Open Set mRs = New ADODB.Recordset mRs.Source = DBfile mRs.ActiveConnection = mCn mRs.CursorType = adOpenDynamic mRs.LockType = adLockOptimistic mRs.Open End Sub