ExcelVBA 「フィールド指定せずに行で」Accessのテーブルへ書き込みしたい

Question

前提・実現したいこと

Excel 1ファイルごとに1レコード(管理している項目は同じ内容・位置なので、必要なセルを1行にまとめて疑似的にレコードっぽくしてます)
この1行だけで良いので、Accessのテーブルに書き込みというか貼付けしたい
イメージしているのはExcelでコピーして、Accessのテーブルで貼り付けする動作

Excelのデータイメージ
Sheet1･･･物件名、号室、契約者名、仲介手数料などを計算・記録　印刷する形でエクセル方眼紙のように作られてます
その中から保管しておきたいデータをシートの下に「=○○」で引っ張って疑似的にレコードっぽく

※この動作は、Access側ではなくExcel側から行いたいです※
自分がやるならどんな形でもいいんですが、営業にやってもらおうと考えてるのでボタン1つで動作したい
(Access開いてAccessでボタン押させるのも、営業から問い合わせ来るだろうなって思うほどのスキル具合です)

試したこと

ExcelVBA+AccessDBについて調べて、ADODB？でのデータベース操作はやってみました。
フィールドを指定する形であれば、読み書き更新削除は動かすことができます。
ただその後、すぐ見つかるだろうとExcelからAccessへの行コピペを調べてみたところ全然出ず、フィールドを指定するものしか出ませんでした
手動ではすぐできるし、Excelシート→ExcelDBは同じ処理をしている(それもテラテイルで教えてもらいました)ので、
簡単なのかと思ったんですが・・・

補足情報（FW/ツールのバージョンなど）

Excel、Accessともに2016

以前質問させてもらい本当に色々と教えていただきました。
今まで難しく考えすぎてたのかなと要らないものを削ぎ落してみると、残ったのはExcelをDBとして使う不安くらいでした。
(毎月20~30、多くても100行かないくらいのレコード追加・変更、計算はExcelで行うのでDBから行うのは抽出のみ、やったとしても足し引きくらい)

追々はデータ自体の精度を高めるためにAccess等で入力フォーム作ったりとか、何か別のアプリケーションに移行したい気持ちはありますが、
自分も営業もスキルが足りていないので、最低限のデータの保管・抽出について意識付けをまずやりたいと思っています。

引き続き自分でも探してみます。何卒よろしくお願いいたします。

###追記

'ExcelよりAccessDBへレコード追加・書き込み
Sub addRecords()

Dim strFileName As String
strFileName = "test2.accdb"
 
Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
 

'***** データを登録 *****


Dim i As Long
Dim rng As Range
 

'・・・ レコードセットrsを開く処理（略）
adoRs.Open "sheet1", adoCn, adOpenKeyset


Set rng = Range("A1", "T2") '疑似レコードの範囲（1行目項目名、2行目値）

adoRs.AddNew
For i = 1 To rng.Columns.Count
    adoRs.Fields(rng.Cells(1, i).Value).Value = rng.Cells(2, i).Value
Next
adoRs.Update
 
'***** データベース終了処理 *****
adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ
 
Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄
 
End Sub

試しに作った分をちょっと加工してみましたが、Forの後の「adoRs.Fields~」の部分でエラーが出ます。
実行エラー3265 要求された名前、または序数に対応する項目がコレクションで見つかりません
そもそもレコードセットを開く処理あたりも不安ですが・・・

Comments

[sazi]

項目を指定せず、行単位で更新を行いたい理由は何ですか？メンテナンスを減らすというのは考えにくいですけど

[morikawa0208]

目を通していただいて、ありがとうございます！行単位で行いたい理由は、管理項目の追加する状況が結構起こりそうだからです。直近だと仲介手数料に対しての歩合計算の方法が変わりまして、日付や担当や金額など15項目くらい一気に増えました。さらに今まで契約した分は今までの計算方法で支給するとのことで残して、新旧のフラグも追加し・・・という感じで今後も起こりそうです。行単位での管理であればExcelに項目追加とAccessテーブルにフィールド追加だけで済むかなと思いまして・・・

Answer 1

回答を用意しているうちに解決済みになってしまいしましたが、せっかく書いたので。

ExcelVBA+AccessDBについて調べて、ADODB？でのデータベース操作はやってみました。
フィールドを指定する形であれば、読み書き更新削除は動かすことができます。
ただその後、すぐ見つかるだろうとExcelからAccessへの行コピペを調べてみたところ全然出ず、フィールドを指定するものしか出ませんでした

フィールドを指定しての書き込みはできているということですね。

AccessのテーブルとExcelの疑似レコードの列数、フィールド位置が同じなら、
ADODBで下記のようにすれば、フィールド名の指定は不要です。

Dim rs As ADODB.Recordset
Dim i As Long
Dim rng As Range

'・・・ レコードセットrsを開く処理（略）

Set rng = Range("A10";"G10") '疑似レコードの範囲

rs.AddNew
For i = 0 To rs.Fields.Count - 1
    rs.Fields(i).Value = rng.Cells(1, i+1).Value
Next
rs.Update

追記

エクセルの疑似レコードが下記のように項目名も持たせた場合の例

項目1	項目2	項目3	・・・
値1	値2	値3	・・・

Dim rs As ADODB.Recordset
Dim i As Long
Dim rng As Range

'・・・ レコードセットrsを開く処理（略）

Set rng = Range("A10";"G11") '疑似レコードの範囲（1行目項目名、2行目値）

rs.AddNew
For i = 1 To rng.Columns.Count
    rs.Fields(rng.Cells(1, i).Value).Value = rng.Cells(2, i).Value
Next
rs.Update

Comments

[sazi]

それは考えたんですが、名称だけ意識すれば良いほうが扱う人にとって手間は少なくて済むと考えました。
特にエクセルは見易さで項目位置を変更することもあるでしょうし。

[hatena19]

私もエクセルの疑似レコードに項目名も持たせた方がいいと思います。
質問が「フィールド指定せずに行で」ということだったのでそれに合わせしましたが。

[morikawa0208]

hatena19様もいつもありがとうございます。サイトも参考にさせて頂いておりますm(__)m
せっかく書いていただいたので動かしてみようと、追記の方を加工してみたんですが、
エラーが出てしまい動きません・・・
質問文に追記しておりますので、もしご都合よくお時間あれば見て頂けると助かります・・・

[morikawa0208]

ちょっと色々と調べているうちに、今回質問した内容はおとなしくフィールド指定してデータの書き込みを行い、そもそものExcelを1つにしてメンテナンスを楽にする方向で動くほうが、シンプルで分かりやすいかなと思いました。せっかく回答いただいたのに、申し訳ございません・・・。ただ、やることがハッキリしたので助かりました。ありがとうございました。

[hatena19]

> 実行エラー3265 要求された名前、または序数に対応する項目がコレクションで見つかりません

エクセルの項目名（1行目）が、レコードセットのフィールド名で見つからないということだと。
エクセルの項目名とAccessのテーブルのフィールド名は完全に一致してますか。全角/半角がちがっているとか。
まは、フィールド名に空白やハイフンなどの記号が含まれている場合もエラーになります。

Answer 2

行単位というのはRecordsetに該当しますが、Recordsetも内部的に構造を持つ必要があります。
AccessにはDoCmd.TransferSpreadsheet メソッドがあり、エクセルのシートを指定するだけで内部的に構造解析を行ってくれますが、インポート/エクスポート用なので、更新には対応できません。

行単位で行いたい理由が、ACCESSとエクセルの項目の追加だけで済ませたいということなので、エクセル側でシートの項目タイトルを取得するように処理を組めば目的を果たすことができるかと思います。

Comments

[morikawa0208]

回答ありがとうございます！まだ頂いた回答について調べ中ですが、http://www.moug.net/tech/exvba/0030002.htmlこのあたり参考にしてみようと思います！
このコピペのほかに重複の検索とか調整する部分もあるので、ちょっと時間かかりそうですが・・・

上の補足情報を返信していた時に思ったんですが、フィールド名を指定してもしなくても、最初に設定しないといけないのは変わらず、そんなにこだわる部分じゃない気がしてきました。
それより元のExcelを1つにできるよう設定して、メンテナンスしやすくするほうが費用対効果というか、成果が見えやすいのかもとも・・・こういう問題は思考がループしちゃいますね;0;

調べたらコピペできるとのことで、また再度検討するきっかけとなりました。
以前の質問でもそうですが、いつもありがとうございます。またよろしくお願いします！