ネットワークインターフェイス：指定された Server/Instance の位置を特定しているときにエラーが発生しました

Question

下記コードでSQLServerへ接続しようとすると
ネットワークインターフェイス：指定された Server/Instance の位置を特定しているときにエラーが発生しました
というエラーが発生してしまいます。
ネット等で自分なりに調べてみてはいるのですが、中々うまくいきません。
何かしらアドバイスがいただけたらありがたいです。

Sub Mysql_連続insert()

'ExcelでADO・ADODBへの参照設定で下記をチェック（古いバージョンでも可）
'Microsoft ActiveX 'Data Objects 6.1 Library


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim ConnectionString As String
Dim sqlStr As String

Dim STUDENT As String
Dim NAME_S As String
Dim NAME_S_F As String
Dim NAME_N As String
Dim GENDER As String
Dim BIRTHDAY_Y As String
Dim BIRTHDAY_M As String
Dim AGE As String
Dim EDUCATION As String
Dim GRANDUATIN_Y As String
Dim GRANDUATIN_M As String
Dim ADDRESS_P As String
Dim ADDRESS_T As String
Dim ADDRESS_P_F As String
Dim ADDRESS_T_F As String
Dim NEAREST_L As String
Dim NEAREST_S As String
'接続文字列
ConnectionString = "Driver={SQL Server Native Client 11.0};" _
                & " SERVER= DESKTOP-09F2LJG\SQLEXPRESS" _
                & " DATABASE= test" _
                & " USER= root" _
                & " PASSWORD= root"

'ADODB.Connection生成
Set cn = New ADODB.Connection

On Error GoTo Err

'MySQLに接続
cn.Open ConnectionString

For i = 2 To 6000

     With Sheets("alldata")
        STUDENT = .Range("C2").Value
        NAME_S = .Range("D2").Value
        NAME_S_F = .Range("E2").Value
        NAME_N = .Range("F2").Value
        GENDER = .Range("G2").Value
        BIRTHDAY_Y = .Range("H2").Value
        BIRTHDAY_M = .Range("I2").Value
        AGE = .Range("J2").Value
        EDUCATION = .Range("K2").Value
        GRANDUATIN_Y = .Range("L2").Value
        GRANDUATIN_M = .Range("M2").Value
        ADDRESS_P = .Range("N2").Value
        ADDRESS_T = .Range("O2").Value
        ADDRESS_P_F = .Range("P2").Value
        ADDRESS_T_F = .Range("Q2").Value
        NEAREST_L = .Range("R2").Value
        NEAREST_S = .Range("S2").Value
        
    End With

        sqlStr = "insert into BASIC_INFORMATION (STUDENT,NAME_S,NAME_S_F,NAME_N,GENDER,BIRTHDAY_Y,BIRTHDAY_M,AGE,EDUCATION,GRANDUATIN_Y,GRANDUATIN_M,ADDRESS_P,ADDRESS_T,ADDRESS_P_F,ADDRESS_T_F,NEAREST_L,NEAREST_S) values ('" & STUDENT & "','" & NAME_S & "','" & NAME_S_F & "','" & NAME_N & "','" & GENDER & "','" & BIRTHDAY_Y & "','" & BIRTHDAY_M & "','" & AGE & "','" & EDUCATION & "','" & GRANDUATIN_Y & "','" & GRANDUATIN_M & "','" & ADDRESS_P & "','" & ADDRESS_T & "','" & ADDRESS_P_F & "','" & ADDRESS_T_F & "','" & NEAREST_L & "','" & NEAREST_S & "')"
        Set rs = cn.Execute(sqlStr)

Next

cn.Close
Set rs = Nothing
Set cn = Nothing

MsgBox "データの登録がおわりました"
Exit Sub

Err:
    Set rs = Nothing
    Set cn = Nothing
    MsgBox (Err.Description)

End Sub

Answer 1

openできないなら空のテキストファイル作って拡張子を「udl」にしてダブルクリックして
「データリンクプロパティ」起動してその画面で接続することができれば
udlファイルをテキストとして開いてconnectionstringにすればいいです。

sql server 構成マネージャーでtcpipを有効にしているのか

ここら辺も重要です。

Comments

[tokumeikibou162]

なるほど、そういうやり方もあるんですね。
udlファイルをテキストとして開いてconnectionstring
というのはテキストファイルで開いたら出てくる接続文字列を張り付けるということですかね？

tcp/ipに関しては一応有効にしています。

[sousuke]

その理解でオッケーです。

[tokumeikibou162]

有難うございます。

Answer 2

質問のタイトルですが、Sqlserverを含むキーワードがないので、ネットワーク障害の質問かと思いました。

sqlserverが手元にないので検証できないのですが・・・。

素人考えですが
For i = 2 To 6000

とありますので、実データがないのに空白データを登録しようとしてエラーとなっている可能性が
あります。ループ回数を減らして実データのみをinsertしてみるとよいかと思います。

例えばですが、テストテーブル・項目を３つくらいに限定して実施してみたら成功したが、
本稼働を想定した項目では失敗した。などの投稿をしていただけるとよいと思います。

下記の部分をコメントアウトすると、エラー箇所でプログラムが止まるので
原因について追加で報告いただくと原因がより特定されるかもしれませんね。

'On Error GoTo Err

'Err:
' Set rs = Nothing
' Set cn = Nothing
' MsgBox (Err.Description)

Comments

[tokumeikibou162]

なるほど、試してみます。

コメントアウトに関しては考えつきませんでした。勉強になります。
コメントアウトして実行してみたところ-2147467259（80004005)が発生しました。
エラー箇所はcn.Open ConnectionStringです。

[mako1972]

sqlserverにはWIndows認証方法とID、パスワードと2種類の接続方法があります。
今回の例でいうと、接続文字列の部分がおかしいような気がします。

'接続文字列
ConnectionString = "Driver={SQL Server Native Client 11.0};" _
& " SERVER= DESKTOP-09F2LJG\SQLEXPRESS" _
& " DATABASE= test" _
& " USER= root" _
& " PASSWORD= root"

こちらのサイトを参照に、接続文字列がただしく行われているか
コードを編集してためされるとよいかと思います。

データベース（SQL Server）に接続する（ADO）
https://excelwork.info/excel/databasesqlserver/

※接続文字列だけでなく、下記のテーブル名の修正もわすれずに。
strSQL = "Select * from T_SYAIN;

[tokumeikibou162]

承知しました。

[mako1972]

下記サイトも参考になりそうです。

SQLServerに接続＠Excel マクロ・VBA
http://www.cocoaliz.com/excelVBA/index.php/10/