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

質問

見積提出表をエクセルで作成しており、データの数が多く最近重くなってきました。
そこでこれらのデータをアクセスに写し、エクセル編集できないかと試行錯誤しております。

エクセル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

と考えていたのですが、これだと再度フォームを開き抽出条件を入力しなくてはならないですよね。

フォームを開かず、抽出条件入力もせず、アクセスデータベースを編集するには、どのようにしたら良いのでしょうか？

回答No.1

nicotinism · ベストアンサー

Accessで全て処理するのが一番だと思います。

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

お礼

ご返事遅くなり申し訳ありません。

やはり全てをアクセスで行なえばいいんですよね。

私の会社にはエクセルは使えても、アクセスを使える人が少なく、
またCPにはアクセスがほとんど入っていません。

なので、エクセルを使いなんとかならないかと考えた次第です。

私自身も現在勉強中で、本を片手にやっとこさここまで仕上げました。

主キーのレコード有無、追加クエリ、更新クエリ、レコードセット。
これらのキーワードで検索し、もっと勉強したいと思います。

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