[VBA] 엑셀에서 MSSQL Server 접속하기

ADODB.Connection
ADODB.Recordset
ADODB.Command
 
참조링크
http://www.red.oit-net.jp/tatsuya/vb/ADO.htm
http://acvb.s19.xrea.com/doc/015.html
http://blogs.msdn.com/nakama/archive/2008/10/16/ado.aspx
http://www.geocities.jp/cbc_vbnet/ADO/recordset.html
http://www.red.oit-net.jp/tatsuya/vb/ADO.htm
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/vb_s12.htm

VBでADO利用EXCEL接続
http://support.microsoft.com/kb/257819/ja
http://www.devpia.com/dExpert/?Op=tv&Seq=400

한국자료
http://blog.naver.com/0new?Redirect=Log&logNo=50017613914

엑셀에서 데이타베이스(MS-SQL) 접속해서 데이타 INSERT하기

' 注意
' ツール > 参照設定 > Microsoft ActiveX Data Objects x.x Library 設置が必要

Private Sub CommandButton1_Click()
   
    If MsgBox("データベースに反映してもよろしいですか？", vbYesNo + vbQuestion, "処理実行確認") = vbYes Then MS_SQL_DataBase

End Sub

Private Const DB_Connection As String = "Provider=SQLOLEDB.1;Password=패스워드;Persist Security Info=True;User ID=아이디;Initial Catalog=서버이름;Data Source=아이피"

 Sub MS_SQL_DataBase()
    Dim SQL         As String
    Dim oCmd       As ADODB.Command
    Dim i              As Long
    Dim j              As Long
    Dim Total        As Long
   
    ' 項目名(省略してもいい)
    Dim s항목1      As String
    Dim s항목2      As String
    Dim s항목3      As String
    Dim s항목4      As String
   
    Dim DELETE_SQL  As String
    Dim CHECKIDENT  As String
    Dim INSERT_SQL  As String
   
    ' DB接続
    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = DB_Connection
    oCmd.CommandType = adCmdText
   
    ' 既存のデータ削除
    DELETE_SQL = "DELETE FROM 그룹.테이블명"
    oCmd.CommandText = DELETE_SQL
    oCmd.Execute
   
    ' ID値を初期化
    CHECKIDENT = "DBCC CHECKIDENT (테이블명, RESEED, 0)"
    oCmd.CommandText = CHECKIDENT
    oCmd.Execute
   
    ' Excelのデータ数取得
    Total = CLng(Range("A1").End(xlDown).Row) - 1     ' 前から数える

    ' Total = CLng(ActiveSheet.Range("A65536").End(xlUp).Row) - 1　' 後ろから数える
 
    For i = 0 To Total
        'データ設定
        s항목1 = Range("A1").Offset(i)
        s항목2 = Range("B1").Offset(i)
        s항목3 = Range("C1").Offset(i)
        s항목4 = Range("D1").Offset(i)
   
        ' 取込
        INSERT_SQL = "INSERT INTO 그룹.테이블명 (항목1, 항목2, 항목3, 항목4) VALUES (" & s항목1 & ", '" & s항목2 & "', '" & s항목3 & "', '" & s항목4 & "')"
        oCmd.CommandText = INSERT_SQL
        oCmd.Execute
    Next
   
    Set oCmd = Nothing
   
End Sub