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

Question

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

解決済

回答 1

投稿 2016/05/23 11:18 ・編集 2016/05/24 14:32

評価
クリップ 1
VIEW 11K+

nekomura

score 132

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

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

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

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 cat
   ②  Set .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 件

評価が高い順
新着順
古い順

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

score 2 · Accepted Answer · 2016-05-23T15:10

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

不明点キーワード：
・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の体験を活かしてコレクションやオブジェクト、プロパティを理解するとわかりやすいのではないでしょうか。

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

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

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

DBとDBMS

DBへのインターフェイス

ODBC

ADO

OLE DBプロバイダ

Accessの動作と接続文字列

ソースコード部分

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

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

関連した質問

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

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

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

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

DBとDBMS

DBへのインターフェイス

ODBC

ADO

OLE DBプロバイダ

Accessの動作と接続文字列

ソースコード部分

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

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

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

関連した質問

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

SNSアカウントでログイン

teratailアカウントでログイン

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