ExcelからAccessのテーブルの値の取得ができない

質問

Excel2003からAccess2003のDBの値を取得するVBAを作成中なのですが、
ExcelのフォームにてTextBox1に入力した社員番号からネットワーク接続されたサーバーにあるmdbファイルから値を取得する内容で
以下のエラーが表示されます。
　「実行時エラー '-2147217904 (80040e10)':
　　1つ以上の必要なパラメータの値が設定されていません。」
デバッグをすると、「rs1.Open sql1, con」の箇所と指摘されます。
以下にコードを貼り付けますので、間違っている点のご指摘をお願いいたします。

Private Sub CommandButton2_click()

Dim shainID As Long
Dim constr As String '接続文字列の定義
Dim con As ADODB.Connection
Dim rs1 As ADODB.Recordset

If TextBox1.Value = "" Then

MsgBox "社員番号を入力してください", vbOKOnly + vbCritical, "社員番号入力エラー"
TextBox1.SetFocus

Else

shainID = TextBox1.Value

Range("B13").Value = shainID

'Connectionの設定
Set con = CreateObject("ADODB.Connection")
constr = "provider = Microsoft.Jet.OLEDB.4.0;Data Source = \\192.168.1.100\ShainDB\shaindb.mdb"

'DB接続
con.Open constr

'RecordSetの作成
Set rs1 = CreateObject("ADODB.Recordset")

'SQL
sql1 = "select 社員漢字氏名,性別,生年月日,電話番号,住所 from 社員テーブル where 社員番号 = shainID"

If sql1 = "" Then

MsgBox "入力した社員番号の社員はいません。", vbOKOnly + vbCritical, "社員番号入力エラー"
TextBox1.SetFocus

Else

'SQLを実行して対象をRecordSetに入れる
rs1.Open sql1, con
Set rs1 = con.sql1

Range("B14").Value = 社員漢字氏名
Range("B15").Value = 性別
Range("B16").Value = 生年月日
Range("B17").Value = 電話番号
Range("B18").Value = 住所
Unload Me 'フォームを閉じる

End If

End If

End Sub

回答No.1

cistronezk · ベストアンサー

＞デバッグをすると、「rs1.Open sql1, con」の箇所と指摘されます。
それ以外のところの方がかなり怪しいですが。ちょっと見ですが、いくつか挙げてみると・・・

＞Dim con As ADODB.Connection
と型宣言できているのに、わざわざ
Set con = CreateObject("ADODB.Connection")
としていること。素直にNewしましょう。
＞Dim rs1 As ADODB.Recordset
についても同様。

＞Set rs1 = con.sql1
これはコンパイルエラーになるはず。そもそも何がしたいのか不明。レコードの取得はその前の
＞rs1.Open sql1, con
でできるはず。（エラーとのことですが）

＞Range("B14").Value = 社員漢字氏名
これでは、データベースからのデータはセルに入れられない。「社員漢字氏名」は、なにも値が設定されていない変数。それをセルに代入しているだけなので。
Range("B14").Value = rs1!社員漢字氏名
とすべき。

全体的にVBAとADOに関する知識がかなりあやふやに思います。もう一度、本を読んで見直してください。
また、かならず「Option Explicit」を設定するようにしてください。ケアレスミスを減らせます。

回答No.2

rivoisu

たくさんありますが
sql1 = "select 社員漢字氏名,性別,生年月日,電話番号,住所 from 社員テーブル where 社員番号 = shainID"
shainIDが数値型の場合
sql1 = "select 社員漢字氏名,性別,生年月日,電話番号,住所 from 社員テーブル where 社員番号 =" & shainID & ";"
shainIDが文字列型の場合
sql1 = "select 社員漢字氏名,性別,生年月日,電話番号,住所 from 社員テーブル where 社員番号 ='" & shainID & "';"

Range("B14").Value = 社員漢字氏名
を
Range("B14").Value =rs1.field("社員漢字氏名")
以下同様

社員がいるかどうかはｓｑｌを実行した後ｒｓ１がEOFかどうかで決める。