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

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

ただいまの
回答率

88.58%

Access DAO→ADO接続に変更 ADO接続の正しいロジックを教えてください。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 24K+

nekomura

score 132

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

Accessで、テーブルデータやクエリを取得して使用するプログラムを作っています。
元のコードは、自身のテーブルデータやクエリからDAOを使用してデータを取得しています。
同様の内容を、
・Access自身のテーブル→SQLserverからのリンクテーブル(ODBC)
・DAO接続→ADO接続

と、書き換えたいのですが、ADO接続の方のコードの書き方が合っているか調べてもピンとこないのと、
エラーが出る為困っています。

元のコードは以下です。

Dim db  As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset

    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("顧客マスタ") //Accessテーブル
    Set rs2 = db.OpenRecordset("Q売上") //Accessクエリ
    Set rs3 = db.OpenRecordset("Q売上明細") //Accessクエリ


'-- いろんな処理

上記のコーディングで、問題なく目的のデータを取得できます。

同内容を、テーブルはSQLserverからのリンクテーブル(ODBC)を作り、ADO接続を用いて
同じようにデータを取得したいのですが、うまくいきません。

問題のADO接続のコードは以下です。
※追記(下記ADO接続コードにおける各テーブルとクエリの説明)
・顧客マスタ→SQLserverにあるテーブル。Accessにあるテーブルと同名のテーブル(顧客マスタ)。
リンクテーブルとしてAccessにおいてあります。
・Q売上→上記リンクテーブルを使用して作ったクエリ
・Q売上明細→通常のAccessのクエリ

Dim cn As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim rs3 As New ADODB.Recordset

    Dim strConnectionString As String  'SQLServer接続文字列

    strConnectionString = CStr(DLookup("connectionString", "接続文字列テーブル", "ID= 2")) 
    //Access自身の"接続文字テーブル"に、SQLserverの接続文字列を格納し、取得しています。
 //このロジックで接続文字列は問題なく変数に代入されています。

    cn.Open strConnectionString

    rs1.Open "顧客マスタ", cn, adOpenForwardOnly, adLockReadOnly //リンクテーブル
    rs2.Open "Q売上", cn, adOpenForwardOnly, adLockReadOnly //リンクテーブルで作ったクエリ
    rs3.Open "Q売上明細", cn, adOpenForwardOnly, adLockReadOnly //Accessクエリ
    //上記の3行、レコードセット.Openのところがデバックでカーソルのあたる箇所

'-- いろんな処理


・エラー箇所は一番下の3行、レコードセット.Openのところで、
・[実行時エラー'2147217900(80040e14)':
ストアドプロシージャ’テーブル名(またはクエリ名)’が見つかりませんでした。]

と、エラーメッセージが出ました。
また、接続文字列に誤りはありません。

コーディングの仕方に問題があるのかと思い、ここで相談させていただきました。
有職者の皆様方、どうかご教示のほどをよろしくお願いいたします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • ttyp03

    2016/05/12 11:07

    以前の質問と同様かと思いますが、確認です。
    SQLServerには、Accessにあるテーブルと同名のテーブル(顧客マスタ、Q売上、Q売上明細)があり、それらのテーブルをAccessのVBAから参照したい、ということで正しいですか?

    キャンセル

  • nekomura

    2016/05/12 11:13

    ttyp03様
    ご指摘ありがとうございます。
    >SQLServerには、Accessにあるテーブルと同名のテーブル(顧客マスタ、Q売上、Q売上明細)があり、それらのテーブルをAccessのVBAから参照したい
    はい。実際にSQLserverにはそのテーブルがあり、リンクテーブルとしてAccessにおいてあります。
    しかし、顧客マスタ以外はクエリです。
    コードの中にコメントとして書いていただけなので、早速本文も編集します。
    ありがとうございました。

    キャンセル

回答 2

checkベストアンサー

+2

せっかくソースコードをかけているので、その意味を考えることで解決しましょう。

Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("顧客マスタ") //Accessテーブル

この意味は理解できているんですよね。
これと、

cn.Open strConnectionString
   rs1.Open "顧客マスタ", cn, adOpenForwardOnly, adLockReadOnly //リンクテーブル

を対比して考えましょう。
「db」はなんですか。
「cn」はなんですか。
おまじないとしてではなく、言葉でそれを考えてみると、どうすればいいか、には辿り着くと思います。

まずそこからいってみましょう。


(2016/05/12 14:07 のコメントの続きです。)

1つのソースコードの中でSQLServerにもAccessにも接続したいのか、SQLServerへの接続はAccessのリンクテーブルを通じて行うのかで回答が変わりますが、仮に後者の場合、

ADOのコードに
Set cn = CurrentProject.Connection

が正解です。
元のソースコードの

Dim cn As New ADODB.Connection

Dim strConnectionString As String  'SQLServer接続文字列

strConnectionString = CStr(DLookup("connectionString", "接続文字列テーブル", "ID= 2")) 

cn.Open strConnectionString

が、

Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection

に置き換えれば動作するでしょう。


(2016/05/12 18:57 のコメントの続きです。)

>変数の「参照渡し」と「値渡し」を知っていますか
javaの資格を取る際に、勉強したので意味としてはわかります。

>↓の2つのコードの違いを理解できますか。
 Dim cn As New ADODB.Connection
 Dim cn As ADODB.Connection

実はこれは以前から気になっていたのですが、ネットなどでサンプルコードをみると、
 Dim cn As ADODB.Connection
 の方は、後ほど変数にNEWを代入してインスタンス化していますが、
 Dim cn As New ADODB.Connection
と初めから宣言しているとその後のインスタンス化は不要なのかなぁ…と、
ぼんやりとしかわからないので気になっていました。

あ、じゃあ説明はしやすいですね。

VBAの場合、大前提として Dim は変数の定義です。
その際、オブジェクト型の変数の場合、 New をつけることで同時にインスタンス化されます。

細かいことを抜きにして、

Dim obj as New object  ' 型宣言とインスタンス化を同時に行う


Dim obj as object     ' 型宣言
Set obj = New Object  ' インスタンス化

は同義です。

オブジェクト型以外のほとんどの変数の場合、VBAでは変数の代入は値渡しで行われるのですが、オブジェクト型の場合は参照渡しで行われます。

Dim obj1 As Object
Dim obj2 As Object

Set obj1 = New Object
Set obj2 = obj1

この場合、変数 obj2 は obj1 への参照となります。
つまり obj2 への操作は obj1 にも影響します。

Dim obj1 As Object
Dim obj2 As Object

Set obj1 = New Object
Set obj2 = New Object

Set obj2 = obj1


この場合、動作は結局一緒なのですが、obj2 として生成されたインスタンスは、obj1 への参照に上書きされ消えます。
(実動上は無駄なだけなはず)

で 2016/05/12 14:57 のコメントにあった下記のソースの

Dim cn As New ADODB.Connection 'SQLserverへの接続 
● Dim cn2 As New ADODB.Connection 'Accessへの接続
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset

Dim strConnectionString As String 'SQLServer接続文字列

strConnectionString = CStr(DLookup("connectionString", "接続文字列テーブル", "ID= 2"))
●Set cn2 = CurrentProject.Connection

cn.Open strConnectionString
●’cn2.Open ←ここでは実行時エラー'3705'「オブジェクトが開いている場合は、操作は許可されません」とエラーが出る為一旦コメントアウト中です。
        この記述は、不要もしくは違う箇所に書かなければなりませんか?ご教示いただければ幸いです。

rs1.Open "顧客マスタ", cn, adOpenForwardOnly, adLockReadOnly //エラー
● rs2.Open "Q売上", cn2, adOpenForwardOnly, adLockReadOnly
● rs3.Open "Q売上明細", cn2, adOpenForwardOnly, adLockReadOnly

cn2.Open でエラーが出る理由は、Dim cn2 As New ADODB.Connection としてインスタンスを生成したものの、その後で Set cn2 = CurrentProject.Connection をすることにより、cn2 はCurrentProject.Connection への参照となっています。

つまりいま開いているAccessファイルへの接続なので、さらにOpenはできないため、エラーが出ます。

Dim cn2 As ADODB.Connection
Set cn2 = CurrentProject.Connection


で、cn2 は既に開いたAccessDBへの接続になっているわけです。

という事でほぼ理解上は解決するのではないでしょうか。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/05/12 18:57

    事前学習という事であれば、AccessとSQLServerそれぞれに接続してそれぞれからデータを取得する、という事をやっておいたほうがいいかもしれませんね。

    また、リンクテーブルの利用は便利ではあるんですが大きな問題があり、例えば、

    - 顧客テーブル がSQLServerにある
    - そのテーブルに対してAccessでリンクテーブルを作る(仮にその名前を「リンク_顧客テーブル」)

    とした際に、

    SELECT * FROM リンク_顧客テーブル WHERE id = 1

    といったクエリを発行すると、

    - 1度SQLServerからAccessが顧客テーブルの全データを取得(つまり リンク_顧客テーブル に相当するデータを取得)
    - その上で WHERE句の解釈

    という動作をするため、顧客テーブルが大きくなればなるほど構造的に重く・遅くなることになります。
    そのあたりを踏まえて、

    - どこにデータがあるのか
    - この処理に必要なデータはどれか
    - 処理のプロセスで不必要にデータが大きくならないか

    といったことを検討した設計をすべきでしょう。

    参照渡しやnew のあたりの話はコメントだと重いので回答を編集します。

    キャンセル

  • 2016/05/12 19:19

    【おまけ】
    キーワード理解、といういみでは、ADOのConnectionオブジェクトのリファレンスを眺めることをおすすめします。
    https://msdn.microsoft.com/ja-jp/library/cc364251.aspx?f=255&MSPPError=-2147217396

    特にプロバイダの意味をわかると、ADOが汎用的、とか、ODBCとの関係、とか、接続文字列の意味、とか色々つながるかと思います。

    キャンセル

  • 2016/05/12 22:25 編集

    Suenaga様
    たくさんのご教示、本当にありがとうございます!!!
    今日は、とても勉強になりました。
    長い間、本日教えていただいたことの理解が出来ず、
    周りには質問することが出来ない状況だった為に本当に助かりました。
    ネットのやりとりでこんなにわかやすかったのは初めてで、感激です。

    たくさんのご回答、勉強しながら何度も読み返しております。
    本当にありがとうございます。

    >特にプロバイダの意味をわかると、ADOが汎用的、とか、ODBCとの関係、とか、接続文字列の意味、とか色々つながるかと思います。

    ばらばらだったこれらの、解決の糸口が見つかりとてもうれしいです!

    キャンセル

+1

すみません、上の所だと改行できないのでこちらで再確認させていただきます。
質問内容を読み取ると、構成としては、

<SQLServer>
顧客マスタ
<Access>
顧客マスタ(SQLServerへのリンクテーブル)
Q売上(Access
Q売上明細

こんな感じでしょうか。
だとすると、

    rs2.Open "Q売上", cn, adOpenForwardOnly, adLockReadOnly //リンクテーブルで作ったクエリ
    rs3.Open "Q売上明細", cn, adOpenForwardOnly, adLockReadOnly //Accessクエリ

この2つはできませんよね。
SQLServer上には存在しませんから。
顧客マスタに関してはSQLServerに「顧客マスタ」としてテーブルが存在し、接続文字列がきちんとSQLServerを指しているのなら問題ないように思えますが。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/05/12 14:38

    多少語弊もあるんですが、DAOとADO、役割として、ローカルファイルへのアクセスを前提としたDAOと、リモート接続を前提としたADO、といったイメージです。

    DAOの場合はJetDB(昔のAccessの mdb ファイル形式のもの)への接続専用、ADOの場合は接続文字列で汎用的に接続可能、という感じです。

    キャンセル

  • 2016/05/12 14:42

    kaz.Suenagaさん>
    解説ありがとうございます。為になります。
    ADOの方が汎用的に使えるとなると、将来的にもADOを使っておいた方が融通が利きそうですね。

    キャンセル

  • 2016/05/12 14:54

    ttyp03様
    Suenaga様
    なるほど…。
    とても為になります。
    ttyp03様、参考URLもありがとうございます。

    キャンセル

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

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

関連した質問

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