excel 連接 sql

趨明 2012-02-16

展開全文

Private Sub 導(dǎo)入_Click()
   Dim i As Integer

   Dim dept_no As String
   Dim dept_name As String

   Set cn = CreateObject("adodb.connection")
   Set rs = CreateObject("ADODB.Recordset")
   strCn = "Provider=sqloledb;Server=INFO2;Database=ww;Uid=sa;Pwd=;"

   Dim strcheck As String

   If MsgBox("你好！是否要執(zhí)行程序需要耐心等待幾分鐘。", 1 + 64, "執(zhí)行程序") = vbOK Then

    cn.Open (strCn)
    strcheck = "select year_month from ww..wpd_prsn where year_month='" + CStr(Range("景盟!A" + LTrim(2)).Value) + "'"
    rs.Open strcheck, cn

    If Not rs.EOF Then
       If MsgBox("你好！此資料已導(dǎo)入數(shù)據(jù)庫，是否需要重導(dǎo)？", 1 + 64, "執(zhí)行程序") = vbOK Then
          strcheck = "delete from ww..wpd_prsn where year_month='" + CStr(Range("景盟!A" + LTrim(2)).Value) + "'"
          cn.Execute strcheck
          cn.Close

        i = 2
        Do While Range("景盟!A" + LTrim(i)).Value <> ""
          If Range("景盟!V" + LTrim(i)).Value = "電腦機編織" Then
            dept_no = "01"
            dept_name = "電腦機"
          ElseIf Range("景盟!V" + LTrim(i)).Value = "縫合" Then
            dept_no = "03"
            dept_name = "縫合"
          ElseIf Range("景盟!V" + LTrim(i)).Value = "手縫" Then
            dept_no = "04"
            dept_name = "手縫"
          ElseIf Range("景盟!V" + LTrim(i)).Value = "整理" Then
            dept_no = "05"
            dept_name = "整理"
          End If

          cn.Open (strCn)
          strcheck = "insert into ww..wpd_prsn(cmp_no,year_month,fact_no,psn_name,dept_no,dept_name,jt_no,xs,grp,ylj,ON_job,upd_date)"
          strcheck = strcheck + " values('F003','" + CStr(Range("景盟!A" + LTrim(i)).Value) + "','" + CStr(Range("景盟!B" + LTrim(i)).Value) + "','" + CStr(Range("景盟!C" + LTrim(i)).Value) + "', "
          strcheck = strcheck + " '" + dept_no + "','" + dept_name + "','" + Right(Range("景盟!W" + LTrim(i)).Value + 10000, 4) + "','" + CStr(Range("景盟!X" + LTrim(i)).Value) + "','','N','Y',getdate())"
          cn.Execute strcheck
          cn.Close
          i = i + 1
        Loop
        MsgBox ("資料重導(dǎo)成功")
        End If
       Else
          cn.Close
          cn.Open (strCn)
          strcheck = "update ww..wpd_prsn set on_job='N'"
          cn.Execute strcheck
          cn.Close

          i = 2
          Do While Range("景盟!A" + LTrim(i)).Value <> ""
            If Range("景盟!V" + LTrim(i)).Value = "電腦機編織" Then
              dept_no = "01"
              dept_name = "電腦機"
            ElseIf Range("景盟!V" + LTrim(i)).Value = "縫合" Then
              dept_no = "03"
              dept_name = "縫合"
            ElseIf Range("景盟!V" + LTrim(i)).Value = "手縫" Then
              dept_no = "04"
              dept_name = "手縫"
            ElseIf Range("景盟!V" + LTrim(i)).Value = "整理" Then
              dept_no = "05"
              dept_name = "整理"
            End If
            cn.Open (strCn)
            strcheck = "insert into ww..wpd_prsn(cmp_no,year_month,fact_no,psn_name,dept_no,dept_name,jt_no,xs,grp,ylj,ON_job,upd_date)"
            strcheck = strcheck + " values('F003','" + CStr(Range("景盟!A" + LTrim(i)).Value) + "','" + CStr(Range("景盟!B" + LTrim(i)).Value) + "','" + CStr(Range("景盟!C" + LTrim(i)).Value) + "', "
            strcheck = strcheck + " '" + dept_no + "','" + dept_name + "','" + Right(Range("景盟!W" + LTrim(i)).Value + 10000, 4) + "','" + CStr(Range("景盟!X" + LTrim(i)).Value) + "','','N','Y',getdate())"
            cn.Execute strcheck
            cn.Close
            i = i + 1
          Loop
          MsgBox ("資料導(dǎo)入成功")
       End If
    End If
End Sub
Private Sub 刪除_Click()
    Dim strcheck As String

    Set cn = CreateObject("adodb.connection")
    Set rs = CreateObject("ADODB.Recordset")
    strCn = "Provider=sqloledb;Server=INFO2;Database=ww;Uid=sa;Pwd=;"

    If MsgBox("你好！確定要刪除此資料嗎？", 1 + 64, "執(zhí)行程序") = vbOK Then
          cn.Open (strCn)
          strcheck = "delete from ww..wpd_prsn where year_month='" + CStr(Range("景盟!A" + LTrim(2)).Value) + "'"
          cn.Execute strcheck
          cn.Close
          MsgBox ("資料刪除成功")
    End If
End Sub