Access VBA Access内のテーブルに格納した文字列を呼び出し、それを使ってSQLサーバのデータを取得したい 指定データ取込/フォームに実装

Question

よろしくお願いいたします。
フォーム画面にボタンとテキストボックスを用意して、
hogeFrom とhogeToとしたテキストボックスに検索条件を入力し、
ボタンを押下すると、accessで作ったテーブル（ChargeInfo）のデータ（接続文字列）を使って、
SQLサーバにあるテーブル”hogehogeCountry”の内容を取得し、フォーム上のリストに反映されるようにしたいです。

本日同様の質問をしておりますが、一から自分で書いたコードのため、エラーが出るたびに進めなくなり、解決するたびに新しいエラー内容を記載し、再質問をさせていただいております。
（こういった投稿の仕方が違反であれば、教えていただければ幸いです）

本件では、コード内にあるエラー箇所、
objCon.ConnectionString = strSql
の部分について主にご教示いただきたく存じます。
strSql の部分に、SQLサーバに接続するための接続文字列が入ったテーブルの列を取得するSQL文を代入しています。
ですが、前回のご指南により、
「ConnectionString は実行したいSQLを設定するとこではなく接続するための文字列を設定するとこです」
から、どのようにしてSQLを実行した値をうまく接続文字列として使い、SQLサーバにあるテーブル”hogehogeCountry”の内容を取得ることができるのか、試行錯誤の末わからなくなってきました。

有識者の方々お知恵をお貸しください。

※備考
・リストに表示したいデータは、SQLサーバの”hogehogeCountry”に格納されている
・SQLサーバへの接続文字列は、同一Accessファイル内のテーブルに格納していて、strSqlとして使用したい
・汎用性を高めるため、接続文字列は直接コードに書かず、Accessのテーブルに格納して呼び出して使いたい
・コード内にあるように、ConnectionStringの使用にこだわっているわけではなく、望む結果になるのであれば他の方法でもかまわない

' On Error GoTo Err_データ取得_Click

Dim invFrom As Variant
Dim invTo   As Variant

invFrom = Me.invoiceFrom.Value
invTo = Me.invoiceTo.Value

   If IsNull(invoiceFrom) And IsNull(invoiceTo) = True Then    '検索条件が未入力の場合

        MsgBox "検索条件を指定してください"
       Exit Sub
   

 End If
//（もしテキストボックスのいずれか（もしくは両方）に、検索条件として値が入っていた場合）
以下より実現したい内容と、そのコード。
・データを取得してリスト表示
・データは、SQLサーバの”hogehogeCountry”に格納されている
・SQLサーバへの接続文字列は、同一Accessファイル内のテーブルに格納していて、strSqlとして使用する

   If Not IsNull(invoiceFrom) Or IsNull(invoiceTo) = True Then   'いずれかが入力されている場合



’データをリストに表示
Dim objCon      As New ADODB.Connection
Dim objRs       As New ADODB.Recordset
Dim strSql      As String
   
   Set objCon = Application.CurrentProject.Connection

   strSql = "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1"
   
   **objCon.ConnectionString = strSql **//ここでエラー
   objCon.Open
   objRs.Open "hogehogeCountry", objCon, adOpenKeyset, adLockOptimistic
   
   Set Me.Recordset = objRs
   
   objRs.Close
   objCon.Close
   
   Set objRs = Nothing
   Set objCon = Nothing
   
 End If
```。

Answer 1

ConnectionStringにはデータベースに接続するための接続文字列を指定します

詳しくは
接続文字列の構文
辺りを参考にしていただくとして
エクセル大事典：Microsoft SQL Serverに接続してみよう！
辺りをさらっと読んでいただければ、VBAからSQLServerへの接続は大丈夫ではないでしょうか

Comments

[nekomura]

dojikko様

まだ基礎がうまく固まっていないところもあるので、大変勉強になりました。
ありがとうございます。
ConnectionStringに接続文字列を使って、データベースに接続することはできるようになったのですが、
その接続文字列を、コードに直接記述せずに、どこかに格納して変数として使いたいのです。
今回チャレンジしているのは、SQLサーバーへ接続する接続文字列を同一アクセスファイル内のテーブルに格納し、それを呼び出すSQL文を、本質問内容のものにしています（strSql）。
しかし、ConnectionStringを使うと、直接接続文字列を代入はできますが、文字列を参照するSQL文を入れた変数を代入するとエラーになります。

調べてもなかなかうまくいかず、こちらで質問をさせていただきました。
私の説明が未熟なため、うまくお伝えすることができずお手数をおかけしてもうしわけありません。
今一度、お知恵を貸していただければ幸いです。
よろしくお願いいたします。

Answer 2

1.「ConnectionString」はDBに接続するのに必要な情報です。詳しくは→ConnectionString プロパティ:MSDN
これとUserId、Passwordを使用してADODB.Connection#Openを実行することでDBに接続が完了します。
2.上でOpenされたConnection#Executeで作成したSQLを実行することで取得結果がADODB.Recordsetオブジェクトとして帰ってきます。
なので、

Dim objCon      As New ADODB.Connection
Dim objRs       As New ADODB.Recordset
Dim strSql      As String

Set objCon = Application.CurrentProject.Connection

objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=serverName;Initial Catalog=databaseName; User ID=userName;Password=userPassword;"

objCon.Open

strSql = "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1"

Set objRs = objCon.Execute(strSql)

Set Me.Recordset = objRs

'以下、レコードセットへのアクセス

…のようにすればよろしいかと。

Comments

[nekomura]

tkturbo様
昨日より何度もアドバイスをご教示いただきありがとうございます。
お蔭様で少しずつ前進できております。

私の質問の仕方が悪く申し訳ありません、実は接続文字列を使用してSQLサーバーのデータを取得することはできています。

ただ、その際の接続文字列をコード内に直接記述するのではなく、
同一アクセスファイル内のテーブルにその文字列を格納しておいて、
変数？つまりstrSql として使いたいのです。
（接続文字列が変わったり増えたりしたときの汎用性を考えて）
strSql のSQL文は、SQLサーバーのデータに対してでなく、文字列を格納した同一アクセスファイル内のテーブルに対しての命令です。

しかし、まず同一アクセスファイル内のテーブルを呼び出す構文が今ひとつはっきりわからないこと、ConnectionStringには、文字列を直にしか？入れられないようなので、
どのように記述をすればよいのかがまだ私のレベルでは実現できません。

つまり、
・アクセスのテーブルに接続文字列を格納（格納場所は、 "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1"　　変数はstrSqlとしたい）
・それをConnectionString に使いたいが、文法上エラーとなるため、他の方法もしくは
記述方があれば知りたい
といった感じです。

私の伝え方が未熟なため、何度もお手数をおかけして申し訳ございません。
可能であれば、上記内容について今一度ご教授いただけれませんでしょうか。

Answer 3

つまり、
・アクセスのテーブルに接続文字列を格納（格納場所は、 "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1" 変数はstrSqlとしたい）
・それをConnectionString に使いたいが、文法上エラーとなるため、他の方法もしくは
記述方があれば知りたい
といった感じです。

たとえば

Set DB = CurrentDb()

とでもしておいて

CurrentDbメソッドとは、Setステートメントと組み合わせて、データベース変数にカレントデータベースへの参照を代入するメソッドです。
データベース名がわからなくても、VisualBasicコードからカレントデータベースにアクセスすることができます。

あとは

SQLCommand = "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1"
Set q1 = DB.OpenRecordset(SQLCommand)

でレコードセットを取得しておいて

strSql = q1![ColName]

でレコードセットから値(SQLServerへの接続文字列)が取れる

みたいな感じでどうでしょう
いろんなやるべき処理をすっ飛ばしているので各々補完してください

Comments

[nekomura]

dojikko様
早々のご回答ありがとうございます（涙）
これを元にいろいろと試してみます。

[nekomura]

ありがとうございました。

大変申し訳ないのですが、未熟なためなかなかうまく実装ができず、
新たなエラーなども発生したので、
dojikko様のご意見を参考にして再度質問を上げさせていただきます。
また、お手すきの際はご指南いただければ幸いです。
このような行為は違反などあれば併せてご教示ください。