Microsoft Access 掲示板

素人質問で大変恐縮なのですが、AccsesとExcelの連携がうまくいきません。助けてください。

7 コメント
views
4 フォロー

おはようございます。
業務の取り扱うデータが一元化されておらず取扱いが煩雑性を極め業務効率がにっちもさっちもいかないため、先日からAccessの勉強を始めました。
テキストに従いAccessとExcelを連携させようとしたのですがトラブルが発生し、その後知人にも聞いたのですがどうしても解決ができず、当掲示板への書き込みに至りました。
当部署ではVBA自体を使えるのが私しかおらず、今までも独学でやって乗り越えてきたのですが、今のこのトラブルだけは一週間近く調べても悩んでもどうしても解決できませんでした。

この状況を打破するための手がかりを一刻も早く掴みたい次第です、初の書き込みでルールもわかってなく、内容も稚拙で大変お恥ずかしい質問であることは重々承知しておりますが、これを実行しDBシステムの構築の礎を作り業務効率化と問題の可視化を行うことは、今期の部下の昇進、昇格にもかかわっており今年度末までに早く解決しなければ今にも現場が崩壊し、守れるものも守れなくなってしまいそうな様な状況です。

何もわからない若輩者の質問とはなってしまうのですが、どうか平にお願い申し上げます。皆様のお知恵を拝借させていただけないでしょうか。

使用環境:Windows10 office2019 Access、Excel共に32bit版
ファイル保存場所:Accsess、Excel共に同一フォルダ
ファイル名:SampleSystem(Excel:データの元の部分) Data.accdb(Accsess:データ入力先)
OBDC32bit:存在確認済み
Excel参照設定:ActiveX DataObjects 6.1Library

以下問題のコード(直接、フォルダとファイルを指定した("Data Source=C:\Users\user\Desktop\System\Data.accdb;")コマンドも試ましたが実行できませんでした。

下記コード実行時の表示:実行時エラー’-2147467259(80004005)':
ファイル 'C:¥Users¥user¥Desktop¥System¥Data.accdb'が見つかりませんでした。

'# シート上データの移行
Option Explicit '変数の宣言を強制する

Public Sub insertRecord()
  '## レコードの挿入
  
  '接続
  Dim cn As Object 'ADOコネクション用オブジェクトの宣言
  Set cn = CreateObject("ADODB.Connection") 'ADOコネクションを作成
  
  %%{bg:yellow}%%{bg:yellow}cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Sourc%%e=" & ThisWorkbook.Path & "\Data.accdb;" 'Accessファイルを指定してコネクションを開く%%
          
  'SQL文の作成
  Dim sql As String 'SQL文用変数の宣言
  sql = _
    "INSERT INTO 販売管理(" & _
      "商品ID, " & _
      "商品名, " & _
      "売上日, " & _
      "数量, " & _
      "売価, " & _
      "製造場所, " & _
      "定価, " & _
      "原価, " & _
      "取引先, " & _
      "営業所, " & _
      "社員名) " & _
    "VALUES(" & _
      "'BL10-10', " & _
      "'青1.0mm10本セット', " & _
      "#2021/1/1#, " & _
      74 & ", " & _
      1170 & ", " & _
      "'b工場', " & _
      1300 & ", " & _
      950 & ", " & _
      "'A社', " & _
      "'東京', " & _
      "'伊藤孝');"
  
  '実行
  cn.Execute sql
  
  '接続解除
  cn.Close
  Set cn = Nothing
  
End Sub

以上です。解決策のみならず、何か不足点や疑問に思われたこと、何か解決の糸口になりそうなものでもなんでも結構です。忌憚ないご意見を賜りたく存じます。
本当に恐れ入りますが、何卒宜しくお願い致します。

たんぽ
作成: 2021/10/07 (木) 05:38:12
通報 ...
1
りんご 2021/10/07 (木) 16:35:25 c564b@0e907

当て推量ですが、Microsoft.ACE.OLEDB.12.0は、64bit用?32bit用?

4
たんぽ 2021/10/11 (月) 03:26:29 316c2@dfd92 >> 1

りんご様
夜分遅くに失礼します。先程までそれについて確認をしておりました。
どうやらPC環境がWindows10 64bitなのに対しOfficeが32bitで、ACE.OLEDBが64bitになっている可能性がありそうです(素人な為この予測が合ってるかはわかりませんが)
この問題に対しどうアプローチをするか今一度学習、検討をしてみます。ご回答心から感謝申し上げます。

6
りんご 2021/10/11 (月) 11:54:42 c564b@0e907 >> 1

データが一元化されておらず…にっちもさっちもいかないため、Accessの勉強を始めExcelを連携させようとしたのですが

 砂上楼閣のリスクを理解する為に、データモデリングの勉強から始めると吉ですよ。ネットニュースで話題になっているように、絵に描いた餅になると大変です。
 あれはもう、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、ね。

2

udlファイル(ユニバーサルデータリンクファイル)を作成し、有効な接続プロバイダーと接続文字列を確認されてはいかがでしょうか?

新規テキストファイルを作成し、拡張子をtxtからudlに変更してから開くと、データリンクプロパティのダイアログボックスが表示されます。ここで、使用可能なプロバイダーの確認や接続テストを行うことができます。
各項目を設定してからOKで閉じます。このファイルをメモ帳などのテキストエディタで開く(例えば、メモ帳を開いてメモ帳のウィンドウ内へudlファイルをドラッグ&ドロップする)とダイアログで設定した接続内容の接続文字列が記載されています。

もしかすると、OLEDB.12.0ではなく、OLEDB.16.0がインストールされているのではないかと思いますが、上記の方法で確認してみてください。

また、蛇足ですが、せっかくADOを参照設定されているのであれば、

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

のところは、

Dim cn As New ADODB.Connection

としたほうが、インテリセンスも構文チェックも有効になるので、コーディングが楽になると思います。

5
たんぽ 2021/10/11 (月) 03:39:50 316c2@dfd92 >> 2

tone様
夜分遅くに失礼します。仰って頂いた通り、URLファイルでデータリンクプロパティを確認してみたのですが…申し訳ありません、当方の入門的な技術力では各項目の設定が上手くいかず参照することが出来ませんでした…今一度知識を改めて挑もうと思います。また、これも完全な初歩的な問題で質問とするには大変お恥ずかしい限りなのですが、ActiveX DataObjects 6.1Libraryはこのままでいいのでしょうか。
また、Dim cn As New ADODB.Connectionについて、シンプルでわかり易いです。
これはぜひ参考に致します。ご回答心から感謝申し上げます。
また、当方の回答が的を射ず、もしかしたら混乱されているかも知れません。その場合は何卒ご容赦ください。知識も浅はかですが、とにかく今はトライアンドエラーで解決を目指してみます。

7

1.参照設定について

ActiveX DataObjects 6.1Libraryの参照設定はそのままのほうが良いと思います。複数個所でADOを使用されるなら参照設定しておいた方がメリットが大きいです。

2.UDLファイルの設定について

確認が足りず申し訳なかったのですが、64bitのWindowsで起動すると、データリンクプロパティも64bitで起動するようです。32bitのcmd.exeからudlファイルを開いてみると、32bitのプロバイダーのリストが表示されます。

1.C:\Windows\SysWOW64\cmd.exeを開く
2.udlファイルのパスを入力(またはudlファイルをドラッグ&ドロップ)
3.エンターキーでコマンドを実行すると32bitのデータリンクプロパティが開く

設定は、プロバイダーでリストから「Microsoft Office 12.0 Access Database Engine OLE DB Provider」を選択し「次へ」、接続で「データソース」にAccessファイルのフルパスを入力すればOKです。
プロバイダーのリストに上記がないのであれば、32bitのACE.OLEDB.12.0がインストールされていないのだと思います。

3.32bitのACE.OLEDB.12.0のインストール

知らなかったのですが、クイック実行形式のOffice(2019はクイック実行形式のみ)の場合、別途インストールしなければOLEDBで接続できないようです。

当方の環境では、64bitのACE.OLEDBがインストールされており、udlファイルを64bitで実行するとMicrosoft Office 12.0 Access Database Engine OLE DB Providerがプロバイダーのリストに表示されましたが、32bitで実行すると表示されませんでした。

そこで、以下のページから、accessdatabaseengine_X64.exeをダウンロードし、インストールしてみたところ、32bit・64bitどちらにもMicrosoft Office 12.0 Access Database Engine OLE DB Providerが表示されるようになりました。

Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント
https://www.microsoft.com/ja-JP/download/details.aspx?id=54920

うまくいくと良いのですが・・・。

3
たんぽ 2021/10/11 (月) 03:22:03 316c2@dfd92

りんご様 tone様
当方の的を射ない質問にご回答くださり心から感謝申し上げます。
当方保険会社社員でございまして、先日の地震対応において時間的リソースを割き過ぎた結果今のお返事となりました事、まずはこの場を持ちましてお詫び申し上げます。