Access VBA Access内のテーブルに格納した文字列を変数として使用し、SQLサーバのデータを取得したい 指定データ取込/フォームに実装

Question

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

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

本件では、
** strSql = "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1"
objCon.ConnectionString = strSql （ConnectionStringは接続文字列を指定するものなので、これではエラーになります）
の部分についてお伺いします。**

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

ConnectionStringには、文字列を直にしか？入れられないようなので、
どのように記述をすればよいのかがまだ私のレベルでは実現できません。

つまり、
・アクセスのテーブルに接続文字列を格納（格納場所は、 "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1" 変数はstrSqlとしたい）

・そのstrSqlを、ConnectionString(または他の方法でも) **に使い,SQLサーバにあるテーブルの内容を取得したい。
**
・リストに表示したいデータは、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 DB = CurrentDb() '
'    SQLCommand = "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1" '
'    Set q1 = DB.OpenRecordset(SQLCommand) '

   
   
   Set Me.Recordset = objRs
   
   objRs.Close
   objCon.Close
   
   Set objRs = Nothing
   Set objCon = Nothing
   
 End If
```。

Answer 1

データベース接続文字列とそれを取得するためのSQL文を混同していませんか
SQL文は実行してはじめて結果になります
→なのでADODB.ConnectionのConnectionStringにデータベース接続文字列を取得するためのSQL文を書いても動かない

全体の流れとしては

• データベース接続文字列を取得する→今回はAccessのテーブルに入っているんですよね
• 取得したデータベース接続文字列を変数strSqlに入れる
• strSqlに入っているデータベース接続文字列を元にしてSQLServerに接続する→データベース接続文字列を直書きしたものはうごくんですよね

となります

とりあえず引っかかっていそうな

• データベース接続文字列を取得する
• 取得したデータベース接続文字列を変数strSqlに入れる

のコードを簡単に書いてみました

多分動くと思います

ChargeInfoというテーブルにIDとCOL1という列があるのが前提です

Private Sub test_getConnectionString()

    Dim DB As DAO.Database
    Dim q1 As Recordset
    Dim SQLCommand As String  'データベース接続文字列を取得するSQL文
    
    Dim strSql As String  '取得したデータベース接続文字列
    
    Set DB = CurrentDb()
    SQLCommand = "SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1"
    Set q1 = DB.OpenRecordset(SQLCommand)
    strSql = q1!COL1

    MsgBox (strSql)

End Sub

Comments

[nekomura]

dojikko様
この回答のおかげで、とうとう解決することができました。
私の素人丸出しのつたない質問の意味を汲んでくださり、感謝の気持ちでいっぱいです。
本当にありがとうございました。

また、本件の解決に向けてアドバイスをくださったたくさんの回答者の方たちへも、併せてお礼申し上げます。

Answer 2

こんな感じですかね？
※オブジェクト開放とかクローズ処理は書いていません。

Dim db As Database
Dim rec As Recordset
Dim sql As String

' access db
Set db = CurrentDb()
' sql文
sql = "select * from [table] where id = 1"
' recordset取得
Set rec = db.OpenRecordset(sql)

' recordsetから欲しいカラム値を取得
dim result As String
reslt = rec("col1").Value

Access VBAの事は調べれば幾らでも出てくるので、ここで躓いていると今後も同じような質問を何十回と繰り返さないと完成しませんよ。
もう少し調べてみましょ。

Comments

[nekomura]

lilithchan様
はい、おっしゃるとおりです。反省。。
ですが、おかげでなんとか解決しました！

lilithchan様のコード、かなり参考にさせていただいた上にとても勉強になりました。
感謝です！！！

[Pun_hiro]

すみません。コードで説明するべきでした。
今後、そうします。すみません。

Answer 3

すみません。

"SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1" をAccessのテーブルで取得すればよろしいのではないでしょうか。
SQLテーブルというのをACCESSのDBで作成し、それをSTRSQL の変数に入れてSQLSサーバーに接続するという手順ではどうでしょう。

1,ACCESSのSQLテーブルを読む、SQL文の取得 →STRSQLに格納
2,SQLサーバーに接続（接続パスもテーブルにする。）ODBC接続？？
3,Sql文の実行

という流れでプログラムを作成されてみてはどうでしょう。

Comments

[nekomura]

Tanaka_hiro様
早々のご回答、まことに感謝いたします。
アドバイスありがとうございます。
ただ、私がまだ初心者なこともあり、お答えいただいた内容をどのように理解すればよいのか、またどのように書くかが
まだよくわかりません。

>SQLテーブルというのをACCESSのDBで作成し、それをSTRSQL の変数に入れてSQLS>サーバーに接続するという手順ではどうでしょう。
すでにAccessには ChargeInfoという名のテーブルを作成しており、
取得したい内容が、"SELECT ID, COL1 FROM ChargeInfo WHERE ID = 1" つまり
ID１　に格納されています。
それを取り出してstrSqlという変数に格納し、
objCon.ConnectionString = ”接続文字列”（←ここに相当する部分に変数を使いたい）
というような結果になるようにしたいと考えています。

もし可能であれば、今一度ご教示のほどいただければ幸いです。

[nekomura]

無事解決いたしました！
また、アドバイスいただきたいです。
ありがとうございました。