質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.49%

  • VBA

    1854questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • SQL Server

    602questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

  • Access

    453questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Access VBA ADO JetとSQLserver接続文字列、CurrentProjectが混在する場合

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 5,461

nekomura

score 124

よろしくお願いいたします。

コーディング自体ではなく初歩的なことなのですが、
調べても疑問点を解決する情報が見つからなかった為、こちらでお伺いしたく存じます。

下記コードは、リンクテーブルを更新するロジックを、当サイトでご教示いただいたものを参考とさせていただき、値や変数の中身を当方で目的のものに変えて記述しております。
コード内①、②、③について質問です。

SQLserverへの接続文字列を使用して、下記コードを実行させたいのですが、
私の現在の知識レベルの認識としては、
○Jet Provider→MicrosoftのDBにアクセス(つまり自身のAccessDB?)
●SQLserverへの接続文字列→"Provider=SQLOLEDB…~"
○Current.Connection→現在のDB(つまり自身のAccessDB?)
リンクテーブル→Accessのテーブル。SQLserverとリンクさせているので同じものがSQLserverにも存在する。
●OCDB接続→Access自体の機能を使って外部のDBのテーブルをリンクさせる

となっています。

つまり、下記コードではJetとSQLserver接続文字列、CurrentProjectなどが
混在している為、矛盾があるような気がする
のですが、いかがでしょうか。
デバックしてみると、Current.Connectionの内容はSQLserverではなくMicrosoftへの接続となっているようです。

SQLserverに接続したい場合の記述方法、自身のDBの場合の方法の違いがはっきりと調べられずうまく記述できません。
(抽象的な質問で申し訳ありません)

不明点キーワード:
・Jet Provider 
・SQLserverへの接続文字列
・Current.Connection
・リンクテーブル
・OCDB接続

'リンクテーブル更新
Sub update_link_table()

    Dim cat As ADOX.Catalog
    Dim strConnection As String     ' DB(SQLserver)への接続文字列
    Dim strTblName As String      ' リンク先を更新するテーブル名

    'SQLserverへの接続文字列
  ①  strConnection = ""Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=I**4\SQLEXPRESS"
"  
     strTblName = "[dbo_A]" '(Accessにあるリンクテーブル名。同名・同内容のものがSQLserver側にも存在)

    Set cat = New ADOX.Catalog
    With catSet .ActiveConnection = CurrentProject.Connection  'これは、Accessにあるデータベースを示していますよね?
        With .Tables(strTblName) //ここでエラー
           ③ .Properties("Jet OLEDB:Link Provider String") = strConnection 'Jet とは、自身(Access)のデータベースにアクセスすること?
             .Properties.Refresh
        End With
    End With

    Set cat = Nothing
End Sub


Accessを始めて、まだ日が浅いですがADOの接続について、
根本的にここがいまひとつしっくり理解ができていません。

わかりやすいそれぞれの違い、解説を有職者の皆様にご教示いただきたく存じます。
どうぞよろしくお願いいたします。

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

です。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+2

一旦ソースコードから離れて、アーキテクチャというか用語整理から行きましょう。

不明点キーワード: 
・Jet Provider  
・SQLserverへの接続文字列 
・Current.Connection 
・リンクテーブル 
・OCDB接続

この辺に関することですが、話の都合で順番が入れ替わります。
また、説明の中で本来はもっと細かく分離されているもの等がありますが、用語が乱立して理解の妨げになる可能性がありますので、ウソにならない範囲で省きます。

 DBとDBMS

  • DB(DataBase)はデータそのもの
  • DBMS(DataBase Management System)はデータベースを操作するための管理ツール

です。
.mdb /.accdb というファイルはDB、「Accessというアプリケーション」はDBMSです。
同様にSQLServerもDBとDBMSという構成になっています。

この回答内では.mdb/.accdb ファイルのDBのことを以降「AccessDB」と書きます。

 DBへのインターフェイス

 ODBC

本来、DBへのアクセスは、SQLServerはSQLServer用の、OracleにはOrale用の、MySQLはMySQL用の、というようにそれぞれごとにアクセスするための仕様が異なります。
これは開発者にとっての負荷が高く、利用するDBを変更することによるアプリケーションの修正範囲が膨大になるといったようなことが発生します。

ODBCは、

  • 各種DBへのドライバ(ODBCドライバ)
  • プログラムなどからのインターフェイス

と分離することにより、ODBCドライバを変更することによって利用するDBを変更する際の影響範囲を最小限にするための共通仕様です。

 ADO

ADOもODBCと同様に、プログラムなどアプリケーションに対し各種DBへのインターフェイスを提供する仕様です。

  • 各種DBへのドライバ(OLE DBプロバイダ)
  • プログラムなどからのインターフェイス

と分離されています。

 OLE DBプロバイダ

OLE DBプロバイダはODBCドライバと同様、各種DBそれぞれ用に提供されている必要があります。
しかし歴史的にODBCの方が古いこともあり、ODBCドライバは提供されているがOLE DBプロバイダは提供されていないDBというものもあります。

これらに対しMicrosoftは、「ODBCを利用するOLE DBプロバイダ」というものを利用することで、ADOに対するインターフェイスの共通化を図っています。

つまり、ADOにとってDBへのアクセス経路は

  • ADO - OLE DBプロバイダ - DB
  • ADO - OLE DBプロバイダ - ODBC - ODBCドライバ - DB

というパターンがあります。

不明なキーワード「Jet Provider」や、「接続文字列」で記載している「Provider」の部分はこのOLE DBプロバイダの指定です。
なお「Jet Provider」の正式名称は「OLE DB Provider for Microsoft Jet」です。

 Accessの動作と接続文字列

AccessDBにおける「クエリ」は通常のDBで言う「ビュー」で、SQLを発行する意味での「クエリ」と紛らわしいので、AccessDBについてもビューといいます。

AccessというDBMSにはSQLServerなど外部のDBの一部機能を利用するための管理機能が含まれています。
それが、

  • リンクテーブル
    SQLServerなど外部のDBへの参照を、あたかもAccessDB内のテーブルとして扱えるようにする仕組み
  • パススルークエリ
    SQLServerなど外部のDBに対して直接問い合わせを発行するクエリを、AccessDB内のビューとして保存できる仕組み

などです。
これらの仕組みは、ODBCやOLE DBを利用して実現されています。
その際、AccessDBに保存されているのはSQLServerなど外部DBの「データそのもの」ではなく、接続の設定情報のみです(つまり参照)。

この設定情報を1つの字列で示したものが「接続文字列」です。

ADODB.Connectionオブジェクトの各プロパティを1つの文字列にしたものとなっています。

https://msdn.microsoft.com/ja-jp/library/cc426819.aspx は Jet の例ですが、参考になるかと思います。
「プロバイダ固有の」という方は接続先DBによって必要になる設定値の部分で、ADODB.ConnectionのPropertiesコレクションで設定できます。
学習のため、

  • ADODB.ConnectionオブジェクトのConnectionStringプロパティに接続文字列を設定する
  • ADODB.ConnectionオブジェクトのProviderプロパティの設定を確認する(Debug.Print ででも)
  • 同様に各プロパティもしくは Propertiesコレクションの各要素名とその内容を確認してみる

といったことをしてみるといいかもしれません。


一旦ここまでの理解を正確にしてから先に進みましょう。


 ソースコード部分

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

テーブル名はあくまで "dbo_A" であり "[dbo_A]" ではないからではないでしょうか。
[]は主にSQL文内で利用される「区切り文字」なので、テーブル名に含めてはエラーになる気がします。


 2016/05/24 10:45 のコメント以降の追記

私が現在コードに書いている内容(実現したいこと)として、
・strConnection(SQLserverへの接続文字列)を使用したい
・ですが、当該コード
With cat
  Set .ActiveConnection = CurrentProject.Connection '←接続先がMSになります
  With .Tables(strTblName)
  .Properties("Jet OLEDB:Link Provider String") = strConnection 'Jetは、MSの接続プロバイダ
  .Properties.Refresh
  End With
End With

のように、「CurrentProject.Connection」「Jet Provider」を使用すると、MSに接続する
ロジックになり、本質問にあるように矛盾が生じるような気がします。

このソースの意味を取り違えているようですね。
このソースは、操作対象はあくまで「AccessDB」です。
AccessDB内に、リンクテーブル、というオブジェクトを作り出す、という処理をしています。

cat (ADOXのcatalogオブジェクト)は接続したDBのカタログ〈テーブルやビューの一覧〉を管理しているオブジェクトです。

With cat
  Set .ActiveConnection = CurrentProject.Connection

catの接続先を CurrentProject.Connection (つまり開いているAccessDB)にする。

  With .Tables(strTblName)

cat 内のテーブル:strTblName への操作(つまりAccessDBのテーブル)。
※ここで想定しているのは既にあるリンクテーブルの接続先変更なので、既存のリンクテーブルである必要がある。

  .Properties("Jet OLEDB:Link Provider String") = strConnection

AccessDB内のリンクテーブル:strTblNameの .Properties("Jet OLEDB:Link Provider String")  つまり接続文字列を、新たに設定したい接続文字列に変更。

  .Properties.Refresh

AccessDB内で変更を有効とするためにリフレッシュ(再読み込み)

  End With
End With

という処理手順です。
操作対象はあくまでAccessDBですので、利用する接続はCurrentProjectですし、DB(プロバイダ)固有プロパティの名称は「Jet OLEDB:・・・」となります。

要点は「操作対象となっているDBが何か」です。

今回は扱っている内容がたまたまAccessDBからSQLServerへのリンクテーブルのため、その リンクテーブルの設定値 としてSQLServerの接続文字列が登場してきますが、ADO・ADOXのコード内で直接SQLServerを操作しているものではありません。


 2016/05/24 17:14 のコメント以降の追記

AccessDB内のリンクテーブル:strTblNameの .Properties("Jet OLEDB:Link Provider String") つまり接続文字列を、新たに設定したい接続文字列に変更。
つまり、この更新プログラムにおいては、新たに更新したい接続文字列=既存のAccessDBのリンクテーブルへの接続文字列
という認識でよろしいでしょうか?

いえ、ちょっとこの例がよくないのかもしれないですが、この例の場合、例えば

  • 開発用DBと本番用DBが同じ構成で存在し、
  • 何らかの条件で(例えば開発用フラグのようなものを用意して)、そのフラグに応じて既に設定されているリンクテーブルの接続先DBを
  • 開発用DB、本番用DBで切り替える

といった動作を想定しています。

そのため、例えば現在はリンクテーブルの接続先が本番用DBとなっている場合、その時点でのProperties("Jet OLEDB:Link Provider String") は本番用DBへの接続文字列となっています。

これを、開発用DBへのリンクテーブルに書き換えるために、

Properties("Jet OLEDB:Link Provider String") = [開発用DBへの接続文字列]

と変更することで接続するDBの宛先を書き換えたことになり、その他の接続情報(リモートでのテーブル名など)は開発DBと本番DBの構成が一致していることにより変更不要で、リンクテーブルの参照先が切り替わります。

なお、AccessDBの既存のリンクテーブルはこのソース上では、

cat.Tables(strTblName)

で表されています。

  • CatalogオブジェクトとしてAccessDBの構成情報が取得されており、
  • その中のTablesコレクションのインデックスstrTblName で指定される Table オブジェクトの
  • プロバイダ固有プロパティ "Jet OLEDB:Link Provider String" ( = cat.Tables(strTblName).Properties("Jet OLEDB:Link Provider String")) を書き換える

という処理です。
この辺はJavaの体験を活かしてコレクションやオブジェクト、プロパティを理解するとわかりやすいのではないでしょうか。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/23 16:16

    Suenaga様
    いつもお世話になっております。
    丁寧なご回答に感謝いたします、大変恐縮です。
    現在Suenaga様の本回答をじっくり読み込んで勉強させていただいておりますので、
    取り急ぎお礼まで。

    キャンセル

  • 2016/05/24 10:45

    Suenaga様
    おせわになっております。
    丁寧に記載頂きました資料のおかげで、用語整理の方はずいぶん理解が深まりました。
    追記の質問として、以下、長文となりますがご容赦ください。

    ご回答頂きました
    ・ADODB.ConnectionオブジェクトのConnectionStringプロパティに接続文字列を設定する
    ・ADODB.ConnectionオブジェクトのProviderプロパティの設定を確認する(Debug.Print ででも)
    を学習の為やってみました。

    本質問のコードですが、エラー部分をご教示の通り変更してもエラーが解決しなかった為、
    箇条書き2つ目のProviderプロパティの設定の確認は、一旦まだです。

    また、もう一点後教示いただきたい内容がございまして、
    私が現在コードに書いている内容(実現したいこと)として、
    ・strConnection(SQLserverへの接続文字列)を使用したい
    ・ですが、当該コード
    With cat
    Set .ActiveConnection = CurrentProject.Connection '**←接続先がMSになります**
    With .Tables(strTblName)
    .Properties("Jet OLEDB:Link Provider String") = strConnection '**Jetは、MSの接続プロバイダ**
    .Properties.Refresh
    End With
    End With

    のように、「CurrentProject.Connection」「Jet Provider」を使用すると、MSに接続する
    ロジックになり、本質問にあるように矛盾が生じるような気がします。
    ADOXを使用して、更新や削除を行い、且つその元となるデータはSQLserverに接続したい場合、
    ネットやサンプルを調べても「CurrentProject.Connection」「Jet Provider」のキーワードのものしか出てこず、
    ここが私が混乱している所以です。
    ベテランの方から聞けばとんちんかんな質問をしているのかもしれませんが、
    不明店などございましたら追記いたしますので、どうかご教示を再度いただけないでしょうか。

    よろしくお願いいたします。

    キャンセル

  • 2016/05/24 11:08

    あくまで推測なのですが、
    .Properties("Jet OLEDB:Link Provider String") = strConnection

    .Properties("SQLOLEDB:Link Provider String") = strConnection
    とするやり方などがあるということでしょうか…。

    キャンセル

  • 2016/05/24 16:24

    「やろうとしていること」「ソースの意味」を誤解しているように感じましたので、ソースコードでやっている操作内容を回答に追記しました。

    多分、ソースコードの理解より、やろうとしている仕様理解の部分でつまずいているようです。
    ちょっと先入観を捨てて、今やろうとしている処理の仕様を普通の言葉で(日本語で)整理してみましょう。

    キャンセル

  • 2016/05/24 17:14 編集

    Suenaga様
    一つの質問に長く、大変丁寧にお答えいただき感謝のあまり言葉もありません。

    >このソースは、操作対象はあくまで「AccessDB」です。
    …なるほど、そうですよね。
    うすうすそのような気はしていたのですが、前述の通り現在、上司からのヒントのキーワードを元に
    構想して、調べて…といった次第ですので、「SQLserver接続文字列が必要」というキーワードが
    本来あったため、先を急ぎ焦っていたようです。
    追記のご回答、拝読いたしましたが大変わかりやすく勉強になりました。
    ありがとうございます。

    >AccessDB内のリンクテーブル:strTblNameの .Properties("Jet OLEDB:Link Provider String") つまり接続文字列を、新たに設定したい接続文字列に変更。
    つまり、この更新プログラムにおいては、新たに更新したい接続文字列=既存のAccessDBのリンクテーブルへの接続文字列
    という認識でよろしいでしょうか?
    この一点、再三の質問で恐縮なのですが今一度ご教示をいただけませんか。
    ※追記
    前回の質問https://teratail.com/questions/35484の2016/05/21 00:06 編集 を
    よく確認しておきます。
    失礼いたしました。

    >多分、ソースコードの理解より、やろうとしている仕様理解の部分でつまずいているようです。
    >ちょっと先入観を捨てて、今やろうとしている処理の仕様を普通の言葉で(日本語で)整理してみましょう。
    Suenaga様のご指摘の通りですね…お恥ずかしいです。
    一度整理して、そちらは新たにまた質問してみたいと思います。

    今回は、大変勉強になりました。
    ご回答が教材のようで、今後も何度も目を通させていただくことになると思います。
    ありがとうございました。

    キャンセル

  • 2016/05/24 17:51

    suenaga様
    2016/05/24 17:14 のコメント以降の追記
    について。
    何度も本当にありがとうございます。
    わからない事だらけで現場で冷や汗の日々ですが、本当に助かっており心強いです。
    私の曖昧な表現に対応していただき、感謝に尽きます。
    リンクテーブルについては、表題どおりいまひとつ理解がしきれておらず、
    ネット上ではなかなか私が理解できるようなものが無く困っていました。

    やっと理解につながりました。
    ありがとうございます!!!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • VBA

    1854questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • SQL Server

    602questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

  • Access

    453questions

    Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。